Compact error - deleted records

[MontyBurns]

Hi,

I'm trying to compact a DB, in which I have a table ADMINConnectedUsers which tells me who is using the FE Access app to connect to the backend DB. Both are Access 97.

When someone opens the FE app, their NT login ID is put in a new record with a timestamp. There is an Autonumber key field also.

When I try to compact it, I get a compact error and it generates a copy of the DB (called db1.mdb). This DB has all the same tables, and an extra one called MSysCompactError. In it are three records corresponding to 3 errors it encountered. These are:

ErrorCode ErrorDescription ErrRecID ErrorTable
-1017 Record is deleted ADMINConnectedUsers
-1017 (see below) ** ADMINConnectedUsers
-1053 (see below) ADMINConnectedUsers

The Error Description for the btm 2 recs is:
ErrorDescription
The Microsoft Jet database engine could not find the object 'MSysCompactError'. Make sure the object exists and that you spell its name and the path name correctly.

The ** represents 2 little squares. If that makes sense...

When I look in ADMINConnectedUsers I see 2 records with the #deleted phrase in there. I can delete these recs, but they reappear when I reopen the table.

I know I can get a backup, but i'm very curious - esp. if this is going to happen every time I try to compact the DB.

Any ideas anyone? Any help would be really appreciated.

Burns

ps - I have exclusive access to the DB

 

[MontyBurns]

pps - just looked at a backup, and the #deleted records are in there also

 

[WildHare]

Not absolutely certain but your problem may trace back to A/N key fields in a multi user database. They're generally frowned upon because of locking potentials. Why bother A/N'ing a key field when Userid and Timestamp should be sufficient?

However, that doesn't solve your problem.

Assuming that this AdminConnectedUsers guy isn't linked to any other tables, I'd try to first export and then import him back in.

If you still have the problem, the table structure itself is probably corrupted (a bad link list or something between the records) and you'll probably need to populate an entire NEW table with the UNDELETED records and then scratch the original.

You don't try to compact this guy while it's in use, do you? Because I think I've noticed that sometimes the compact starts up and compacts some tables, and then barfs when it finds a table being shared by >1 user and then stops, leaving your "Db1.mdb" guy out there..

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[MontyBurns]

cheers for the suggestions.

I take your point about the A/N field - just my way of doing things. I'm a firm believer that a key should be completely meaningless etc.

That was going to be my next action: to drop and then remake the table. Data's not a problem as when no-one's connected, there are no records in the table. I.e. when a user logs out the FE app deletes the appropriate record from the DB (or recs if they have more than one instance of the FE open at any one time).

And no, I don't compact when other users are connected. I always wait 'til I have exclusive access.

Thanks,
Burns