Primary key dropped during Compact and Repair

[timmah13]

Hi all. I have an MS Access 2000 database used as the backend for VB code. At a client's site, multiple networked users are connected to the database at any time. Some users occasionally receive the message: "Record(s) cannot be read; no read permission on 'XXXXXXX'" on a few different tables. Any thoughts since this occurs rather infrequently? I don't know if this is a cause or symptom of a corrupt database (or neither).

Also, through code, each night a compact and repair (C&R) is run. Sometimes, but not consistently, if the database is corrupt, the primary key for a table will get dropped during the C&R. It's not always the same table either. I have noticed that immediately before the C&R there appear to be duplicate IDs even though it's the primary key. However, when I sort the table by that column, the two records are several rows apart (i.e. ID "75" appears in the location where ID 71 should be and the real ID 75 appears in its proper location). Any thoughts here? Any help is greatly appreciated. I can't seem to find any information about this anywhere.

 

[SMerrill]

Simply re-create the table and populate it with data from the old table. Make sure to put in the keys and relationships.

Take care,
--Shaun Merrill
Seattle, WA

 

[timmah13]

Thanks for the reply Shaun. While your solution will fix the problem once it has occurred, I'm looking to find out the reason the problem happens in the first place.

For instance, is it an issue with a slow network computer not finishing one database call before another call is made? It appears some sort of data collision is occurring since an existing record is getting overwritten with newer data. Why would Access allow the virtual ID 75 to overwrite the real ID 71 when the ID field is the primary key (and the real ID 75 exists)?

 

[THWatson]

timmah13
I'm not sure that this is your problem, but have a look at Article 257408 in the Microsoft Knowledge Base. This suggests there is a known issue in Access 2000 if the primary key is an AutoNumber, but that it has been fixed in the latest Jet engine update.

Just thought it might be worth mentioning.
Tom

 

[SMerrill]

And my thought was that once you re-created the table, the old corrupt table would be gone and the new one should not fail from then on.

Take care,
--Shaun Merrill
Seattle, WA

 

[timmah13]

Thanks Tom and Shaun for replying so quickly. As to Tom's thought, the primary key is not an autonumber field so I don't think that article applies. As to Shaun's point, I agree that moving the data into a new database should help, but I've already tried that and the problem still happens occasionally. Fortunately, I'm in the process of converting the application to use MSDE as the backend so we can get away from the all-too-fragile Access database! Keep those suggestions coming...