Records getting lost

[Jim Devenish]

I have a table in which a number of records get lost, most likely after
compacting the database.

The relevant table structure is as:

Table Tasks with a primary key TaskID (Autonumber)
Table Enquiries with primary key EnquiryID and foreign key TaskID (long
integer)

A relationship between the tables (on TaskID) includes cascading
deletes.

Not all enquiries relate to a task and so some records in the Enquiries
table have a null value for TaskID.

Records have on two occasions disappeared from the Enquiries table
(possibly, but not certainly after compacting). The missing records
appear to be those with a null value in TaskID.

Is this what one should expect after compacting?

I expect someone will advise me to redesign the table structure.

Jim

[Allen Browne]

No, you should certainly not expect to lose these records when you compact
the database.

Null foreign keys are an important part of database theory and practice.
Where they are intended (clearly your case), they do not represent any
problem with the data structure.

If the records are being lost on compact, it is more likely due to a
corruption of the database. For suggestions on how to avoid that, see:
Preventing Corruption
at:
http://allenbrowne.com/ser-25.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Devenish" <in***************@foobox.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I have a table in which a number of records get lost, most likely after
compacting the database.

The relevant table structure is as:

Table Tasks with a primary key TaskID (Autonumber)
Table Enquiries with primary key EnquiryID and foreign key TaskID (long
integer)

A relationship between the tables (on TaskID) includes cascading
deletes.

Not all enquiries relate to a task and so some records in the Enquiries
table have a null value for TaskID.

Records have on two occasions disappeared from the Enquiries table
(possibly, but not certainly after compacting). The missing records
appear to be those with a null value in TaskID.

Is this what one should expect after compacting?

I expect someone will advise me to redesign the table structure.

Jim

[Devonish]

Allen

Thanks for helpful response. I am pleased that the data structure is OK
- I
thought that it should be.

It is possible this occurred after a corruption - not absolutely sure.

Sadly the back end does get corrupted once or twice a week. The most
likely
cause it the network. I am in the process of learning enough about SQL
Server to upgrade the back end but am not there yet.

I was interested in the statement in your link:

"If Access crashes, do not allow it to compact and reopen. Delete the
ldb file.
Create a copy of the crashed database. Then reopen, and compact/repair.
If it
did corrupt, it may be possible to rescue some objects from the
corrupted
database provided it has not been compacted. The undocumented commands
SaveAsText and LoadFromText are useful in this context."

How do I use SaveAsText and LoadFromText with a corrupted copy of the
database?

Jim

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

These commands work in VBA (so in the Immediate window).

If the database is corrupted to the place where it cannot be opened, they
won't work. Sometimes a particular form or report will not work with
SaveAsText even though it appears to be okay, and that is an indication that
the object has corrupted.

The syntax of the command is self-explanatory. For example, to export a form
named "Form1" as a text file:
SaveAsText acForm, "Form1", "C:\Form1.txt"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Devonish" <no*****@nowhere.com> wrote in message
news:7Q***************@news.uswest.net...

How do I use SaveAsText and LoadFromText with a corrupted copy of the
database?