Solved Automated search for Database Corruption Assistance

[Pat Hartman]

Thank you, but you keep answering questions other than the ones I asked.

So, you don't want to implement security or customize menus? You were talking about both earlier. You can roll your own or you can use a concept created by someone else. The security in that version of the switchboards also allows you to customize the switchboards.

Probably the best solution is to stay on topic. This thread is 160 posts long which is way too long to be useful. No one is taking the time to read the whole thing any longer so you are seeing the same suggestions repeated, even by me. I'll go away now since I'm pretty sure I can't help you beyond what I've added today.

I'm not sure if the above is a true statement?

It is. If the user can get to the Nav pane and open a table, he can delete/update/insert rows.

(It is useful to see how I could prevent edits, though.)

Excel has the provision to lock spreadsheets. This could be how you stop the problem you are having. At the moment you have not clearly identified whether the Access table is getting changed or the spreadsheet. You just know they are different.

Most of the team doesn't want to bother or doesn't trust themselves to add records.

So if the users don't want to add records, who is doing it?

 

[Marshall Brooks]

Grrrr - we are talking about different things and you are abandoning the thread ...

I agree the thread has taken too long and wandered somewhat. Stream of consciousness - it was all loosely related to database corruption.

So if the users don't want to add records, who is doing it?

As I said - legitimate additions 90% of the time, I am. If you mean random records that seem to appear from other tables, I suspect as @The_Doc_Man theorized, probably cut and paste when DS view was enabled.

It is. If the user can get to the Nav pane and open a table, he can delete/update/insert rows.

Wrong true statement. I have no real doubts about that.

What I meant was that I'm not sure it is a true statement that if I have the nav pane disabled and I have every form set to Allow Deletions = No, this is sufficient and I don't need to add any beforeDelete or BeforeDeleteConfirm code to each form. From what I have been able to tell, though, and from Google, the above seems to be a true statement.

Excel has the provision to lock spreadsheets. This could be how you stop the problem you are having. At the moment you have not clearly identified whether the Access table is getting changed or the spreadsheet. You just know they are different.

Correct observation, but ...

It probably wasn't clear - there are a lot of exports to Excel. The two I've mentioned are:

