Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

Switchboard close code - That is pretty clever. I like it for a single-form only application. I wasn't seeing where the code was to hide it, but I found it now.

You can hard code anything. The issue comes when you change your mind or need to add another object between two existing objects.

I guess it was a more basic question - i.e. does having it set up that way gain me anything over just creating a form named Switchboard that opens with the database on startup. The table to edit seems unintuitive to me. If it need to add anything, I just go to design view and shift the buttons there.

 

[Gasman]

The table to edit seems unintuitive to me. If it need to add anything, I just go to design view and shift the buttons there

So you have never created data driven systems?

Think of your combos, your listboxes, hell even the data itself.
If you have a flag to change the sort order, then no need to change the code. If you have a field to change the field(s) to order by.
Just depends on how much you want to data drive the system.

Need a new button?, add a record.
Your way, change the form.

Even if I created my own switchboard form and did not use the builtin version, it would still be data driven.

 

[Marshall Brooks]

No - not really data driven systems - other than as you said, the data itself.

created my own switchboard form and did not use the builtin version

Okay - here's a basic question. What is meant by the builtin version?

I went to another website and it mentioned a switchboard manager. I added that to the toolbar and started working through it and it added a table like Pat Hartman's named tblSwitchboardItems, but it never created a form that I could see or any way to load the switchboard.

I copied Pat's Large Buttons form into my database, and it would work, except I want more than 12 buttons, I want them somewhat grouped, and I want some hidden depending on conditions.

Now I'm creating my own form, but I'm not sure if there are any disadvantages to this - other than what you explained above.

 

[Gasman]

Well the form is already created for you. No work involved unless you want to tweak it, like I did for security levels.

Been ages since I created one though.

https://www.youtube.com/results?search_query=access+switchboard

In 2007 the switchboard manager is in Database Tools.

 

[CJ_London]

Agree with Colin - too many questions for one thread - start a new thread for each question

. You aren't the only member of the forum.

Agree but many members won’t want to get involved with your frenzied approach

 

[Marshall Brooks]

Okay - per CJ London's suggestion, this thread will only be for database corruption. Any questions I have about user interface improvements will be made in new separate threads.

I'm thinking (like everybody else told me) that our issues are not really related to database corruption.

To recap - 25-Jul-2023 I revised the front end to only open from the mapped U:\ drive or the users desktop. 31-Jul-2023 I revised the front end to remove datasheet view.

We haven't had any crashes (inconsistent state, database can't be read) errors since that time, but it was an intermittent error before.

Late on Thursday, 3-Aug-2023, I exported the backend tables to text files and re-imported them. Everything seemed to be working well. I ran the Excel Validation records after I did this since I reset the PK numbering and the reports sort on that. (Otherwise the reports would show hundreds of errors where the PK values did not match.)

Tuesday - I did a compare of the Excel validation reports (So a compare between last Thursday night and last Monday night. Only one table had errors:

• One record was shown as added to the table - i.e. it had an entry in the current report (Monday the 7th at 6:45 P.M.) and not in the prior report (Thursday the 3rd at 5:37 P.M.) Theoretically, this could happen and not be an issue. Other people CAN add records to the database and they wouldn't necessarily notify me, but this isn't a recent record. I checked our backups from 1-Aug-2023 and it was in that report. That report shows it was added November 1st, 2022. It's POSSIBLE it got deleted after 1-Aug and added back before 4-Aug (it got difficult to keep up with), but it should have been in the Thursday version of the database. I checked the exported .csv files and the copy of the BE after I imported the .csv files and it is in both files. THAT makes me somewhat question or export/validation routine, but it is simply a query of 4 fields from the table exported to Excel. I don't see any rhyme or reason for it dropping a record, and especially for it doing it Thursday but not doing it the following Monday. That's not my primary concern, though:
• One record was shown as deleted from the table. This was a fairly recent record also - it was shown as added July 24, 2023. I searched and it was not in the database. It was not in Monday night's backup. It WAS in Thursday nights backup. So sometime on Monday, it got deleted somehow. That IS concerning.

Questions:

• What are the possible methods of deleting a record from an Access table? The only methods I know are Datasheet View of the form and right-click Delete, or from the table directly by doing the same thing (or via VBA code). DS View is blocked, tables are hidden, VBA is not accessible and I don't think I've told any of the users how to do this. (But you don't know what you don't know. For all I know, Shift-Del might delete a record and I have never tried that.)
• What can be done to prevent a record being deleted? (Back in Reply #55, @Pat Hartman mentioned added code to the BeforeDelete and BeforeDeleteConfirm events, but she never said what to add.)

Thanks in advance!

 

[Marshall Brooks]

@Pat Hartman - Thanks!!! (I am listening and learning, whether it seems like it or not.)

The delete button is active in all form views, not just DS. If you don't want people to ever delete rows, you can just set the AllowDelete property to No.

And there is is - clear as day "Allow Deletions = Yes" I need to release a new FE today, before I worry about the other changes.

I guess you didn't look at the sample database I referenced. I can't give you code because I have no idea how you are handling security. Here's some code from the sfrmDetailItems form in the switchboard with login sample.

Yes, I did look at the sample database. It wasn't clear to me where the beforedelete and beforedelete confirm code was located. What I saw was mostly talking about the switchboard and about event logging. There was a LOT more to it that I didn't look into, but as you mentioned, not all of the forms were updated.

Three followup questions:

• I'm the only one that should be deleting records from the database. I'm comfortable with going to the tables in the backend to delete records. The tables are hidden on the nav pane and the nave pane is going to be hidden in the next version of the FE, but I can either access the tables through a different front end that I maintain, or I can enable the nav pane myself through the switchboard with buttons that will only be visible to me. With the constraints above, do I still need to add code to the form's BeforeDelete event, or is turning off allow deletions sufficient?
• If I only want myself to be able to delete records, I assume the If statement could just be:
  If Environ("Username") = "<myusername>" Then
  correct?
• In reply #55, you mentioned changes to both the BeforeDelete events and BeforeDeleteConfirm events, but above you only show code for the BeforeDelete events? Do I need to update both (assuming from the first question that I need to update either one), and if so, do I use the same code for both?

Thank you again!!!

 

[Marshall Brooks]

@Pat Hartman - Thank you, but you keep answering questions other than the ones I asked. The reason I keep asking is b/c I think I know how things work, but obviously, things don't always work the way I think they do.

I released an updated version of the FE yesterday. The updated version has "Allow Deletions - No" in the properties of all forms.

As I understand it - nobody (including me) can delete any record from the front end. (Other than technically for this version of the front end, they could, if they knew how, change the taskbar to show hidden items, select the tables, open a hidden table, and delete the record from there).

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

If the above is true, I'm not sure I need to add/edit each form's beforeDelete event? I seems somewhat superflous/overkill if deletions aren't even allowed in the form properties.

You previously mentioned changing each form's BeforeDeleteConfirm event, but you haven't mentioned that again or whether any changes are required for it?

As far as the other events:

• Everyone needs Edit Access. We have some employees in other departments who would like read-only Access to the database, but don't necessarily need real-time information. For them, we export the database data to Excel nightly and give them access to the Excel files. (It is useful to see how I could prevent edits, though.)
• Currently, everyone has add record privileges. I don't want to restrict that at this time. I probably add over 90% of the new records. Most of the team doesn't want to bother or doesn't trust themselves to add records. If a record is added by someone else, I should see it on my weekly validation reports and I can edit or correct it at that time. (Again, it is useful to see how to limit it if that becomes necessary.)

Thanks to all for the assistance.

 

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

 

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

 

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

 

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