Solved Automated search for Database Corruption Assistance

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

 

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

 

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

 

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

 

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

 

[The_Doc_Man]

Pat calls it "validation" code and she's right. I refer to "constraints" (as in per-field data validation rules found on the table design pages) - but that's just one way to validate. Pat is one of our expert members and knows exactly what she is talking about when it comes to putting together apps. The subject she is discussing in post #40 falls under the general topic of "securing a database" and it is absolutely good advice. You can search the forum for more on that topic.

When your people can see the inner workings of your app, they don't even have to be malicious to cause your app to fall flat on its face. They just have to be two perfectly common things... curious and clumsy. That's all it takes. In a well-designed app, NOBODY but the designer ever sees what is behind the curtain.

 

[Marshall Brooks]

@Pat Hartman ...

Believe it or not, most of your suggestions, I'm already doing. More comments/questions:

What you should be doing is adding validation code to each form's BeforeUpdate event to ensure that bad records don't get saved.

There is a confirmation prompt in the before update event for the user to confirm that they mean to save the record. There isn't really "validation" code, but I thought the forms basically did that - i.e. if I have a date field on the form, it won't let me enter text in the field (although I think the corrupt records sometimes have that.
I have primary keys. Don't have unique indexes, but I think that is what a PK does, and don't have relationships, but so far haven't needed them.

You need to lock down the FE so the users cannot get to the queries and tables directly.

I don't know how to do this, although it sounds like a good idea. Some of our users update the records from the from in datasheet view rather than form view - I assume this is okay?

The front end is distributed as an .accde.

Be very conscientious about your backups.

We're pretty good about that - wasn't always that way. The previous version of each front-end revision is saved. The backend if C&R'd and backed up nightly on work nights.

One thing I forgot to mention, but it seemed odd. Yesterday, after the unrecognized format error, I was restoring records from the backup and Monday Nights backup said the database was not recognizable, even though I had been working in it all morning. So I restored them from Thursday's backup which opened fine.

Also - when restoring records, I open the backup of the backend, find the missing record in the table and then copy it to the linked table in the current backend using the front end. Is that okay, or do I need to be working in the back end exclusively? (I'm asking b/c you said not to edit the tables directly, but that seems the most efficient way to add missing records back to the database.)

If you can't shut the users out during the day,

I didn't say I can't do that. What I said is I can't say "Don't use the database for several days while I play around and hopefully get stuff fixed." I have code that should kick users out of the database (after a delay) and code that keeps them from opening it until I am finished.

What I don't have and am not sure how to do, is how to make a back-door so that only I can get in to work on it. i.e. I can lock them out, but that for me to test out the changes, I have to remove the locking file so that I can open it, but then they can as well. (I think maybe that is usually done with a "Admin Front End" that avoids the lockout code - but I haven't thought that through.)

 

[Marshall Brooks]

(I think maybe that is usually done with a "Admin Front End" that avoids the lockout code - but I haven't thought that through.)

Disregard - I'm going to change the front end so that the lockout code is bypassed if the front end is loaded from my desktop - which means it will still lock everyone else out.

 

[Marshall Brooks]

I searched a bit and I'm not seeing a Simple way to prevent access to the tables and queries directly and still allow access to the navigation pane.

i.e. we have 7 main forms that users typically switch between on the navigation pane. There are 14 more forms that are hidden by default. I personally have "Show Hidden Forms" enabled, but I don't think any other users know how to do that (and it would just confuse them if they did). Also, they could select tables or queries from the drop-down on the navigation pane, but I don't think they know how or have any reason to do that.

From a search, the only way that I've seen to keep users from access tables and queries would be to hide the navigation pane, disable F11 to show it, and disable the shift key to bypass the code that hides it and show it that way.

That would be workable, but then I would need to make something like a pseudo-navigation pane - i.e. instead of having Form1 open from startup and the navigation pane open to select Form2, Form3, Form4, Form5, etc, I would have to have frmMain open from startup and frmMain would have buttons to show Form1, Form2, etc.

That seems a bit extreme to me, but ... Is there a way to do this so that it WORKS like the navigation pane - i.e frmMain would show in the left 1/5 or so of the Access window, clicking the Form 1 button would open Form1 to the right of it?

 

[isladogs]

You can block access to forms and reports using code.
However, there isn't a way of preventing access to tables and queries from the nav pane. The best you can do is hide them.

The solution is simple. Hide the navigation pane completely and ensure users can open whichever objects they need via your forms. Its the standard solution. Not at all extreme.

 

[Marshall Brooks]

Hiding them makes sense and has the advantage that I will still be able to see them. (And it probably has the advantage that for our users, if someone says "I can't find this table anymore", then I know they were using the tables and can just tell them they need to use the forms.)

I didn't mean extreme as in overly difficult to do, I meant it in terms of:

• As said in Reply 44 - I don't know of a way to do this that would look and act like the navigation pane. I can figure out how to make a form with 7 buttons on it that opens the other forms and then they close the other forms to get back to the main (switchboard) form.
• It's a change for our users and most of them are not computer savvy and don't like changes.
• I'm doing all this to prevent them from accessing tables and queries that probably 80% of them don't know are there or what they were or how to get to them (but I know it's the 20% that I need to worry about and it only takes one).

 

[isladogs]

Hiding them makes sense and has the advantage that I will still be able to see them. (And it probably has the advantage that for our users, if someone says "I can't find this table anymore", then I know they were using the tables and can just tell them they need to use the forms.)

I didn't mean extreme as in overly difficult to do, I meant it in terms of:

• As said in Reply 44 - I don't know of a way to do this that would look and act like the navigation pane. I can figure out how to make a form with 7 buttons on it that opens the other forms and then they close the other forms to get back to the main (switchboard) form.
• It's a change for our users and most of them are not computer savvy and don't like changes.
• I'm doing all this to prevent them from accessing tables and queries that probably 80% of them don't know are there or what they were or how to get to them (but I know it's the 20% that I need to worry about and it only takes one).

Its always the small minority who cause the most trouble ... usually those who think they know what they are doing
Its very simple to create a navigation style menu
You could use the built in navigation form though I prefer to roll my own.
There are many examples of this on my website.

If you really want to go 'the whole hog' you can also hide the entire application interface. See

Control Application Interface

This article discusses how the Access application interface can be controlled including hiding the navigation pane, ribbon or the entire application interface. Some methods can help make a database more secure. The example app opens with a form 'floating independently' on the desktop. Various...

www.isladogs.co.uk