• Nightly, we do an export to Excel of the table fields. These are shared for other teams in the company to view. They aren't really compared to the database, unless someone sees an obvious error in them.
• I started doing Excel exports to check for added deleted records. This runs weekly and exports 4 or 5 fairly stable fields (the PK and enough other information to identify and find the specific record. Each week, I do a machine comparison between these files. I don't think these files are being tampered with. The time stamps are not changing. My management knows I set them up, but I don't think they know where they are. Nobody other than myself opens them other than me. Correct statement that I don't really see how a record that was in the backup before the previous export, and after the previous export and in the current export is happening. Possibilities:
  • Somehow the export skipped one record in one table that night?
  • Someone for some reason opened the previous Excel file and deleted that record only - and in that table only.
  • Someone deleted the record before the Excel export ran, but after the backup, and then restored the record after the export and before the next backup with the same PK as previously, etc.

None of those seem likely - and many of them don't seem possible.

So, you don't want to implement security or customize menus? You were talking about both earlier. You can roll your own or you can use a concept created by someone else. The security in that version of the switchboards also allows you to customize the switchboards.

I never said I wasn't implementing security. I just haven't fully gotten there yet, and the plan has evolved.

• My original plan was to replace the Navigation Pane with my own custom Navigation Pane and hide the original one, and add additional buttons to the new nav pane for hidden features. There were two ways to do that and neither one of them would work properly for us.
  • The first attempt was to create a "switchboard" with a Navigation Group. So the main form would have a subform area and what was FormA would now be shown as SubFormA on the switchboard. Problems with this:
    • The main problem was it wouldn't work at different resolutions. With Forms - the status bar is always at the bottom of the form. With this approach, the status bar for the Navigation Pane was always at the bottom of the window, but the status bar might be off the screen. I could probably make it work if everyone ran 1920x1080 at 100% scaling, but I don't even do that on all my screens.
    • My FormActivate code never fired - I think b/c it was loading as a subform instead of a main form.
    • I wanted to add buttons below the navigation buttons, but there was no way to scale that navigation menu smaller than the subform or to add buttons below the navigation buttons.
• Second approach would have been to go the other direction - i.e. create my switchboard form and add it as a subform to the left of my FormA, FormB, etc. I think this would have worked, but I couldn't minimize to the right the subform (I don't think), like I could with the built-in Nav Pane. (More accurately, I think I could, but I couldn't get the rest of the form controls to shift left with it, so it wouldn't gain me anything.
• I'm ending up using a standard (custom) switchboard form. The Nav Pane and the toolbars/ribbon are both going to be hidden. The shift Key is disabled.

I created my own switchboard rather than modifying the one you (graciously) provided b/c:

• Mainly, I knew how to create it as opposed to learning how to create/modify yours.
• Once I tried your sample database and saw the way the switchboard operates (and @Gasman explained it to me), I knew how the concept of the switchboard worked.
• My switchboard is doing more than just opening forms in the database. It is also (for certain users) running macros/VBA which can't be done after the ribbon is hidden (unless I provided a batch file, etc.). I wanted the buttons color-coded to what their function was. I believe I certainly could have modified your sample switchboard to have additional command functions and to have different colored buttons and resize the buttons, but by the time I did that, it is about the same amount of work either way.

 

[Pat Hartman]

it was all loosely related to database corruption.

But the problem isn't database corruption as we have been trying to explain to you all along.

Somehow the export skipped one record in one table that night?

Not a chance.

Someone for some reason opened the previous Excel file and deleted that record only - and in that table only.

Possible

Someone deleted the record before the Excel export ran, but after the backup, and then restored the record after the export and before the next backup with the same PK as previously, etc.

Unlikely unless they are messing with you.

I'm ending up using a standard (custom) switchboard form. The Nav Pane and the toolbars/ribbon are both going to be hidden. The shift Key is disabled.

Yep, and that's where we got into the hardcoding discussion. I gave you an alternative that uses defined security to control what the switchboard shows. It just won't be grouped, you can control how it is sorted by changing the sequence numbers. There is even a renumber option for the custom switchboard table that increments by 10 or whatever you want. If you use the big button version, the numbers have to be specifically 1-12 or the hard coded buttons won't work. You have to adjust the numbers manually. I do it when I'm using one of the older versions of the switchboard.

A lot of what you are now doing could have been done with the custom switchboard in the "security" database because it uses custom pictures on buttons. You could make them colors or images. You could use the concept in your own version also.

When you export the spreadsheets ---- LOCK them to prevent accidental changes. I wouldn't even say "tampering" although that is certainly a possibility. Also lock the BE. You can even, with the help of your IT folks, move it to a folder that will be hidden to all but you and the IT admins. You can link the BE and it will stay linked but the users will not be able to navigate to the folder.

\--------------------

This thread is way too big. There are too many live topics. No one will read all the posts. You need to take stock of what you have done and create a summary. Then start a new thread and include a link to this one, for each topic that is still open. The link is important so don't forget to include it or you will make experts angry with you for "double" posting.

For example, did you ever rebuild the database from scratch, just in case one of the tables is corrupted? It happens but the symptoms are not what you are experiencing.

 

[Gasman]

The link is important so don't forget to include it or you will make experts angry with you for "double" posting.

Not just the experts either.

 

[Marshall Brooks]

Database has been rebuilt from scratch. Will reply to other comments Monday.

 

[Marshall Brooks]

But the problem isn't database corruption as we have been trying to explain to you all along.

Concur - that is my term for it, but not what is happening. Old habits die hard ...

When you export the spreadsheets ---- LOCK them to prevent accidental changes.

Okay - I'll look into that. Locking won't prevent someone saving a copy and filtering/editing it, will it?

Also lock the BE.

How? - and does that prevent users from editing the records?

You can even, with the help of your IT folks, move it to a folder that will be hidden to all but you and the IT admins. You can link the BE and it will stay linked but the users will not be able to navigate to the folder.

Hidden, but they will still need write access to the folder, correct? I know when we got new team members, they couldn't use the database unless they had write access to the folder the BE was stored in.

I won't see the replies until Monday, so have a great weekend!

 

[Pat Hartman]

All that is hidden is the folder name which means the users cannot navigate to the file to open it because they can't see it. It doesn't prevent access. You do the linking and your security level will allow you to see the folder so you don't have any trouble getting there. Once the BE is linked to the FE, they will have whatever access, your FE allows. Sorry, I can't remember what this feature is called. You need your IT person because you probably don't have the security to make the folder invisible.

I don't do much with Excel. I'm pretty sure that locking only prevents updating.

 

[Marshall Brooks]

I'll look into both. On the BE, I think you are just referring to hidden folders. I could be (probably am) mistaken, but I think that might be the same for everyone, i.e. if it is hidden, it is hidden for me also, but if I turn on "Show hidden folders" (it is off by default), it is visible by everyone.

Still - another layer of protection.

I don't publish the location of the BE and that is another reason I plan to hide the ribbon as currently anyone can access the linked table manager and find exactly where it is. (Again, most of our users won't know they can do that, but it only takes one ...)

 

