Solved Automated search for Database Corruption Assistance (2 Viewers)

jdraw

Super Moderator
Staff member
Local time
Today, 09:37
Joined
Jan 23, 2006
Messages
15,437
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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
@ 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

George Hepworth
Local time
Today, 06:37
Joined
Nov 25, 2004
Messages
2,158
@ 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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
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

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
27,677
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

George Hepworth
Local time
Today, 06:37
Joined
Nov 25, 2004
Messages
2,158
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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
@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

George Hepworth
Local time
Today, 06:37
Joined
Nov 25, 2004
Messages
2,158
Thanks for the clarification. Sometimes the forum context does leave out a lot of relevant detail.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:37
Joined
Sep 12, 2006
Messages
15,805
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

Super Moderator
Staff member
Local time
Today, 09:37
Joined
Jan 23, 2006
Messages
15,437
@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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
@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

Super Moderator
Staff member
Local time
Today, 14:37
Joined
Sep 12, 2006
Messages
15,805
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

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
27,677
@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.
 
Last edited:

ebs17

Well-known member
Local time
Today, 15:37
Joined
Feb 7, 2020
Messages
2,071
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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
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
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
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.
 
Local time
Today, 09:37
Joined
Feb 28, 2023
Messages
666
or one of Pat Hartman's CITRIX/RDP solutions with a private folder on the RDP server.
I'm not familiar with this. Is there more to it than ensuring the FE is installed on the Citrix server and each users runs their own copy of it?

Could the front end being on one network folder and FE being on a different network folder (neither part of the Citrix Server that is called both of them) cause the issues I'm seeing?

Finally, we run multiple servers. Would the one user running his FE on Server 1 and another user running his FE on Server 2 (from folders on the Server) cause any issues? (I'm assuming not - conceptually this would be no different from me running the FE from my local computer and another user running the front end from their local computer.)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
27,677
1. You said
The newest team member is using a SHORTCUT on the OneDrive Desktop to open the FE in the stated folder.
The shortcut can be anywhere since it isn't active once the launch of the targeted file/app occurs. Nothing else happens to the shortcut. If I remember this correctly, the shortcut is treated as though it was a one-line command line equivalent to "RUN X with parameter Y". So running via a shortcut is SIMILAR to running from a batch script... a very SHORT batch script. (Could be wrong - but I think that is correct.) The shortcut's properties and content are not altered, so even if OneDrive was involved, it would have no updates to make to the cloud copy.

The crucial aspect is where the targeted app resides. In theory, since the implied launch occurs on the user's PC, the launched code - the FE in this case - will have to have a clear, unobstructed path (in the network sense) to the PC and its memory. If you can't put a shortcut on the desktop but CAN have a user's files on that machine in a local folder, don't make the shortcut a desktop shortcut, but instead make it just an icon in the user's home folder. (Technically, a desktop shortcut is just another file in the implied folder that is the desktop.) Open Windows Explorer (or whatever they are calling it these days) to the user's working folder and launch from there.

2. You asked
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?
As long as each user's FE resides in a folder where a different user won't try to open it, you should be OK.

The thing you want to avoid is that two users will open the same physical file. (Not talking about users opening COPIIES of the same file.) When two users open the same file simultaneously, you have locking issues AND you have the potential for the file's contents to get scrambled. That is why we tell you to copy the FE and only share the BE file.

3. You asked about using CTRL/C and CTRL/V as copy/paste to move bulk data from one place to another.

If you have any tabular forms (similar to datasheet view) where a group of controls could be copied at once, I suppose it is possible to get all bollixed up with a bulk copy/paste. People used to Excel-like behavior have some bad habits to break and that is one of them. But for that to happen, you have to have a grid-like form in two places where a copy-from and a paste-to can occur. I would think you would know whether you had such a thing.

Let me digress for just a moment. In Excel, you have the explicit ability to select multiple cells and do a CTRL/C copy, then move to another sheet or segment thereof and do a CTRL/V paste. When you do that, though, Excel asks you whether you wanted to jam everything into a single cell or do a paste maintaining separation of cells or do some other type of paste. It also matters whether you select enough cells in the desitnation area to hold the cells you selected from the copy area. In any case, you get options.

When you do that grid-oriented copy/paste with Access, you don't get options because Access treats grids differently. In Excel, each cell is a unique entity unto itself. EVERY CELL can be something else. In Access, the cells in a tabular grid are constrained by record layout definitions and explicit or implied data types. You can build an Excel spreadsheet with a column that you are using as a scientific number. You can then copy/paste a number from that column into another column that is text. (Or vice versa.) And Excel WILL NOT CARE. In Access, try that and you might get a type misatch. Therefore, I would expect bizarre behavior in trying to do a bulk copy/paste in any grid-like display in Access.

Can this happen? Yes. Does it happen for you? Only if your users can get to grid-like displays.

This is getting long. I'll start a new one.
 

Users who are viewing this thread

Top Bottom