Massive record loss in Access 2003

[Lysander]

Can any of the experts throw any light on this situation.

I have a multi-user (backend/frontend) database in Access 2003, with about 15 main tables, and associated lookup tables.

This system is a double-entry system, i.e. all data is entered twice, by two different teams, into two different sets of tables. All the tables are normalised and linked as required.

This database has been corrupted a number of times, mainly due to constant power cuts and when it gets corrupted, normally 1 table loses its relationships and 1 record. Sometimes, the whole table is lost.

The main table is tblWomen and at this point in the system, tblWomen is having no records added or deleted, though they are being ammended.

On Friday, one of the data entry persons reported 97 women had dissappeared from tblWomenDoubleEntry (I have a routine to check the double entry values). When the supervisor did a full check, over 56,000 records had dissapeared. He sent me the database from India and yes, 56,000+ records had gone, but no sign of corruption, no damaged indicies, no bad records.

I copied the missing records across from tblWomen, but, has anyone seen this sort of corruption before?

My code has not changed for months, so I can't see how that can have caused it, and the staff in India have no access to the underlying tables, only access through my code.

Obvioulsy, I am worried this might happen again, and if it happens on both sides, single and double entry, we could be in big trouble. (And yes, we do take daily backups, well, I hope we do, we are supposed to, but I am in North London and the system is in Hyderbad:)

Thanks in advance if anyone has any insight on this.

[NeoPa]

Not a lot it seems Lysander :-(

I'll post a link in the Help thread in case that triggers more comments.

[MMcCarthy]

do you have any synchronisation routines for tblWomenDoubleEntry. I'm thinking if there was some kind of crash during sychronisation this could be where the problem arose.

If the users don't (and can't get???? - as in some clever idiot pressed a button which showed the database window) get access to the tables then the problem has to arise in some routine that acts on that table.

Hope that sends you in the right direction.

Mary

[Lysander]

Thanks Mary,

I know what you mean about synch routines and there is such a routine for a monthly monitoring table but the women, and womendoubleentry tables were populated during an initial survey 3 years ago and the only change now made to those tables is to flag individual women as having died. There is no option to delete women in code. I am loath to point the finger at some smart alec in India accessing the tables directly as I have no evidence of that at all:)

[MMcCarthy]

Are you sure the records were actually deleted and not just corrupted? Check to see if there are a load of records with ###'s in them.

If there are this kind of corruption occurs when the database doesn't have the edited record lock set usually.

[NeoPa]

Another way to check for unresponsive records is to run an update query (which has no net effect) on the whole dataset and see how many records are reported as being updated. I have often noticed a larger number of records reported when I clear down a table (DELETE FROM [tblXXX];) than are listed as extant at the time.

[Lysander]

Hi Mary,

The problem has been fixed and the database sent back to India but there were no 'funny' looking records and a compact and repair produced no errors. Also, normally when the table gets corrupted it loses its primary key and all its relationships. In this case, everything looked fine except for the missing records.

Have just got a fresh copy from India, after another weeks work and everything is normal. Also just searched the whole project for tblWomenDoubleEntry and there are no instances of "Delete" in the code.

Very strange. As the clinical trial ends this month, just have to put it down to one of MicroSofts mysteries.

[MMcCarthy]

Make sure the edited record lock is on anyway. It's a good precaution on a multiuser db.

[NeoPa]

Your database could maybe check that Default Record Locking is set to Edited record in the registry.

I'm running Office 2003 and my version number is therefore 11.0. I found the Registry Value in the registry under the Key :
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\A ccess\Settings\

Module to Read From the Windows Registry may help if you need it.

[Lysander]

Thanks NeoPa, and sorry for the delay, I must have missed your post. I can't check the registry entry as the database is running on a server in Hyderabad, India and there is no-one there competent to mess with the registry.

As we are running 10 different databases, all of similar type and nothing like this has happened in 5 years, I am going to have to assume a 1-off weird corruption.

(BTW, having had massive record locking issues in another project, before we sent out the 1st database 5 years ago, I had 10 students enter and delete data continously for 4 hours and we had no locking issues:)