Missing data (1 Viewer)

C

chrisp

Guest
Noticed random records were missing from database. Checked all tables, checked field properties and relationships which seemed okay except data appeared as follows:
Table 1 - random records were missing (approximately 1000 of 3000 records). Primary Key of table 1 is foreign key in tables 2, 3, 4 with relationships as 'one to many'.
Random records are also missing from Tables 2, 3, & 4 although in some instances, they weren't the same related record. i.e. record missing in table 1 appeared in table 2 etc.
There doesn't seem to be a pattern for the missing data as most of the records are perfect. We also were not able to establish when the corruption might have happened because records missing range from 1999 to 2004.
Has anyone heard of such a problem? What could have caused it and can it be fixed? Please help!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:23
Joined
Feb 19, 2002
Messages
42,983
What are you using to determine that data is missing? Are you considering gaps in an autonumber to be missing records? Gaps are a frequent occurance in autonumbers and nothing to worry about.

Do you have referential integrity enforced between the two tables? If you do, there should be NO orphan records in table two.
 
C

chrisp

Guest
Thanks for responding. The missing data was first discovered when we went to update a customer's records and it wasn't there. That prompted me to look at the tables in the backend which revealed lots of gaps in the autonumbers in all related tables. We know for a fact that there are lots of records missing. We have been inputting records into this database for about five years without any problems. "Enforce Referential Integrity" isn't checked.
Why do gaps appear in autonumber fields? Why is it that an autonumber missing in the primary table appears in the related table as the foreign key of that field? Does this mean that the record is still somewhere in the database but is hidden?
Please excuse my ignorance. I've been a database user for a while and have just completed my Expert level certificate. The developer of this database is no longer with the company and the problem has been redirected to me. Is there anyway we can salvage this database or do we need to rebuild another database?
 

RoyVidar

Registered User.
Local time
Today, 15:23
Joined
Sep 25, 2000
Messages
805
1 - gaps in autonumbers are a "feature" of the autonumber. An Autonumber should not contain any business meaning. When a new record is cancelled, that number is used, the next new record will get the next number
2 - orphan child records - I'm far to lazy to not enforce referential integrity - I don't consider myself a good enough a programmer to catch all the possible (and perhaps impossible ways) of getting orphan child records when referential integrity is not enforced. It's so easy to forget when you're "just doing a little something on something completely unrelated, that's not going to effect...."

I don't think you'll find the missing parent records, they are probably deleted, I would suspect by users, cause I've not heard about Access doing that by itself.

I would perhaps not rebuild it from scratch, but collect all information necessary to rebuild the missing information (check your backups), then enforce referential integrity to prevent it from happening again, and of course, frequent backups.
 

diamondpass

New member
Local time
Today, 07:23
Joined
Dec 4, 2008
Messages
1
Hi,
I just put together a Work Order template Stopped for a couple of days then came back and most of my second entry notes disappeared.

Then input some more data in and stopped for a other couple of days the information was gone including the customer that the information was under.


Boy that was strange.

And no pattern of the deletions

So... if any comments, Iam checking my cells yet the cells for being invisable, are still there yet no information.


Mike

:rolleyes:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:23
Joined
Feb 19, 2002
Messages
42,983
Make sure your form is not filtered. Examine the tables directly. Another potential issue if you are using a subform is that the master/child link is not properly set so the foreign key of the subform record is not being populated. This will create orphans and records will seem to disappear.
 

Users who are viewing this thread

Top Bottom