Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

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 record will be overwritten with "###" - random number of hashtags, but always at least three.

For the first two issues, I have a fairly complex procedure, but it works - essentially I have a query for each table with 3 to 4 fields that rarely change and I export that query to Excel and compare it to last weeks report and look for items that are in last week's report and missing from this week's report.

For the last issue, I open each table manually and search for "[#][#][#]" (without the quotes).

I'm looking for a way to use VBA to automate the process above - i.e. how would I write code that will search all fields in all tables and if it finds "###" report what table or record has the error so that I can fix it from the backup - or report no errors found or just the table with the error.

It would be a plus if the code could run unattended and generate a report that I could view later, but that isn't completely necessary.

All help appreciated!

 

[MajP]

Those symptoms do not sound like corruption to me. If it was then you need to fix that and stop it. You have bigger problems coming.
Since I doubt it is corruption you need to find out the code or forms that are improperly designed that are causing this.

 

[Minty]

I agree with MajP - this sounds like a dodgy cut and paste or someone entering rubbish data.
Corrupt records don't disappear, in fact, a genuinely corrupt table record is normally very difficult to get rid of by normal means.

Records getting overwritten, sounds more like user error, or a poorly designed interface, allowing data to be overwritten when the end user doesn't realise they are on the wrong record.

 

[The_Doc_Man]

Out of curiosity, is that an SQL engine back-end or a native Access back-end?

I'm with MajP in that none of those sound like corruption of the kind that a Compact & Repair would recover.

Typically, while corruption CAN lead to loss of records that appear to have been deleted or overwritten, the fact that you can recognize the alteration at all suggests not corruption, but a faulty program actually overwrote something.

The hash-tag epidemic probably occurs if you have a function that is trying to write something that won't fit in the space allotted to it so it gives the old "improper format" indicator. Again, not corruption but a program error.

Your idea of comparing to old Excel exports works except that anything that legitimately occurred between your export and your comparison would be different - yet not wrong. To track this down, I might look into data macros on the tables where this problem occurs. Keep an audit log on those tables. Also, if something overwrites a record and yet nothing much happens, there must not be any constraints on the table that got overwritten, or else there is one helluva coincidence that what was written didn't trigger a constraint error. That might be useful if you can figure out a reasonable constraint given what was overwritten.

 

[MajP]

I think I would apply an audit trail, to see if you can use that to identify where and when this takes place. There are several

Audit Trail

Hi Everyone I am trying to use Allan Browne's Audit Trail Code (http://allenbrowne.com/AppAudit.html) and I am getting the following error:- The code on the Subform is as follows:- Option Compare Database Option Explicit Sub AuditChangesSub(IDField As String, UserAction As String) 10...

www.access-programmers.co.uk

This works at the form level so if users are editing directly into tables or queries this will not catch that. If you allow this then you have bigger problems. You may want to add auditing to any code that runs action queries too.

This sounds highly likely

this sounds like a dodgy cut and paste or someone entering rubbish data.

 

[Marshall Brooks]

Fair comments, but ...

I inherited the database around 2008 and it's been in use since around 2002.

I can't really tie the error to a specific module, and it isn't a poorly designed interface ...