[Pat Hartman]

i.e. if it is hidden, it is hidden for me also

I believe permissions control the property.

 

[Marshall Brooks]

A bit of an odd question, but it is related ...

I added my search for "###" and created a macro called "Database_Corruption_Check" (I understand it is not a corruption issue) to run it.

I created a batch (.cmd) file to run the macro and then open the current and previous Excel files in Beyond Compare.

The start of the batch file looks like this:

rem echo off
"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\<username>\Desktop\OurDatabase.accde" /x Database_Corruption_Check
"C:\Program Files\Beyond Compare 4\BCompare.exe" "Table1_DB_Verification"
pause
"C:\Program Files\Beyond Compare 4\BCompare.exe" "Table2_DB_Verification"

Everything works, but ...

I added the Pause steps so the batch file will say "Press any key to continue ..." Otherwise it would open all the Beyond Compare sessions at one time.

I removed the Pause command after the MS Access call, but the batch file is still waiting until I close the database before it runs Beyond Compare.

I'd prefer to leave it open and open BC simultaneously, and I expected it to do that.

What can I change?

I could move Access to the end of the file and that would work, but I'd prefer to check it first.

 

[Marshall Brooks]

Figured it out - it works if you use start "" - i.e.:

rem echo off
start "" C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\<username>\Desktop\OurDatabase.accde" /x Database_Corruption_Check
"C:\Program Files\Beyond Compare 4\BCompare.exe" "Table1_DB_Verification"
pause
"C:\Program Files\Beyond Compare 4\BCompare.exe" "Table2_DB_Verification"

The empty quotes are a dummy title. From https://stackoverflow.com/questions/11010834/how-to-run-multiple-dos-commands-in-parallel

 

[Marshall Brooks]

Slight update ...

So far the following have been implemented:

• Datasheet View disabled on all forms.
• Nav Pane disabled.
• Shift Key bypass disabled.
• Deletions disabled on all forms.
• I thought I had all backend tables with a defined PK, but I checked the backups and some of them are not. Will correct that soon.

Since these changes were made:

• We have not had any "inconsistent state" errors.
• We have not had any ### entries.
• We have not had any records appearing in the wrong table.

However, on yesterday's validation report comparison. I noticed on one table, one record was duplicated (including the PK, but that is just a field and not an actual PK, apparently). And one record was missing/deleted.

I am thinking the change happened on the same day and the duplicated record overwrote the deleted one, but I can't say that for sure. The deleted record was in the 29-Aug backup. The 30-Aug backup did not have the deleted record. It did have one copy of the duplicated record, but I didn't see a second copy below it. When I try to repeat the search in the 30-Aug backup, it gives "Unrecognised database format". The 31-Aug backup does have that duplicated record in it twice.

There is a duplicate record button on the form, but it prompts you for changed information and doesn't copy over all of the fields. If someone used that, they would have had to purposely copy over all the fields that were omitted with the same values as the existing record and then they would also need to delete the record that became missing.

Does anyone have any ideas how this is happening and how I can prevent it?

Thanks!

 

[jdraw]

Marshall,
I was just scanning this thread, and saw the last post was Sep 6/23.
Here you were asking a question "Does anyone have any ideas how this is happening and how I can prevent it?"

You have obviously progressed beyond this -- you may want to tell/post what you found and leave readers with a solution or link to follow-on activity.

An interesting thread and you have certainly learned and experienced many things.

 

[Marshall Brooks]

Not much has changed since Sep 6. (The thread could use a summary, though).

I'm not sure I posted the code for the original question - that would be this:

