Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

I downloaded the file - not really comfortable with it.

• It's not 64-bit compatible. I changed "Declare function" to "Declare PtrSafe function" throughout. Not sure that is correct, but it got the main form to open.
• Text on the main form is written in German, so I'm not sure what each option does or how to use it.

It's not a large database, but ... it's 20 tables, in some cases, 50-70 fields per table, a few thousand records per table.

I would introduce more errors re-typing all of that by hand than I would ever solve.

 

[ebs17]

What is the easiest way to do that

You take the word EASY very literally.
But remember, others value their own time, too.

Now that you know what you might be looking for, you could do that (search, research). But it costs your own time and/or your own work.

 

[Marshall Brooks]

Fair enough, but re-building the back end to a blank database was fairly easy.

Typing several thousand records by hand is not easy and my company won't pay me to do so.

They'd be happier with me finding a way to automate crash recovery and fixing it every few weeks - which was my initial intent and what I accomplished.

(Not saying I don't want to ultimately fix it).

 

[ebs17]

Typing several thousand records by hand is not easy

Nobody talked about that. There are append queries for data.

finding a way to automate crash recovery

You would use a stable DBMS and create regular backups.

An active DBMS like the SQL Server is a service and not a "dumb" and defenseless file like an ACCDB. There are much better ways to implement what your clients want.

 

[Marshall Brooks]

Points taken. We tried migrating the back end to SQL server, but things like searching the database for a record from the front end was extremely slow - but I didn't really understand what I was doing. I think I needed a pass-thru query for searching, but again, I didn't really understand how to do that either.

 

[MajP]

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

Great resource. Thanks

 

[ebs17]

but things like searching the database for a record from the front end was extremely slow

SQL Server uses a different (and significantly more powerful) SQL dialect than Access. This should be used extensively.
Besides that, FAST searching is a small art (query design, index usage). This involves a little more than clicking around in the QBE.

 

[Pat Hartman]

but things like searching the database for a record from the front end was extremely slow - but I didn't really understand what I was doing. I think I needed a pass-thru query for searching

Straight conversion of an Access application to SQL Server with no changes at all, can certainly result in slower performance than the original application. One of the most likely culprits would be binding forms to tables or to queries with no selection criteria and than using Access form filters to filter the data. This completely circumvents one of the most important gains of using a remote RDBMS and that is - letting the server do the heavy lifting. You always want your queries to select the smallest set of data that is rational. For a bound form, that would be ONE main form record. Therefore, your selection criteria is supplied BEFORE the form is loaded and therefore only the selected record is ever returned from the server.

Did you use my suggestion to rebuild the BE and reload the tables with the original autonumbers?
Have you looked into the use of batch files to open the FE?
Have you modified the existing FE to refuse to open if the FE is located in the master folder rather than a user folder?

 

[Marshall Brooks]

@Pat Hartman

I missed some of your earlier comments. To give more information:

We really don't use Access as a relational database. It's more of a glorified Excel file with a GUI and LOTS of background VBA.

As you mentioned, our TYPICAL usage is binding a form to a single Access table with Sort filters and no real queries. We then use the Find button or the search bar at the bottom to find a record. With Access as the front-end and the back-end, we can search almost instantly. With SQL Server as the back end, search would take 5-10 seconds to find the first match, 5-10 seconds to find the next match, etc.

From your description - it could work, but it would require retraining the entire team and they have a hard enough time (as you can tell) just using Access.

I didn't initially see your suggestion on rebuilding the backend with .csv files and autonumbers. I did recently re-import the back end into a blank database, but I'm gathering that isn't a great solution.

I've heard of the batch file method - would have to get IT to approve putting the batch file on the Citrix desktop. We do use a modified version of Bob Larson's front end tool: https://btabdevelopment.com/free-access-tools/. So when we release a new front end version, it downloads the new version from the server. I found this more efficient (if the front end doesn't change often) than having to wait for a batch file to download the front end (which likely didn't change from the installed version.

I haven't modified the front end to refuse to open. Too many possibilities - i.e. There are really two prohibited locations: There is a MASTER location that the front end checks for and downloads from. I don't publicize where that location is. Sometimes some users have said the automated download didn't work, so I'm put the file in a temporary master location for them to access. I haven't blocked the file from opening from that location, but I have started removing the file once everyone should have updated it. (And I'll probably go to NOT putting the file there and telling users to see me if it won't download.) More info:

• There are too many possibilities - i.e. the users could put the front end on a network location and both open it from there. (We only have 8 users, so not a HUGE likelihood).
• Ideally, I could restrict the front end to ONLY work from the desktop or the root folder of the U:\ Drive - which would be unique for each user, but I don't want to be that restrictive unless I absolutely have to.

 

[Marshall Brooks]

Thought it through some more. Next release of the front end will ONLY open from the desktop or the U: root folder (or my development directory, but if anyone else can get to that, we have major problems).

 

[Pat Hartman]

I found this more efficient (if the front end doesn't change often) than having to wait for a batch file to download the front end (which likely didn't change from the installed version.

Downloading the FE shouldn't take long enough for anyone to notice. The FE is typically very small and always having a "clean" copy can eliminate certain types of corruption and bloat.

I haven't modified the front end to refuse to open. Too many possibilities

How can ONE source for the master copy be too many possibilities? That is what you need to check for to stop the users from opening the the master copy. An additional check which you should consider is to have a version table in the BE and a version table in the FE. When you modify either, you should change the version number. Then, when the FE opens, in addition to ensuring that it isn't the master copy being opened, compare the version numbers in the two tables to ensure they are the same. That goes a long way toward preventing them from accidentally using an old copy that they have hanging around.

You are using lots of poor practices which gives your users lots of opportunities to mess up and rather than searching for potential problems in the code, you are making excuses and doing fixups after the fact. ACE is NOT UNSTABLE. The BE does not just corrupt.

You can use the load file method on Citrix if you don't like the batch file. The batch file is pretty simple and easier for most developers to understand. Combining it with the version checking and the directory check makes for a pretty solid solution.

 

[Marshall Brooks]

Too many possibilities in that our users could put the front end in any folder and open it, and it is too hard to specify "This folder is okay, but this folder someone else could also be using." I re-thought that, the next release of the front-end will only open if the file is on the desktop folder, or U:\ (or one specific folder on my local computer that others can't log into). No longer a possibility (that I can foresee) of two users opening the same copy of the front end.

Lots to go over ...

Front end is approximately 25 Mb. I haven't figured out exactly why, but the front end takes about 10-15 seconds to open locally over an Ethernet connection. From home, it takes 1-2 minutes to open with the file locally on the computer, using VPN to connect to the backend. Any file transfer over VPN is slow for us, and I'm not sure why, but it's something I have to take into consideration. If the front end gets corrupted and needs a clean copy re-loaded, I'm not concerned about that and can facilitate that.

I do use version tracking on the front end and it has made a huge difference in preventing users from opening and using an old version.

We don't use version tracking on the back end. I assume you mean structure changes as far as changing the version number. If we had to roll the version number each time a record was changed ???

I'm not immediately seeing how a version number for the back end would help. There is only one back end and the front end is linked to it. I'm not sure how the front end could open an older version.

 

[The_Doc_Man]

Regarding back-end versioning...

I used a scheme where I had a "build number" that was the "actual" version, never mind what the text version said. When I made FE changes that were text mods or things that were GUI sequencing mods, that was an FE-only change, which was far more common than BE changes. My version compatibility table residing in the BE would be queried for two different build numbers. There was the "last mandatory build" and the "most recent build" - and they often were different. I flagged the BE table to show that a particular version of the FE was mandatory - i.e. older versions of the FE were not allowed, because the FE changes of a "mandatory" version corresponded to BE changes. But when the running FE build was between "last mandatory" and "most recent" it was OK to continue.

Before anyone asks, our IT guy refused to allow me to use the auto-updater scripts, which I would have LOVED to use - but U.S. Navy IT guys can sometimes be a bit hard-headed. He was good at a lot of things - but a bit rigid on a few niceties.

I actually kept a build version for the BE file only for one reason: I kept a history log of changes because... (wait for it) ... the Navy IT guy required a version history. The BE really didn't need it but the Navy did. But having the FE know which of its versions would work with the current BE - that was important.

 

[Pat Hartman]

Too many possibilities in that our users could put the front end in any folder and open it

It doesn't matter where they went out of their way to put it. All that matters, is that they CANNOT use the master copy of the FE. The version check prevents them from using an old copy of the FE. Otherwise, if they can't follow directions, you can't help them. This is like laying down rules for your teenagers. Pick the rules you can enforce and hope their common sense and your guidance keeps them safe. Don't ever lay down rules you don't need to or are not prepared to enforce. You only need to enforce the rule that they cannot open the master FE. Then, to keep them safe from themselves, you make sure they are using the version that the BE says they should be using. Beyond that, you can't help them. But if you did want to force them to use a specific folder, you only have to ensure that the folder name includes their user name. If they go behind your back and conspire to get around the rules they can make a folder on the server that has everybody's username in it and that would let them get by your rules. Would they even think to do that? I doubt it and why would they anyway?

If we had to roll the version number each time a record was changed ???

Why would you ever think that? You really need to hang up your spreadsheet hat and find a relational database hat. "Version" refers to structural or code changes. How do you do the FE version checking now? Do you keep a table in the BE and match its contents to something in the BE because that is all I suggested. The BE says version 128 and the FE says version 128 - OK, they will work together. Allow the FE to open.

You can get a little more complicated and keep a separate BE version. That will allow you to have multiple FE versions that work with the same BE but it won't ensure that your users are using the most recent version of the FE which is far more important.

 

[Marshall Brooks]

@Pat Hartman - We're gradually reaching the same conclusions ...

All that matters, is that they CANNOT use the master copy of the FE.

Negative - all that matters is that multiple users can't open the SAME copy of the FE. Previously they could have moved the master copy from the network share to any other network share and open it and had others open it.

Now, it has to be on their desktop or in their unique mapped directory. That's a bit Draconian, but it's simpler to give them something common and enforceable. "Just put it here and it will work - you don't need to know WHY."

But if you did want to force them to use a specific folder, you only have to ensure that the folder name includes their user name. If they go behind your back and conspire to get around the rules they can make a folder on the server that has everybody's username in it and that would let them get by your rules.

True, they could do that. (Actually, I check for the username on the desktop. For Citrix, I check for the folder mapped to U:\ - which includes the username.) (Technically, if they wanted to, they could map the master file location to U:\ and open the same version of the front end from multiple PC's, but as you said - would they think to do so and why would they bother?)

Do you keep a table in the BE and match its contents to something in the BE because that is all I suggested. The BE says version 128 and the FE says version 128 - OK, they will work together.

Yes, that is how it works. There is a table in the front end with a date field that I update just before I release the new version. There is a table in the back end with a date field that I open from a different front end when I want the database to go live. After that, if the date in the front end table is older than the date in the back end table, it prompts you to update.
There is also a field on the main form with the version date, which is more for my reference and for development so that I know what version I am working with.

In hindsight, I should have gone with version numbers instead of dates b/c there are occasional times that I want to issue two updates in the same day. 20-20 hind sight ...

 

[Marshall Brooks]

Okay - I'm somewhat convinced I need to do something.

I issued the new front end this morning, so no (easy) chance of two people being in the same front end at the same time.

Today, we got an error about unrecognized database format on the back end file.

Had everyone get out of the back-end and did a C&R and the database opened fine.

Did my Excel comparison between last night's Excel exports and the newly repaired back end.

• 19 of the 20 tables were fine.
• One of the tables that had about 300 records originally had 3 classes of errors.
  • Almost 200 records were added with either every field blank or every field contained "###" - random number of tags. I was able to delete these records.
  • Two records were brought over from a completely different table. The records were still in the other table also. I was able to delete these records.
  • Four records were missing/overwritten or otherwise deleted. I was able to copy these from a backup. However, when I pasted the new files from the backup table to the current table, it gave them Primary Keys of 9,0310,397, etc. (I'm not sure Access supports 9 million records, but there are only maybe 5,000 total records in the database). I'm not sure if this is an issue or if it can be fixed, it doesn't seem to be affecting anything.

Could someone walk me through the best way to repair the back end. I'll need complete steps, not "Export all the data to .csv files and use append queries to add the data back in." (I'm not picking on Pat Hartman - I'm just saying I don't know how to do that.)

Also the database is used daily, so there isn't really a way to ask for exclusive access to the database for 4 days while I repair things. (I can do that if it comes to it, but I'd prefer not to do so).

 

[Pat Hartman]

Negative - all that matters is that multiple users can't open the SAME copy of the FE.

And I told you how to do that but you didn't want to follow my directions. IT becomes compliant when you explain the danger of not following the best practice. It is very unlikely that they would give you any grief about forcing the users to have a separate copy of the FE once they understood the problem.

As far as the users doing stupid stuff, you might want to remind them that you are not being arbitrary. The rules are to protect the data. If they violate the rules, they run the risk of damaging the company's data and that would be a termination offence if they did it deliberately - which is what deliberately ignoring your rules and safeguards would be. Just put your foot down. If you don't have the juice to put your foot down, get your boss to do it. If he won't do it, send him a letter with a copy to HR for your file regarding your position. It might get you fired but protecting company data is too important to be lax about. The alternative is YOU taking the rap if anything bad happens to the data. Your boss sure isn't going to take the bullet.

Recreating the BE is not likely to fix the problem permanently since the problem is more than likely being created by bad code and bad queries or good code and queries running at the wrong time with the wrong arguments.

The simplest way to export data to a csv is to use TransferText. You need one for each table or you need a loop. Here's a sample that is as simple as I can make it. You can turn it into a loop or you can go the hard way and include 20 lines of code. I would use a loop but there are too many unknowns for me to make something that will work for you. So, Down and dirty works. The first function goes into the current BE. The second one goes into the New BE. Between the first and the second functions, you have to manually rebuild the tables. One simple way to do that is to open the tables in design view and copy the design that way. Once the tables are recreated, make sure that the indexes are set up and add the relationships.

For this function, you use the exact table names. I hope they don't contain spaces or special characters.

Option Compare Database
Option Explicit

Public Function ExportData()
Dim strPath As String
Dim strFullName As String

strPath = "C:\SaveExportedData\"
DoCmd.TransferText acExportDelim, , "Table1", strPath & "Table1" & ".csv", True
DoCmd.TransferText acExportDelim, , "Table2", strPath & "Table2" & ".csv", True
DoCmd.TransferText acExportDelim, , "Table3", strPath & "Table3" & ".csv", True
'...
End Function

For this function, you can't link the .csv files with the same name as the table they will append to because that would cause a conflict so I just prefixed the linked table name with "Link"

REMEMBER, you MUST import the tables in a logical order if there are any relationships in play. If you don't have any relationships defined (why not???) then use alpha order for consistency.

Option Compare Database
Option Explicit

Public Function ImportData()
Dim strPath As String
Dim strFullName As String
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDb()

strPath = "C:\SaveExportedData\"
DoCmd.TransferText acLinkDelim, , "LinkTable1", strPath & "Table1" & ".csv", True
DoCmd.TransferText acLinkDelim, , "LinkTable2", strPath & "Table2" & ".csv", True
DoCmd.TransferText acLinkDelim, , "LinkTable3", strPath & "Table3" & ".csv", True
'...

strSQL = "INSERT INTO Table1  SELECT LinkTable1.*  FROM LinkTable1;"
db.Execute strSQL

strSQL = "INSERT INTO Table2  SELECT LinkTable2.*  FROM LinkTable2;"
db.Execute strSQL

strSQL = "INSERT INTO Table3  SELECT LinkTable3.*  FROM LinkTable3;"
db.Execute strSQL

'...
End Function

 

[The_Doc_Man]

I am convinced that from your description, you are writing these records as though you were overlaying records in an Excel worksheet. That is easy to do because Excel doesn't have nearly anything like the level of data checking that Access does. In its purest form, Excel doesn't have ANY data checking other than that you can't exceed its string or number limits and doesn't even have an inherent data type unless you impose a format on a column or range.

What you need to do is find some fields where you can impose useful constraints. For example, numeric fields that you know must be >0 - or even numeric fields where you test whether IsNumeric is true. That will stop - and maybe catch - the thing that is giving you the overlaid data. If you get an error message regarding a constraint violation, whatever was happening at the time is your culprit for the "#" case and for the "records from a completely different table" case. For the four records that you mentioned in the 3rd bullet, I don't know what to tell you if they are just "missing" since a legit delete might do that, too. You described this symptom:

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.

If the SSN is formatted with dashes, it might be trickier to decode, but if it is raw numbers then a test for numbers might tell you something regarding the input data and stop things when you get input that isn't numbers. The fact that you COULD overlay records this way tells me that you are not using Access data typing to help you protect your data.

Until you catch what is actually happening here, you are going to be plagued by this long-term. This is a pay-me-now or pay-me-from-now-on situation. There IS no pay-me-later because this is ongoing corruption. The first issue will be to "catch the rabbit" because you ain't makin' Hasenpfeffer until you do.

 

[Marshall Brooks]

Okay - at least I have a path forward ...

Question: Will this method fix the records with a Primary Key of 9-million something, or will it copy that over, and should I be concerned about that, or should I not worry about it?

I thought about it and I'm really not concerned if the Primary Keys change. It will make my database verification (Excel File Comparison) useless for one cycle, but at long as it doesn't crash before I run one cycle on it ...

I'm thinking the way to do this with the minimal downtime would be:

Manually create the two functions above in the new back end.
Work from a copy/backup of the back end and re-create the table structure in the new back end.
Probably I will work from the previous backup of the back-end and verify everything works - the new back-end would not have today's changes, so I'd want to re-do it on the current file, but at least everyone could keep working while I did my testing. (And I'd have an idea of how long it would take so that I would know how long I needed users to stay out of it).

Get exclusive access to the back end and move the new function there, run the functions, put the new back end in place, and run my Excel exports so I have a baseline.
Let other users back into the database.

To answer some questions and clarify some issues:

And I told you how to do that but you didn't want to follow my directions. IT becomes compliant when you explain the danger of not following the best practice. It is very unlikely that they would give you any grief about forcing the users to have a separate copy of the FE once they understood the problem.

It wasn't an issue of not wanting to follow directions - it was more a matter of deciding it was the right thing to do and how to do it. I used a MORE restrictive approach than you suggested, there are only three folders that the front end will open from (unique to each user, though) and one of them is only on my computer. But I also had to figure out a way to display a message about what folder it needed to be in so that users didn't just complain that it never opened.

It still crashed AFTER I did the issue, so that means that it isn't being caused by multiple users opening the same front end - although that might have previously introduced the corruption that is now going to be repaired.

Recreating the BE is not likely to fix the problem permanently since the problem is more than likely being created by bad code and bad queries or good code and queries running at the wrong time with the wrong arguments.

Okay - somewhat disappointing, but that will be the next issue to face.

For this function, you use the exact table names. I hope they don't contain spaces or special characters. REMEMBER, you MUST import the tables in a logical order if there are any relationships in play. If you don't have any relationships defined (why not???)

Also @The_Doc_Man - No spaces or special characters, but that was more luck than design. No relationships. More on structure:

This is a database that has been in use since at least 2000. My previous supervisor was bored and decided to learn a bit about Access and build a database for us. It was originally just supposed to create a report automatically and it never did that. I inherited it and with some help from others and a lot of things cobbled together from Google and this forum, it has a lot of useful VBA code. Otherwise (and even with most of the VBA, it's basically Excel with a GUI and a bit more friendly to multiple users at the same time.

There are 8 primary tables and 8 forms that the users see to edit the data in these tables. The remaining tables contain data that are called by the various VBA routines.

There are something like 48 queries, but the queries are basically only used to export the data to various Excel report and the most reliable code I found to export to Excel used queries rather than tables with filters.

There is a LOT of VBA code and the VBA code interacts with Excel, Outlook, Adobe/Kofax PDF, etc. (It used to interact with Word, but we finally phased that out of it.)

If you get an error message regarding a constraint violation, whatever was happening at the time is your culprit for the "#" case and for the "records from a completely different table" case. For the four records that you mentioned in the 3rd bullet, I don't know what to tell you if they are just "missing" since a legit delete might do that, too.

Puzzled here - as I said, it wouldn't necessarily be a constraint violation as I'm seen times that the corrupt records duplicated a primary key. AFAIK, Access isn't supposed to allow you to do that. And the tables have a lot of fields, but each field DOES have a defined type (maybe it has to), but I'm meaning I have date fields and short text and long text fields, etc.

Missing was perhaps an inaccurate term. The could have been overwritten by the records copied from the other table, or they could have been some of the records that were overwritten with "#". I can say with some certainty that it was NOT a legit delete for two reasons. I think I'm the only ones that knows how to delete records from the database. I haven't really publicized that and most of our users aren't likely to research it. The four records that were deleted were all records that I edited earlier in the day yesterday. I didn't intentionally delete them and the odds of another user just happening to delete them is highly remote. I noticed in the past that typically the records that were deleted (or overwritten) were probably records that were open, but I guess that makes sense also.

If the SSN is formatted with dashes, it might be trickier to decode, but if it is raw numbers then a test for numbers might tell you something regarding the input data and stop things when you get input that isn't numbers.

Unfortunately, the SSN example was a bit of subterfuge, there are no SSN numbers in the database. But what I meant was I could tell that somehow it copied the data from Field 4 of Table A into Field 4 of Table B (probably in some cases in violation of constraints - i.e. it might have put text in a date field, etc.)

The first issue will be to "catch the rabbit" because you ain't makin' Hasenpfeffer until you do.

Further tips on how to do that? Would the audit trail mentioned early on in this thread be helpful. (I've never done one, but it seems like possibly a good idea).

***
I'll work on the back end reconversion and let you know how it works out or if I have more issues/questions.

Thanks all!

 

[Pat Hartman]

What you should be doing is adding validation code to each form's BeforeUpdate event to ensure that bad records don't get saved. You also need primary keys, unique indexes and relationships. You need to lock down the FE so the users cannot get to the queries and tables directly. Then distribute an .accde so the users can't get to the code. Be very conscientious about your backups. It will be a royal pain to recover from losing your master .accdb and it won't be free. ALL updating should be controlled via the forms. Either your own code is causing the issue or the users are mucking around doing stuff the app doesn't support. Using "shortcuts" that they developed themselves.

NEVER work on the production BE while any user might have it open. Obviously, you need to create the procedures I suggested and test them. THEN. Everybody out of the pool and you can run them to build the BE. If you can't shut the users out during the day, then YOU work nights and weekends so you can do this in isolation. I told you how I would do it. Make whatever changes you want if you think you know better.

Rebuilding every time someone breaks something is the wrong approach. Access is NOT corrupting your data. Do NOT blame this on Access.

The method I described is intended to transfer the data as text which avoids all corruption, if any exists. It is not something you would do weekly and it won't fix the bad data you are working so hard on fixing after the fact. I would spend my time preventing user error and ferreting out code errors.