Solved Automated search for Database Corruption Assistance

[jdraw]

Sounds like a plan--- keep us updated.

 

[MajP]

This is a really long thread with lots of changes to the DB so hard to know what you fixed.

Primary keys do not revert to non-keys (in a non-corrupted table) unless done purposely as stated here:

Solved - Automated search for Database Corruption Assistance

We have a split database with 16 tables in it. Periodically, it experiences three types of corruption: Records will randomly disappear (typically a record that someone had open, but not always). Records will randomly be overwritten with data from a record in another table. Various fields in a...

www.access-programmers.co.uk

Is there code anywhere that could be doing this?

This sounds like a corrupt table. Seems like it is possible.
https://answers.microsoft.com/en-us...and-i-am/8d0103af-55a4-4717-a340-7d3e752f0178

The last possibility could be switching between different back ends or local. Maybe you are relinking to an old backend.

 

[Marshall Brooks]

Primary keys do not revert to non-keys (in a non-corrupted table) unless done purposely as stated here:

Seems to be what is happening and happening repeatedly, and seems to be what the answers.microsoft.com thread is describing also.

Is there code anywhere that could be doing this?

I don't think so. I could figure out how to write DDL code (I think) to change it, but I haven't done so, and I don't think anyone else with access to the db knows how to write code to do this and/or knows how to do it manually either.

The last possibility could be switching between different back ends or local. Maybe you are relinking to an old backend.

Unlikely. There is only one production backend. I fairly often switch to a local backend for development work, but the other users do not and the errors are occurring in the production backend.

One thing that I have heard, but I'm not sure if it is a genuine issue or not:

The backend is on a network drive.

4 members of our team use the database through Citrix with the front end on their U:\drive.

3 members of our team use the database with the FE on the local hard drive - sometimes using VPN to connect to the network and sometimes in-plant without using VPN. In both cases, the local team may be using Wi-Fi. Almost certainly when using VPN and sometimes locally. It would be possible but somewhat inconvenient for the local team to use Citrix also.

One member of our team uses the front end on a OneDrive folder - but he just started yesterday. I think that is unlikely to be the cause of today's problem and it can't have been the cause of the problem two weeks ago.

 

[GPGeorge]

Using the FE in a OneDrive folder is a bad day waiting to happen. Please figure out an alternative.

I skimmed the 9 page thread, so I may have missed something.

"The values were duplicated exactly - for example, we have a PrimaryKey field. It was NOT set as the primary key and it had the same value for all of the duplicated records."

That describes a problem that can result from corruption in tables. If you do a Compact & Repair on an accdb with a table with one or more corrupted records, Access can, and will, drop the Primary Key constraint in order to salvage as much of the data as possible. Then, if you don't catch it, subsequent data entry can create "duplicates". I've only seen that once or twice, in an application which was known to suffer corruption due to a flaky network.

 

[jdraw]

GPG,

Interesting observation/experience. Since Marshall has a procedure to check/confirm that Each Table has a PK, routine running of the procedure should identify if this condition( no PK on Table6) exists.

 

[Marshall Brooks]

@ GPGeorge - That helps. I do run a nightly C&R on the backend. So it is POSSIBLE the BE was corrupted, the C&R removed the PK and then a later corruption/error allowed the duplicated record.
@jdraw - I have a procedure to check for unknown records. I don't have a procedure to confirm that the table has a PK. I noticed that the records that were duplicated had the same value for the PK field, so I checked if this was still marked as the PK for the table and it wasn't. Then after I changed it, I checked again to ensure it stayed set.

 

[GPGeorge]

@ GPGeorge - That helps. I do run a nightly C&R on the backend. So it is POSSIBLE the BE was corrupted, the C&R removed the PK and then a later corruption/error allowed the duplicated record.
@jdraw - I have a procedure to check for unknown records. I don't have a procedure to confirm that the table has a PK. I noticed that the records that were duplicated had the same value for the PK field, so I checked if this was still marked as the PK for the table and it wasn't. Then after I changed it, I checked again to ensure it stayed set.

That pattern very much resembles my recollection of the problem at the client I mentioned. They also printed the Primary Key values on documents emailed to clients, so it was a highly visible problem. That's why we caught it so quickly; the person handling notifications was very competent and spotted the duplicated PKs.

