Access table dropping entries

[JLSigman]

I'm using Access 2000, SR-1. The database in question was originally created in Access 2000. It's a huge database (11000+ records in one table, 3000+ in another) keeping track of permit files.

Recently, it has started to drop some of the records in the large table. So far, from what I've seen, it only seems to be the first couple of records, but when you're talking about permits and keeping up with them, one is too many. What could be causing this? The whole database is barely over 6MB large, so I would tend to think it's not a space issue... or is it?

Thanks,

Jennifer L. Sigman
Information Resource Consultant
SC DHEC - Bureau of Air Quality

 

[RickSpr]

Is it possible that it's not actually dropping records? I'm speculating that these tables have a one-to-many relationship, enforced, with Update Cascade turned on. Perhaps somebody changed something in the main table, causing the foreign key to change, and the record no longer appears where you were expecting it. Or perhaps Delete Cascade is on and somebody deleted the main table row, by mistake, and then added it back.

I've heard anecdotes about Access not handling large tables well, but 6MB/11K records doesn't seem all that big to me. I haven't seen any convincing proof that Access malfunctions on large tables.

Make sure you compact the database occasionally (compact on close is good enough, provided your users don't keep it open day and night). Badly fragmented databases are more prone to failures. Rick Sprague

 

[JLSigman]

I've eyeballed the tables, and the records are definitely not there anymore... and the tables aren't related at all, so I don't think it's that. I'll compact the database, and tell them to use it again. If it loses more, I'll come back and see if I can narrow down the problem more. Thanks!

-jls