For example - it only happened once, but a record from Table A got pasted into Table B. The Primary key was duplicated in Table B from the old Primary key in Table A (so there were two records with the same primary key, and all the fields from Table A got pasted into Table B (so for example, if Field 4 in Table B was Last Name and Field 4 in Table A was SSN, I had an entry in Table A with an SSN of Brooks, etc.

I don't think the hash tag is improper cut and paste - it happens too randomly - i.e. a field that doesn't change for 6 months and doesn't need to change, suddenly does change to ###.

Audit trail could be useful. Changes are made at the form level. There is nothing preventing changes at the table level, but most of our users wouldn't know how to do that.

Excel method does work - I also picked only key fields that don't change often.

What I am looking for is how to find the "###"'s.

I think it might be workable by combining:

Access VBA loop through all Tables using DAO.TableDef

This Access VBA tutorial explains how to loop through all Tables using DAO.TableDef in Access.

access-excel.tips

and

Access VBA loop through Table records

This Access VBA tutorial explains how to loop through Table records or Query records in Microsoft Access.

access-excel.tips

But I haven't experimented with it yet.

 

[Marshall Brooks]

Fairly often, we will see a message that "The database has been placed in an inconsistent state and must be rebuilt, ..." after a C&R on the backend, sometimes records will be missing or I will see errors like the ###.

 

[ebs17]

Does each user have their own front end? If not, one should not be surprised about strange conditions.

Data corruption is not a normal condition. You shouldn't repair it automatically, but eliminate the causes. I mentioned the first measure.

You should also rule out that tables are defective => create new tables (do not copy) and transfer the contents from the old tables.

Maintenance work on an Access backend should only be carried out with exclusive access, i.e. there must be no further access at the same time.

 

[MajP]

after a C&R on the backend, sometimes records will be missing or I will see errors like the ###.

This kind of changes things. If his happens after C&R then this could be corruption. Again if you are trying to fix this after the fact you are wasting your time. Things will likely get worse until the DB is unusable. That would be like every time you go outside you get sunburned, and your solution is to buy more aspirin and aloe. Might want to apply sunblock

 

[Marshall Brooks]

Yes, each user is SUPPOSED to have their own front end. There are only 8 users. Unfortunately, 4 of them have to open the front end from a network drive (mapped locally), and unfortunately some of them open it from the distribution folder, but they are SUPPOSED to open their own copy from their own mapped network location.

The back end was recently recreated by copying the data to a new blank back end. That fixed some errors with not being able to read the backup files, but the random errors still occur.

Maintenance work on the backend is done with exclusive access.

 

[MajP]

The back end was recently recreated by copying the data to a new blank back end. That fixed some errors with not being able to read the backup files, but the random errors still occur

Unfortunately that will not fix a bad table. Need to

create new tables (do not copy) and transfer the contents from the old tables.

 

[Marshall Brooks]

The "typical" process:
- Database is in an inconsistent state - or some other error - three duplicate records, even though there are checks to prevent creating duplicate fields names and the records can't be deleted.
- Kick everyone out of database.
- C&R back end.
- Database LOOKS and works okay, but records (may) have been deleted, so check the Excel files or search for ###.

 

[Marshall Brooks]

@MajP - I agree, I'd prefer to prevent the errors, but I'm not seeing simple solutions - i.e. the audit trail suggestion is probably a good one, but now if the database crashes, I have to repair it and then figure out who was doing what right before it crashed and hope that that tells me something useful.

create new tables (do not copy) and transfer the contents from the old tables.

What is the easiest way to do that and not be copying the old or invalid data - is it just CTRL-A and CTRL-C and CTRL-V ?

 

[Pat Hartman]

but they are SUPPOSED to open their own copy from their own mapped network location.

Users should never share the FE. You can add code to the FE when it opens to check the path. If it is not valid, then display a message and close the application.

To help them to do the right thing, create a desktop shortcut for them that points to the correct folder.

Unfortunately, 4 of them have to open the front end from a network drive (mapped locally)

This sounds suspicious. Are you using a VPN to allow the users to open the database remotely?

I use a batch file that is run from a shortcut. The batch file copies the master copy of the FE to the user's local drive. The local drive has a consistent name so that makes the check easy. You can do a similar thing but it would be more complicated since the local drive name would need to include the login ID of the user so the same code would work regardless whether the drive was C: or X:

PS, I also don't believe this is corruption. Just to be safe, You can recreate the BE. Export all the data to csv files. Then recreate the FE with indexes and RI in place. Then you can use append queries to append the .csv files to the recreated tables. Since you are probably using autonumbers, you need to ensure that parent tables get imported prior to child tables so you don't break the relationships. Using this method allows the original autonumbers to be retained. If you were to use a make table method, for example, you would not be able to change the PK later to be an autonumber.

Make sure that no user is opening the BE directly or using the table directly from the FE.
Using the batch file to open the FE has the advantage of copying a fresh copy of the FE which eliminates bloat issues and makes it easy for you to distribute updates to the FEl

 

[Marshall Brooks]

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

 

[ebs17]

Database is in an inconsistent state

There are reasons for that.
You have not yet answered the question of which database management system is used.
Is there external access to the backend such as backups, antivirus program, system work?
Is the network stable? Access is sensitive to interruptions.

 

[Pat Hartman]

Do not rely on the users to do the right thing. Help them out.
This is the Citrix batch file:

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

This is the LAN batch file for the same application:

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

You may need the help of the IT folks with Citrix. They can add code to the login script that automatically copies the FE to the users private folder and then opens it from there.

If you can't work this out with IT, then you MUST add code to the FE that prevents it from opening if it is opened from the master directory on the server.

 

[Marshall Brooks]

Sorry - wasn't sure what you meant by database management system and it got lost in the other replies. It's an .accdb Access backend with an .accde Access front end.

Not sure what you mean on external access? We run a nightly backup and C&R on the backend when there is exclusive access to the backend. Citrix and the local computers have anti-virus running. System work?

Network is not totally stable, but that is pretty much beyond my control.

I can't do a whole lot with batch files, but as long as they run it from U:\ (anywhere on U:\), it should be okay (U:\ is a different directory for each user).

I can remove it from the master directory (not the main master directory, but I don't publicize that) after a few days - which I have just done.

 

[Marshall Brooks]

Figured out my original question from this reply and the thread I linked to earlier: https://www.access-programmers.co.u...all-tables-in-a-database.234890/#post-1198193

I changed the code to fix one typo and to avoid a loop, so it only finds the first instance of ### and then you have to fix the error and run it again until it says ### not found.

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
        MsgBox "### Not Found"
    End If
End Function
Public Function SearchTable(Tablename As String, SearchString As String, Found)
    Dim rs As dao.Recordset
    Dim i As Integer
    i = 0
    Set rs = CurrentDb.OpenRecordset(Tablename, dbOpenDynaset, dbOpenDynamic)
    If Not rs.EOF Then
        Do Until rs.EOF
            For i = 0 To rs.Fields.Count - 1
                If InStr(1, rs.Fields(i).value, SearchString) > 0 Then
                    MsgBox "Search Found in " & Tablename & " field: " & rs.Fields(i).Name
                    SearchTable = Tablename
                    Found = True
                    Exit Function
               End If
            Next
            rs.MoveNext
        Loop
    End If
End Function

@MajP - Still need an answer to the question in Reply #13.

Thanks all!

 

[ebs17]

New tables (table definitions) are created
- by hand
- via DDL (definition statements SQL)
- via DAO
- via ADOX (definition instructions ADODB)

There are existing solutions that take such definitions from the existing tables and which can then be used to create a new one.
Attached is one (I've never really used it) by Nouba from another forum that has since been shut down.

If you use such solutions, you should check the result for completeness. Fields and field definitions are usually well analyzed, with indexes and validity rules (if you have such) you should take a closer look and, if necessary, reprogram or add what is missing by hand.

As an addition, here is a collection of functions for changing the structure of (Access) databases (texts in German):

vb@rchiv · Workshops · Strukturänderungen an der Datenbank

Kunden arbeiten mit Ihrer Anwendung und Sie haben ein Update, bei welchem die Strukturen der Tabellen innerhalb des Backends geändert werden müssen. Was nun? Eine Lösung muss her, aber wie sieht diese aus? Das erfahren Sie in unserem Workshop. Dieser Wordshop wurde in Zusammenarbeit mit: Günther...

www.vbarchiv.net