As an aside, their problem was probably related to the fact that their IT guy was notoriously lazy super efficient. Instead of removing ceiling tiles and running network cables in that space, he draped them across the room, below the ceiling tiles, supporting them on top of the fluorescent lights to save installation time. When the lights came on, the network blipped.

 

[Marshall Brooks]

Found a new wrinkle and now I am highly confused.

The first issue that I found with a field not having a defined primary Key and having duplicated records occurred on 17-Jun.

I deleted or restored the records on 17-Jun and verified each table had a defined primary key.

The duplication I found today occurred in Table A.

I looked at our backups, and Table A had a defined Primary Key on the backup files from 17-Jun, 18-Jun, 19-Jun, 20-Jun, and 24-Jun at 7:00 P.M.

Table A does not have a defined Primary Key currently - I don't believe. I don't have exclusive access to the database, but I looked at the Table in read-only mode in design view and it doesn't have the key icon by the field, but it does have that for Table B.

Now the really strange thing - I found the duplicated record in a comparison report run at 6:45 P.M. last night.

So at 6:45 P.M., the database had a duplicated record, including the Primary Key field value, which is NOT SUPPOSED to be able to happen with a defined PK. But 15 minutes later, the backup of the database shows a defined PK field.

(We run a program at 7:00 P.M. that does a copies the backend to the local computer, does a C&R, and creates a backup, but I'm not sure if it does the backup before or after the C&R. I think the backup is first.)

Either way, I'm not sure how the database has a fully duplicated record 15 minutes BEFORE it still had a defined PK, and then somehow the defined PK was removed this morning. (The defined PK could have been removed during the C&R right after last night's backup, but that seems like an odd co-incidence and also doesn't explain how the record was duplicated including the PK value.)

???

 

[The_Doc_Man]

One member of our team uses the front end on a OneDrive folder - but he just started yesterday.

Put a stopper in that NOW. The protocols used by OneDrive are NOT repeat NOT compatible with the protocols used by Access. Access uses SMB (Server Message Block, the Windows File & Printer Sharing protocol) and OneDrive does not use that. SMB is a partial file protocol but OneDrive uses whole-file protocols. Which means that you would run across cases where a change by user 1 inadvertently triggers a whole-file copy to occur while user 2 is half-way through an update. Give the way that updates occur within Access, that is a walkin' catastrophe lookin' for a place to settle down - and it is lookin' towards you at the moment.

 

[GPGeorge]

Found a new wrinkle and now I am highly confused.

The first issue that I found with a field not having a defined primary Key and having duplicated records occurred on 17-Jun.

I deleted or restored the records on 17-Jun and verified each table had a defined primary key.

The duplication I found today occurred in Table A.

I looked at our backups, and Table A had a defined Primary Key on the backup files from 17-Jun, 18-Jun, 19-Jun, 20-Jun, and 24-Jun at 7:00 P.M.

Table A does not have a defined Primary Key currently - I don't believe. I don't have exclusive access to the database, but I looked at the Table in read-only mode in design view and it doesn't have the key icon by the field, but it does have that for Table B.

Now the really strange thing - I found the duplicated record in a comparison report run at 6:45 P.M. last night.

So at 6:45 P.M., the database had a duplicated record, including the Primary Key field value, which is NOT SUPPOSED to be able to happen with a defined PK. But 15 minutes later, the backup of the database shows a defined PK field.

(We run a program at 7:00 P.M. that does a copies the backend to the local computer, does a C&R, and creates a backup, but I'm not sure if it does the backup before or after the C&R. I think the backup is first.)

Either way, I'm not sure how the database has a fully duplicated record 15 minutes BEFORE it still had a defined PK, and then somehow the defined PK was removed this morning. (The defined PK could have been removed during the C&R right after last night's backup, but that seems like an odd co-incidence and also doesn't explain how the record was duplicated including the PK value.)

???

If you are doing a compact & repair during the same time frame when it is possible that a user wants to add or update records--and it sounds like you were within 15 minutes there--you are begging for corruption, IMO. It must be absolutely one way or the other. Don't touch the backend if it's possible someone has it open. And vice versa; make sure no one tries to open it when you are conducting maintenance.

 

[Marshall Brooks]

@The_Doc_Man - To clarify: the BE is staying on the share drive (Network, not OneDrive). The FE is either local or on OneDrive. Is that still an issue? I can potentially put the FE on a local folder and have a shortcut on OneDrive to open the FE. Would that work and would that be required/recommended?

@GPGeorge - I wasn't clear. The DB runs verification reports at 6:45 P.M. on Monday evening. This runs unattended. Typically I am the only one in the database at this time, but it wouldn't matter if someone else was in the database. The reports take less than 15 minutes to run. At 6:53 P.M., the database initiates a shutdown, which kicks everyone out - even if the reports are still running, and can take up to 4 minutes. At 7:00 P.M., a separate database backs up and compacts and repairs the back end, but if it can't get exclusive access, the C&R will fail and it won't complete the repair.

 

[GPGeorge]

Thanks for the clarification. Sometimes the forum context does leave out a lot of relevant detail.

 

[gemma-the-husky]

4 of our users have the front end on their desktop and open it from there.

4 of our other users are running the database from Citrix and (are supposed to) open it from a network path that is unique to each user and is mapped to U:\

They can't write to the Citrix desktop and they can't access company files outside of citrix, so a desktop shortcut won't help.

We use a script that updates the database from the default location, but sometimes it didn't work, so I posted the front end on another network path.

They are SUPPOSED to COPY the database from the network path to their mapped U:\ drive, but I've seen locking files on the distribution path, so I know some of them have opened it from there. I've warned them not to do that, but ...

I don't know that more than one of them has opened it from the distribution folder at the same time, but I wouldn't rule it out ...

You can easily stop users using the master copy of the database. Just use a sentinel file of some sort. I have an ini file to store some info. The ini file is in the users home folder, but isn't in the folder that stores the master copy. If a user opens that version, it just warns them that there is no ini file and then closes.

I use an ini file, as there's lots of code around to read ini files without having to write it all from scratch..it used to be a common thing, not so much now, but it's very useful to have.

 

[jdraw]

@jdraw - I have a procedure to check for unknown records. I don't have a procedure to confirm that the table has a PK. I noticed that the records that were duplicated had the same value for the PK field, so I checked if this was still marked as the PK for the table and it wasn't. Then after I changed it, I checked again to ensure it stayed set.

Marshall, I thought you were also checking/confirming that each table had a PK defined. If you had a routine to do so, it might help in diagnosis. It would be quick running and read-only. (AFAIK) Quick and automated to show that a PK(s) had been dropped/compromised.
If all tables have a PK by design, then identifying those table(s) without a PK would show a problem.

 

[Marshall Brooks]

@The_Doc_Man - Solved the OneDrive issue. The new users Desktop folder is on OneDrive but I was able to create a C:\Users\username\Desktop folder and put the FE there and put a shortcut on the OneDrive Desktop to open the FE.

@Uncle Gizmo - There are only 8 users, but each user has there own front end.

@gemma-the-husky - We used to have problems with users opening the master front end - or the same front end from a network folder. The database now checks and it will only open from the users U:\ Drive or C:\Users\Username\Desktop. There are checks for the location of the U:\Drive so they can't just map the master location (which isn't publicized) to U:\

Marshall, I thought you were also checking/confirming that each table had a PK defined.

I've been checking that, but I've been doing so manually. An automated check would be nice, but I'm not sure how to code it.

What I'm mainly concerned about now:
I have a weekly routine that exports the Primary Key field and certain other fields (that rarely change) to Excel Files on Monday evening. I have a program that compares last Monday's Excel file to the previous weeks Excel file. Yesterday's Excel file showed two records with identical information, including the value of the Primary Key field. The backup file which ran 15 minutes later showed the PrimaryKey field was an actual PK. The current backend shows the field is no longer a PK. From what I understand, the PK should be an autonumber field with no duplicates allowed. So I shouldn't be able to have two records with a PK of "1114", but that is what my report is showing.

That is also what last night's backup shows. I did a search for 1114. It shows up in the PK field between PK 1113 and 1115 (Record 1114). It also shows up in the PK Field between PK 3930 and PK 3932 (Record 3922 of 3925). I have a date Added Field. PK3930 was added on 6/17 at 11:07 AM. PK3930 was added on 6/19 at 12:55 PM. The second 1114 shows it was added on 7/27/2020 at 2:24 P.M., which I think was a copy from the first record. So the copy probably occurred between 6/17 and 6/19, which would put it on yesterday's report.

I just don't see how it happened or could have happened ...

 

[gemma-the-husky]

Is your "pk" just an autonumber, or is it actually designated as a unique key. If it's just a number, or even an autonumber it could still be duplicated in some circumstances. If it's a unique index/pk then it shouldn't get duplicated.

Check it in your tables.

I still expect this will prove to be programming issues rather than corruption.

 

[The_Doc_Man]

@The_Doc_Man - Solved the OneDrive issue. The new users Desktop folder is on OneDrive but I was able to create a C:\Users\username\Desktop folder and put the FE there and put a shortcut on the OneDrive Desktop to open the FE.

Sorry I didn't get back to you earlier. My car is in the shop and I've spent some time dealing with that. Bloody be-damned "check engine" lights! Back to your problem.

According to your comment, the user's desktop is on OneDrive and thus the FE file is also on OneDrive. From a protocol viewpoint, the FE can no more be on OneDrive than the BE because both parts must use SMB protocol. The FE uses its own temporary space for work areas when it is developing its lists of records to be returned. Any protocol-based misstep will corrupt the FE just as happily as it would the BE file. But now, you have put the FE part of the user's resources across a network link.

Your solution MUST be to have the FE on a purely local (i.e. outside of the range of OneDrive's reach) folder on the user's desktop system, or one of Pat Hartman's CITRIX/RDP solutions with a private folder on the RDP server. And the worst part is that OneDrive is insidious. You never know when it is going to decide to do a refresh. If it does that refresh while you have a memory transfer underway, your DB willl go bang, zoom, to the moon. (If you remember the old Honeymooners TV series). ANY corruption you get now, despite all your work, you can lay at the feet of OneDrive.

Another reason to keep the FE local, this time purely pragmatic, is that if the PC running Access has to open a remote copy of the FE, it is passing the reins of file locking control to the machine hosting the FE file. It is a rule in Windows file sharing that the machine hosting the file is responsible for its lock management. So having a remote FE means you have to use network operations to load the parts of the file that would normally be in local memory once you opened the file locally. When the FE file is physically local to each user's desktop machine, all of the file locking in the FE has ZERO CHANCE of lock collisions - because nobody else will be on your desktop to even make the attempt. When the FE file is on a remote server, then that part of the path (I'm thinking "C:\Users\" gets tapped by EVERYONE. OK, they will be doing READ DIRECTORY operations, but EVERY TOUCH to a folder has to be security arbitrated. It is bad enough for the BE to be remote, but that's the way you share it. PLEASE tell me that the BE is also not subject to OneDrive periodic backups. Because if you have a crash during a long transaction and try to restore, your backup copy will be unusable. The only really safe time to make a backup copy is when you KNOW FOR A FACT that nobody is in the DB so that you can do a manual file copy to your chosen backup folder.

If you think about a backup copy of a file being like a point-in-time snapshot of that file, then think about what happens when you take a snapshot of your family when the youngest kids are running around like wild little hellions. You get a blurred image. I watched the U.S. Navy wrestle with this problem from the late 1990s until we got something work right in 2005 (just in time for Hurricane Katrina to hit New Orleans.) I won a steak dinner on a bet in 2003, from the vendor of the backup solution who just didn't understand the dynamics of the situation. Trust me, there is a MAJOR HEADACHE just waiting around the bend.

 

[ebs17]

In addition: table definitions do not simply change at will, sometimes PK, sometimes not, values duplicate themselves or disappear. This is nonsense.
This can only be understood as corruption in the tables, and a third party is involved.

Are we talking about the backend being an Access file and all the tables being viewed being in this backend? A file is a stupid creature and cannot defend itself against external measures. If a user is currently carrying out write operations and another user or an admin service is simultaneously carrying out copying or compression measures on this file, this is potentially problematic and can lead to corruption.

 

[Marshall Brooks]

I wrote this yesterday prior to Reply #194, but it didn't post ...

@gemma-the-husky In each table, there is a field named PrimaryKey. It is set as an Autonumber field, and it also has the "Set As Primary Key" property and the little Key icon by it in design mode. (Or it did in last night's backup and no longer does currently).

What I'm not seeing - as you said, I shouldn't be able to have two records with identical values in a defined PK field.

What @GPGeorge said, could have made sense with the timing - i.e. somehow the database created a record with a duplicated primary key, then when the database did a C&R, it found the duplicated PK value so it removed the PK field. The problem with that theory is everything appears to point to this duplicated record being created on 6/18, but I have backups from 6/18, 6/19, 6/20, and 6/24 that have the PK Field still defined as a PK.

I don't doubt that is may be programming rather than corruption. It also somewhat appears that sometimes records are being copied across tables, but I don't know how it is happening. Users shouldn't be able to do that, and I don't have VBA code to do that exactly, and the VBA code that I have that approximates that, the other users don't have permission to run, and when I've run it, I haven't had issues

 

[Marshall Brooks]

According to your comment, the user's desktop is on OneDrive and thus the FE file is also on OneDrive.

This is not the issue and is resolved - although it wouldn't have been if you had not commented - much appreciated. See Reply #192. The FE was on OneDrive on Monday night and Tuesday morning, but the user isn't actively working in the database yet, and the file duplication happened apparently around the 18th.

PLEASE tell me that the BE is also not subject to OneDrive periodic backups. Because if you have a crash during a long transaction and try to restore, your backup copy will be unusable.

I don't believe the BE is subject to OneDrive, but I'm not certain. The BE is on \\networkname\networkdirectory. There is no OneDrive in the directory tree and there was in the new users Desktop folder location. OneDrive is new to us at the user level, so I'm not certain how it and where it is being utilized.

Your solution MUST be to have the FE on a purely local (i.e. outside of the range of OneDrive's reach) folder on the user's desktop system, or one of Pat Hartman's CITRIX/RDP solutions with a private folder on the RDP server.

Let's look at the second part of this ... Half of our team (8 users total) operates with the FE on the users desktop on the local machine (C:\Users\username\desktop). The newest team member is using a SHORTCUT on the OneDrive Desktop to open the FE in the stated folder.

The other half of to team (4 users) accesses the database through Citrix. Per policy, they can't install the FE on their local PC. For them, the FE is installed on a network (\\networkname\username\) folder that only the individual user has access to. Could this be part of the issue? One of our db admins on a different database tends to blame it, and other databases in the company the don't rely on Citrix don't seem to have the issues we typically run into.

I don't know how much support I might get trying to have the FE moved to a private folder on the RDP Server. I'm not familiar with Pat's solution. Generally when there has been an issue, the Citrix team has told me that Access is not supported on Citrix.

Question 2: You mentioned VERY early in this thread that the issues I mentioned could happen if a record from one table was pasted into a different table, and I said that seems VERY likely what was happening (at that time, but somewhat still). How could this happen? The users can't directly access the tables, the layout/datasheet/Excel view is disabled, the navigation pane, ribbon, and navigation bar and F11 are disabled. Is there something obvious I overlooked like Ctrl-C in one form and Ctrl-V in another form? I know it seems silly, but it's a serious question.

In addition: table definitions do not simply change at will, sometimes PK, sometimes not, values duplicate themselves or disappear. This is nonsense.
This can only be understood as corruption in the tables, and a third party is involved.

I don't want to get into an argument or semantics of whether or not it is considered corruption. I have tables in the backup file that were labelled as PK and are not labelled as PK in the current file. I have tables in the backup file where two records have identical values in the PK field, which is not supposed to be possible with Access, as far as I understand it (which isn't that far).

It may well be that a third party is involved, but I'm not sure how to find that third party and prevent it.

Are we talking about the backend being an Access file and all the tables being viewed being in this backend? A file is a stupid creature and cannot defend itself against external measures. If a user is currently carrying out write operations and another user or an admin service is simultaneously carrying out copying or compression measures on this file, this is potentially problematic and can lead to corruption.

Yes, backend is an Access file. Tables in question are linked into the FE from the BE. As far as I can tell, the third sentence shouldn't be happening. Most users don't know where the BE file is located to try to run backup or compression on it. I run a nightly unattended routine that does this, but there is a routine that kicks all users out first and the routine will fail if anyone is in the file (and I can verify it failed), but that hasn't happened during the time the duplication has occurrred.