A mystery: can just a compact & repair change table relationships?

[GaryDave]

My school registration database has not been quite right after a recent
compact and repair (done while I was away). Though most of the many
forms and subforms are working normally, one form in particular will no
longer allow new entries into the subform - the recordset of the
underlying query is no longer updatable.

The subform in question enters the identity and other vital info for
children whose parent's ID provides the FK, linking the parent's info
on the main form to the children's info on the subform. We can enter
children by manually addressing the underlying tblchildren, being
careful to enter the correct adult ID#.

The wierd part is that the same form/sub-form works fine with an old
copies of the same tables, before the compact and repair. Something in
either tblparents or tblchildren has changed that is disabling the
query, but damned if I can see anything that doesn't seem right. Any
suggestions out there? I'm running Access 2K.

Thanks!

Gary

[Tom van Stiphout]

On 9 Jun 2005 13:05:33 -0700, "GaryDave" <ga******@hotmail.com> wrote:

Your assumption that the Compact caused the problem is probably
incorrect. This error just isn't being reported by others.
I would debug as follows:
1: Get current with all Office service packs and Jet 4.0 service
packs.
2: Repair & Compact both front-end and back-end databases.
3: Review subform properties. Are AllowEdits and AllowAdditions still
set to True? Anything weird with LinkMasterFields and LinkChildFields?
You're not setting any of these properties in code, right. In the
Debug window type something like:
?Forms!frmMainFormName!subfrmControlName.Form.Allo wAdditions
4: If the problem is not with the subform, convince myself that the
query itself is indeed not updatable. Typically it would only be
something like "select * from tblChildren order by Birthdate", which
is always updatable (assuming the table has a primary key).
5: Assuming your subform query is more complicated than that, start
with "select * from tblChildren order by Birthdate" and incrementially
add features until it becomes not updatable (when run in query view).

-Tom.

My school registration database has not been quite right after a recent
compact and repair (done while I was away). Though most of the many
forms and subforms are working normally, one form in particular will no
longer allow new entries into the subform - the recordset of the
underlying query is no longer updatable.

The subform in question enters the identity and other vital info for
children whose parent's ID provides the FK, linking the parent's info
on the main form to the children's info on the subform. We can enter
children by manually addressing the underlying tblchildren, being
careful to enter the correct adult ID#.

The wierd part is that the same form/sub-form works fine with an old
copies of the same tables, before the compact and repair. Something in
either tblparents or tblchildren has changed that is disabling the
query, but damned if I can see anything that doesn't seem right. Any
suggestions out there? I'm running Access 2K.

Thanks!

Gary

[GaryDave]

Tom -

Thanks for your really useful and detailed reply. I know that it's
highly unlikely if not impossible for a C & R to create the sort of
problem I'm experiencing, but it is absolutely the only change in the
environment that occured in the lead-up to the current issue, so what
else could it be? I have various versions of the front-end, older and
newer, and they are all experiencing the same issue, so the backend
must be the culprit, somehow.

I will go through the steps you recommend. I can't run the underlying
subform query except with the form actually open, as it relies on combo
boxes on the main form for some of its fields. But I can still add
fields one-by-one as you suggest, then open the forms and see if they
are still updatable.

If I have an success I'll report it here for the benefit of others.

Gary

[Tom van Stiphout]

On 9 Jun 2005 23:00:34 -0700, "GaryDave" <ga******@hotmail.com> wrote:

You can temporarily "stub out" those references to parent fields: just
put in a value that the combobox might have.

-Tom.

Tom -

Thanks for your really useful and detailed reply. I know that it's
highly unlikely if not impossible for a C & R to create the sort of
problem I'm experiencing, but it is absolutely the only change in the
environment that occured in the lead-up to the current issue, so what
else could it be? I have various versions of the front-end, older and
newer, and they are all experiencing the same issue, so the backend
must be the culprit, somehow.

I will go through the steps you recommend. I can't run the underlying
subform query except with the form actually open, as it relies on combo
boxes on the main form for some of its fields. But I can still add
fields one-by-one as you suggest, then open the forms and see if they
are still updatable.

If I have an success I'll report it here for the benefit of others.

Gary

[jv]

I've seen intances where a R & C could remove the primary key property
of a field and delete table relationships. If a record in a table was
corrupted and R & C was not able to recover it, it sometimes changes
the value of the fields in that record to be ###### and place a null
value in the primary key field. Since a primary key field cannot
contains a null value, the primary key property of this field is then
removed. And thus, consequently, sometimes alter the table
relationships.

Our procedure after a R & C, especially if it was run to correct a
problem, is to always look for a table called MSysCompactError that
would have been created by Access during the R & C process. This table
would give you the ErrorTable name and ErrorDescription. Open the
ErrorTable in datasheet field and sort it ascendingly by the primary
key field to make sure that there is no null value. If there is then
delete that record. Then switch to design view and make sure that the
primary key property still exist. Then open the relationship window
and check to make sure that all relationship for that that table still
exist. Finally, we would try to append the deleted record (if any)
back to the table using our hourly backup of the database.

Good Luck.

Julie Vazquez

[David W. Fenton]

"GaryDave" <ga******@hotmail.com> wrote in
news:11********************@o13g2000cwo.googlegrou ps.com:

My school registration database has not been quite right after a
recent compact and repair (done while I was away). Though most of
the many forms and subforms are working normally, one form in
particular will no longer allow new entries into the subform - the
recordset of the underlying query is no longer updatable.

The subform in question enters the identity and other vital info
for children whose parent's ID provides the FK, linking the
parent's info on the main form to the children's info on the
subform. We can enter children by manually addressing the
underlying tblchildren, being careful to enter the correct adult
ID#.

The wierd part is that the same form/sub-form works fine with an
old copies of the same tables, before the compact and repair.
Something in either tblparents or tblchildren has changed that is
disabling the query, but damned if I can see anything that doesn't
seem right. Any suggestions out there? I'm running Access 2K.

Others have answered the question in your subject (with a NO, and
they are correct). You don't say what version of Access, but it if
it's Access 2000, it sounds like a typical problem that occurs with
the shipping version of Jet 4, which was horridly buggy. Your
problem sounds like a corrupted index, or a lost seed value for an
AutoNumber field.

A2K is completely useless until you've patched it to these levels:

1. Access SR1 or higher (you have to run the full Office service
pack; the last was SP3, but that also included the Draconian email
security patches for Outlook; SR1 does not include that, but has
fixes to Access that are sufficient to make it perfectly stable)

2. Jet 4 SP6 or higher (the current SP is 8).

No A2K installation should be running with anything less than these
minimums, unless you don't care about the safety of your data.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[GaryDave]

All -

Problem solved!! Julie clued me in to look for a problem with the
Primary Key, and in fact it had been removed from one of the key
tables. I remember now that the IT person who did the C & R mentioned
something about a corrupt record, so that had already been deleted. I
was able to restore the ID field as the PK, and bingo - the recordset
is now updatable.

Now I will see about updating the SPs - last time I tried, I think I
ran into problems because MS had stopped posting older SPs past a
certain #, and the newest SP would not update an SP as old as mine.
But anyway, the immediate problem is solved, so thanks to all!!!!

Gary