Table losing data

[fat controller]

I wouldn't believe that this was true if I hadn't seen it with my own eyes, but I assure you that it is.

I have a table which is mainly used as a source of information that is used daily. Edits or additions to this table are fairly rare, and are done through a specific form and any changes made are written back to an audit table. The information contained in the table is related to vehicles (so registration number, Unique ID number etc)

About four weeks ago, it became apparent that some of the vehicles were 'missing', so a bit of a look at the table showed they were indeed missing, and that for some reason the Primary Key (Autonumber) ID's were in a bit of a mess -- instead of starting at 1 as they always did, they started at 252, ran up to the 500's and then things started to go astray with ID's missing, and then a jump from 878 to 1199, as though someome had deleted all the records in between.

I decided that the table was corrupt, so copied in a new table (renamed the original as 'old' copied the data across to a new table, and then carried out a compact and repair on the entire back end file.

All was good until this morning, when 12 vehicles have just dropped off again - ID's 1 through 12 do not show on the table at all, and I know for a fact that they were there before - - Indeed, I have evidence that the data was there on Friday as it was used on numerous occasions, yet by yesterday it was gone.

I have checked the audit trail to see if it has been done maliciously or accidentally, but there is no record of anything being done on the form that controls the table. Users do not have access to tables to delete the data directly.

Other than quickly making a new back end file and importing the data to it, I have absolutely no idea what to do now, mostly as I haven't got a clue what is going wrong?

PS - the back end file lives on a company server, the front ends are .accde files that live on user's desktop screens.

 

[MarkK]

Is the network a domain running SBS? I have seen a similar problem where data seems to go missing from Access databases. In some cases, user files, such as those in the My Documents folder, actually reside on a file server, and the user only uses locally cached versions. When the user logs off Windows, these cached files are re-synchronized with the files on the server, but if a file is not opened by the Windows registered program that "owns" that file type, then windows does not update the Last Modified Date. As a result, software can edit data in an Access Database without running Access, and Windows won't know that the file has edits, and Windows won't update the LastModified date. In this case, I suspect that windows discards the "updated" file on re-sync with the server because the modified dates of the two version are the same, even though the local file was edited.

Not sure if this might apply in your case, but this remains an unsolved problem at one of my customers, where Access database are used by third party software and data known to be entered by one user is not present when a second user opens the files edited by the first user. This is an intermittent problem for them. Sometimes the data is present, sometimes not.

 

[Galaxiom]

I have a table which is mainly used as a source of information that is used daily. Edits or additions to this table are fairly rare, and are done through a specific form and any changes made are written back to an audit table.

Do you have any users writing to the database though slow connections eg WAN or Internet? This can do damage to more than just the table they were writing to.

Maybe there is a "source of information" query that gives unexpected access to that table and an unanticipated opportunity to accidentally delete records? Check the Object Dependencies for that table to see where it is used. You could look further with V-tools Total Deep Search to find every mention of it in the front end.

Remember too that it is virtually impossible to lock users out of an Access backend. Someone may know enough to be dangerous.

 

[fat controller]

I think I have found the problem - it appears that our network has been less than happy, and has been dropping connections all over the place. We operate another type of database (essentially a large HR system) and it too has had problems; users who have logged out are not actually being logged out, and when the connection to the servers has dropped, users have still been showing as logged in and active. Our IT bods have been playing with the network to try and stabilise it, and we will go from there.

I ended up making a new back-end file, and having taken the data from the original table into excel to have a closer look at it, I then appended it to the new backend and all is now well. Fingers crossed that is it sorted.