Random Table Data Deletion (1 Viewer)

[hunsingerl]

Have you considered or do you have an Audit Log to identify what changes were made to data (tables), when and by whom?

I have an audit log system set up, but it is record based. So when the records get deleted the log for the record does as well. User deletes are disabled (it's a check on the record line that the user selects so the data no longer displays). After an allotted time, I go in and delete the files (Archiving isn't necessary for this system. I'm in the process of making a secondary backend that will collect deleted data.

 

[hunsingerl]

This is potentially a significant fact. Was that size difference between the damaged DB and the backup based on the "raw" damaged database for which you had not yet attempted a Compact & Repair?

If you use Access to make a backup copy, I don't know if it implies a C&R. If you use Copy/Paste from Windows Explorer, that DOES NOT imply C&R and thus the copied file should be the same size. If the damaged DB was not C&R'd before examination and was STILL shorter than the backup DB, that is a sign of corruption. An "ordinary" delete doesn't reclaim space from a file until the C&R occurs.

Stated another way, if you took a backup of the DB, copied that, and deleted tables manually, but did NOT do a Compact & Repair, the two files should have been approximately the same size within a couple of percent. If the damaged DB is significantly shorter, the physical file is damaged.

I would run a CHKDISK on the disk holding that damaged back-end to see if it has one or more file fragments it can't identify as being part of a named file. (Because you have already replaced the original with its backup, thus replacing the name.) If that has happened, you had a disk failure.

To follow up on that, you should check the system's event viewer. If you know approximately when the loss occurred, go to that machine that served the DB and check the event log viewer. Entries are sorted by date/time so it would be easy to find the detailed logs for the time of the error. Look for a disk error. If you had a disk error and lost the back part of the file, the REAL surprise is that you didn't run into even worse errors than merely losing data.

I'm having the IT folk that run the server the backend is on run full system and disk checks. I don't think this is the case, because it would be extremely odd that only a file was corrupted and even more odd that only certain tables in that file were affected.

 

[hunsingerl]

Could it be that a backup and compact were run whilst users were still using the database? I suppose someone could have made a copy of the database, taken it home, created a series of cascade delete queries and then ran it against the database back at the office.

I don't think the first case could of happened, since the backup is done at midnight. I'm having the IT folk that run the server check the event logs to see if something happened with a delayed backup or something.
Not saying it's impossible and doesn't happen at other offices but couldn't happen here.

 

[LarryE]

Well then someone put an old backup file into production and it was opened and used as the current Backend file. Maybe the records were never there to begin with if that's the case.

 

[hunsingerl]

Well then someone put an old backup file into production and it was opened and used as the current Backend file. Maybe the records were never there to begin with if that's the case.

Would have to be a REALLY old backup file. When checking audit logs on the damaged back end the dates on the unaffected tables were all current.

 

[hunsingerl]

Just happened again. I was the only one in the DB just watching to see if it would happen again. No inputs were made and the tab set that I moved to had #DELETED# in all columns. Only 3 tables were affected this time.

 

[jdraw]

So, now that it happened again(only a half hour ago), do you have any facts to help with resolution?
Users online, other activities, event logs, audit... anything special??

 

[LarryE]

You might try opening each form in design view and setting its Allow Deletions property to NO. Then see what happens.

I assume you have looked to see that no macros or VBA code is triggered when you open the database.

Also check the External Data>>Linked Table Manager to make absolutely sure you are linked to the correct Backend file.

 

[hunsingerl]

So, now that it happened again(only a half hour ago), do you have any facts to help with resolution?
Users online, other activities, event logs, audit... anything special??

Unfortunately, just made things more confusing. I was the only user online, no event logs triggered, no audit lines triggered, I wasn't changing any data, and wasn't clicking any buttons. Just moved to a tab set and the data showed #DELETED#.
There isn't ANY code or macros that trigger on a tab set and ZERO code that deletes from tables.

 

[jdraw]

Same tables? Is it the same Tab that is set when the issue occurs? Do you get anything on your Error handler?Can you step through some code before the Tab set, or add some debug.prints to possibly narrow down the point of "failure"?

Is it a database that could be shared here to help/review?
How long has this been happening? What is impact on the company/users/management?

 

[LarryE]

Just moved to a tab set

What is a "tab set"

 

[hunsingerl]

Same tables? Is it the same Tab that is set when the issue occurs? Do you get anything on your Error handler?Can you step through some code before the Tab set, or add some debug.prints to possibly narrow down the point of "failure"?

Is it a database that could be shared here to help/review?
How long has this been happening? What is impact on the company/users/management?

Different tables this time. Different tab than last time. No returns on the error handler. Have done multiple tests with breakpoints stepping through the code and haven't seen the delete happen.

Unfortunately, database can't be shared.

Issue started yesterday afternoon. I have 10 users that update the database all day every day, which is packaged as a standalone and delivered to several customers every month. Anytime this delete phenomenon happens I have to pull a backup and my users have to redo their data entry that was after the backup date/time. I have moved up my backups to every 15 minutes, so our data loss is minimal, but having to pull a backup every time this happens isn't a sustainable option.

 

[hunsingerl]

What is a "tab set"

Form with several subforms, each accessed by selecting a tab at the top of the form. Like a web browser with multiple tabs open.

 

[LarryE]

Please take a screenshot of your relationships window so we can at least see how your tables and fields are designed. Database Tools >> Relationships

 

[Cotswold]

On the backup, you say it runs at midnight when users are not there. But does it check for users that may have left one or more copies open before running? I once had a user who opened multiple copies of various programs but didn't always close them all.

Just prior to this delete nightmare happening were then any changes to any code or queries?
If so then could the update have caused unintended consequences?

Do you have any functions that delete records? If you have, consider removing them and replace with hiding the record.
Consider removing the delete events and change to hide. For instance, I have a field in certain tables where deleting is a user option called Hide as Byte 0/1. Where 1=Hide. On the user screen the field label is Delete, so they believe they are deleting. All applicable queries have a filter using Hide=0. That way a user cannot delete something they shouldn't. For instance booking goods out to a friend, or themselves.
The manager of the system can run a filter to see hidden records and check for fraudulent deletes, or frequent mistakes that users set delete/Hide. If all is well then the manager can run an update query to delete all hidden records. Usually with a date filter option. Then the database is compacted to remove deletes. I tend to avoid cascade deletes except for maintenance. After all if the Parent is Hidden then none of the children will show anyway, so they can sit there until removed.

 

[ebs17]

Different tables this time. Different tab than last time. No returns on the error handler. Have done multiple tests with breakpoints stepping through the code and haven't seen the delete happen.

Whom have you angered so that all suffering comes upon you?

I have 10 users that update the database all day every day

If the update does not involve deleting any data records, you could block this deletion directly in the tables of the Access backend using DataMacro.

As is well known, the security of an Access backend is not very high (I'm still thinking about access from a third party), so in a professional environment you should quickly consider using an active database management system (SQL Server & Co.).

 

[gemma-the-husky]

Absolutely agree with that first statement.
As far as statement 2 goes is the weird part... Cascading deletes are set up, but the records that could cause that kind of delete weren't modified. As an example: Table 1 is the main with tables 2-10 as children of that main table. Relationship is set up so that if table 1 record gets deleted, data related to that table 1 record for tables 2-10 get deleted. Problem is all table 1 data is fine, and table 2, 5, and 10 are all fine. Tables 3, 4, 6-9 ALL data is gone. A user would of had to delete ALL table 1 data to cause ALL table 2-10 data to delete, which didn't happen.

I don't understand that. If you have RI set between tables you would be prevented from deleting records that would leave orphans. If you just know you have relationships between tables, then you ought to have some RI. For what it's worth I never allow cascade deletes as I think it's just too dangerous.