Function DB_Corruption_Check()
    ' https://access-excel.tips/access-vba-loop-through-all-tables/
    ' https://www.access-programmers.co.uk/forums/threads/using-vba-to-search-all-tables-in-a-database.234890/#post-1198193
    Dim Found As Boolean
    Dim tdf As DAO.TableDef
    Dim sTable As String
    Found = False
    For Each tdf In CurrentDb.TableDefs
        If Not (tdf.Name Like "MSys*") Then
            sTable = tdf.Name
            Call SearchTable(sTable, "###", Found)
        End If
    Next
    If Found = False Then
        strResult = Dialog.Box(Prompt:="### Not Found.\n\nTo manually search, look for [#][#][#]" & "", Buttons:=(0 + 64), AllowCopyToClipboard:=True)
    End If
End Function

As far as what I found since 6-11-2023: The main issue seems to be that one of my tables did not have a defined PK. It had a field named PrimaryKey, but it allowed duplicates (I believe), and it was not SET as the primary key. I got exclusive access to the BE and changed that and the errors have ended - although I still check weekly.

The other changes were basically details in Reply #172, but if I had to summarize them - in importance:

• Datasheet view is evil. I didn't realize it, but it is fairly simple to copy a record from one table in DS view and paste it over a record in a different table in DS view. You end up with what I was initially seeing, data in fields does not fit (#########), field contents don't look at all like the field definitions. Records from Table 1 are still in Table 1 and now also in Table 2 and records in Table 2 cannot be found. First step was deleting that.
• It is apparently easy to delete a record from a form if deletions are enabled. We got rid of the delete record button, but I think just pressing the delete key will let you delete a record. I disabled AllowDeletions and AllowAdditions on all forms. (I ended up adding a button to add or delete (and a copy) button, but only myself and one other semi-admin see those buttons. When you do that, in the button code you have add Me.AllowDeletions = True before the delete statement and Me.AllowDeletions = False after it. For Additions, it is even more tricky - you have to add Me.AllowAdditions = True before the addition statement and then Me.AllowAdditions = False in the Form_AfterInsert event.
• The shift-key bypass is dangerous, so it is disabled on the distributed .accde file. It is still allowed on the .accdb file, but hardly anyone besides me knows where that is.
• Navigation pane and toolbar is dangerous for three reasons:
  • It exposes the data tables, which means users CAN add and delete records directly. (The tables are hidden, but you can show hidden objects).
  • If it isn't an .accde, users can get into your VBA code, which means they can bypass anything they want to bypass.
  • It exposes the linked table manager, which tells users where your master BE is located, and from there they can likely find where the master FE is located (ours are close in structure).
• To hide the navigation pane and toolbar, I borrowed heavily from @isladogs CAI (Control Application Interface) and added a variation of Pat Hartman's Switchboard form to replace the form selection on the navigation pane.

The next update will be adding "privileges" to a user table in the backend - i.e. if your username isn't in the table, you can't even open the database. If this yes/no field isn't checked, you can't fill in these fields. If a different field isn't checked you can't run (or even see the buttons to run) reports. If a different field isn't checked, you can't add/delete records, etc.

 

[jdraw]

Good stuff Marshall. It just may help someone in similar situation.

 

[Marshall Brooks]

I know this is frowned on, but I didn't want to start a new thread for the same issues.

We have started having some problems with our Access Database lately ...

Two weeks ago, in one table two records with the same value in one field (for Example Field A had value 001) were missing/deleted, and two other records in the same table with a different (same) (for Example Field A had value 004) value were duplicated.

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.

I checked all of the tables in the backend and made sure they had dedicated primary keys. I checked the backend again and verified they still had defined primary keys.

Today, there was a garbage record in one table - similar to if a record was copied from a different table, and one record in a different table was duplicated - including the primary key. I deleted the records and checked the backend and the primary key field was NOT defined as a primary key.

Questions:

How could the primary key field be changing to not being a primary key and how can I prevent this? I spoke to another db admin and he pointed out that Access using warns you to prevent this if you try to do it manually with exclusive access to the BE, so I don't think this is something being done intentionally.

How can records be being either duplicated and/or copied from one table to another one? The tables are not easily accessible from the front end. Only two people have access to the VBA macros to duplicate records and I'm sure we didn't duplicate the records in question.

(I'm planning to add data macros to show when records are added or deleted, but I haven't done so yet ...)

Thanks in advance.

 

[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.