All records have been deleted from a table - help please! (1 Viewer)

poohbear · Jan 6, 2018

I built an access database system which has been running my Son's business very well for 5 years. Yesterday, the system "froze" (his words) for a while and then he found that all the records had been deleted from a table containing text fields (containing notes about client conversations). This is the largest table in the system and contained 75,000 rows.

There is no code anywhere in the system which will deleted records from a table. I find it hard to belive that it would be a virus as it would need to be very specifically targetted.

Does anyone have any ideas on how these records got deleted? Are there any keystroke combinations that could acieve this if the user did something strange?

We have gone to backup but have lost a whole day's worth of notes and I am worried that if we don't know why this has happened then it could happen again.

Any thoughts would be greatly appreciated.

Thanks

NauticalGent · Jan 6, 2018

Usually when something like this happens, the culprit is multiple users on a DB that is not split.

Do you know if there were multiple users and whether or not the DB is split?

poohbear · Jan 6, 2018

Thanks for the quick response.

There were definiely no other users on the database when the problem happened.

NauticalGent · Jan 6, 2018

Well if you are sure of that then I can only guess what the culprit would be and none of them would bring the data back.

If the DB is not split, I strongly urge you to split it. The drawbacks to doing this are almost non-existent and the benefits are many.

Here is the best article I have found on the subject:

http://www.fmsinc.com/microsoftaccess/databasesplitter/

Aside from that, frequent backups are your friend, which it appears you already know that. Also, a periodic Compact and Repair can fix problems before the become major.

And finally, if the is a lot of code involved, an occasional Decomplie is a good thing. The FMS site I gave you goes into detail on that as well.

Some other members here are much more knowledgeable then me so they may have a better solution or suggestion.

Best of luck!

poohbear · Jan 6, 2018

Thanks for taking the time to post al that information - I really appreciate it.

I will look at all the points you suggest.

Best regards

poohbear · Jan 6, 2018

Oh - I forgot to say, the database has always been split.

NauticalGent · Jan 6, 2018

You’re quite welcome and don’t hesitate to ask if you have any questions or concerns!

The_Doc_Man · Jan 6, 2018

For future reference, if this ever happens again, the first thing you do is immediately copy the file. If you are EVER going to recover today's data, it is there (or nowhere). THEN you can try various recovery techniques starting from a copy of that copy.

Since the DB is split, the next question is whether it runs across a network, even a local intranet that is not managed at a domain level. (I.e. the type of network you find in your home based on a cable router rather than a domain controller.) What kind of network are we discussing, and is there a chance that this network is intermittently connected (or stated another way, exhibits occasional signal dropouts).

The idea that you lost a whole table begs the question: Even if you have no "Delete" queries running, do you have any whole-table Update queries in play? There are designs for this situation that help improve the safety of your DB.

Are the text fields Short Text or Long Text (older name for Long Text is Memo)?

poohbear · Jan 6, 2018

Hi and many thanks for your detailed response.

I did make a copy of the database straight away. I tried to use a tool which claims to recover deleted records but it didn't find any. I then discovered that I had "compact on close" turned on (which seemed a good idea at the time). I have now turned this off.

The front end and back end are both running on the PC which was being used at the time, so presumably there would not have been any network issues.

There are no queries which update the whole table (there is only one such query in the whole application and that operates on a completely different, unrelated table)

The text fields are just "Text", set to 255 characters feild size. (presumably Short Text, as there is another option of "Memo")

Thanks again.

Galaxiom · Feb 27, 2020

How big was the backend file? Access can run out of room quite catastrophically when it passes 2GB.

theDBguy · Feb 27, 2020

Does the user have direct access to the tables? Just curious if all the records were somehow got selected and accidentally deleted manually rather than by code.

Micron · Feb 27, 2020

By any chance did either of you copy the fe (and maybe be), open the fe and then figuring you were acting on data in that db, do anything to delete records? I have seen this happen before because of a lack of understanding about linked tables. It's probably far removed from the reason you lost all records in a table, but it has happened before because the guilty party thought those linked tables belonged to the copy.

As for a keyboard combination, ctrl+A then Delete will delete everything from a table, and if you have turned off warnings, it will delete immediately without prompting. Do you ever turn off warnings in this db?

All records have been deleted from a table - help please! (1 Viewer)

poohbear

New member

NauticalGent

Ignore List Poster Boy

poohbear

New member

NauticalGent

Ignore List Poster Boy

poohbear

New member

poohbear

New member

NauticalGent

Ignore List Poster Boy

The_Doc_Man

Immoderate Moderator

poohbear

New member

Galaxiom

Super Moderator

theDBguy

I’m here to help

Micron

AWF VIP

Similar threads

Users who are viewing this thread