I'm working with an application that has an MS Access front end linked to the SQL Server DB backend. During a period of one week, 32 records of a spefic event (and all related records to that event) disappeared from both DBs. The application uses an Access report db for users to query for specialized data. Security is to the Access DB is limited to 3 db admins (2 IT, I departmental), and 1dept manager. The app's audit log is supposed to track app activities, however, nothing is logged showing deletes for the missing records. The SQL Database is secured for one user, the IT DB admin.
We have a restored copy containing all of the missing records and are working to insert the mising records into the SQL DB.
There are no delete queries that have been run ** to my knowledge **. The application IT admins (myself and one other) have not deleted anything thru the Access DB. The SQL DB admin did not delete anything (no time, no inclination, besides not having the application knowledge for the precise removal of records) Our departmental admin doesn't have the query knowledge on delete queries. The department manager has minimal query knowledge and works through the application database analyzer for minimal changes to reports.
In my research, I found a few references to pc, systems crashes and network failure causing data loss in Access databases due to way Access retrieves data. This info is from a technical bulletin dated 4/2/02 and refers to a Microsoft article Q300216. According to the article, all tables involved in a form, report or query are copied across the network from the server to the pc. The tables are then processed and filtered to generate the recordset.
Additionally, if several users are using file read, write, and locking operations on the same shared file(s) accross the network, the file(s) can be left in an incomplete or a corrupted state. Two examples given were unexpected, abrupt client termination or a dropped network connection.
If Access DB has been corrupted, and is linked to the SQL database, could this be a possible answer to our problem. We plan on doing our own restore as we have both missing data and the qualified staff.
The vendor, not surprisingly, has claimed negligence on our part for the missing records, mainly due to the absence of corroborating audit trails. My purpose in posting this question is to validate our research and encourage (strongly!) that the vendor improve their product and prevent this from occurring again.