Solved Automated search for Database Corruption Assistance

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

 

[Pat Hartman]

There is a confirmation prompt in the before update event for the user to confirm that they mean to save the record.

That isn't validation. All that type of prompt does is train your users to ignore your prompts. If every time they save a record, you prompt - did you really mean that? You know what, they stop reading your prompts and always press yes. Your prompts need to be few and important. If they are frequent and not important, you are training them to ignore you.

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

If a field is defined as a date, you can only enter a valid date. Is 7/26/203 a valid date? Probably not to the application. It is a very common typo though. Access has no clue that it doesn't make sense in context, so Access lets it go without a warning. If you are entering a date of birth, would 12/31/2300 be valid? No, probably not. Are you getting the message? For dates, at least, there is almost always a rational range so you should validate the entry that Access thinks is a valid date as a sanity check. Same for numbers. There is usually a logical range for numeric values. Text is more of a problem. But, some things can be controlled by using combos.

When a value is absolutely invalid, you display a descriptive error message, prevent the record from being saved (cancel = true), and exit the BeforeUpdate event.

If you want to prevent the users from accidentally updating a record, it is better to include a lock/unlock toggle button so that records always display as locked and in order to change anything, the user must first press the unlock button which then toggles to lock. You also have to set to Lock in the Current event and in the form's AfterUpdate event. Once the user presses the unlock button, you can and should assume that he intends to update the record so you would never prompt him. You just validate whatever you can to ensure that data is present and within valid ranges if any can be defined.

The other alternative to control if a user can update a record is to only allow changes to be saved if the user presses your Save button. This is more delicate code and needs checking in several events as well as a global variable. I can describe if you need me to.

 

[Pat Hartman]

Some of our users update the records from the from in datasheet view rather than form view - I assume this is okay?

This is very likely how your data is becoming corrupted. I do not allow the user to switch to DS view. But, if you had proper validation code, you would be trapping inserts and deletes also to minimize the risk.

 

[Pat Hartman]

"Don't use the database for several days while I play around and hopefully get stuff fixed."

Nobody said to do that. The rebuild procedures I described should take less than an hour to build and test on TEST databases. THEN you kick everyone out and do it for real.

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

"Edit the tables" means changing their definition. Changing data is not the same as changing the definition of an object. Access is quite capable of having multiple users working on changing "data" simultaneously.

Here's several sample switchboards including forms that replace the switchboard wizard for adding items to the switchboard.

Replacement Switchboard

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons...

www.access-programmers.co.uk

If you look in the database samples forum, there is a version of a couple of these switchboards that includes login in code if you want to go that far.

I rarely allow multiple forms to be open at one time. It just confuses the user. This sample shows a simple way to only have one form open at a time.

Chaining Forms

Many people open multiple forms at one time. This is especially prevalent when you are using the tab view which is the default. I find that to be confusing for users so I developed a simple technique that uses the OpenArgs to allow one form to open another in DIALOG mode and then hide itself...

www.access-programmers.co.uk

The switchboard sample does this also so just download the switchboard if you are going to look at that.

Chaining Forms

Many people open multiple forms at one time. This is especially prevalent when you are using the tab view which is the default. I find that to be confusing for users so I developed a simple technique that uses the OpenArgs to allow one form to open another in DIALOG mode and then hide itself...

www.access-programmers.co.uk

 

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

www.isladogs.co.uk

 

[Gasman]

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?

I got by with a standard switchboard form. Pat Hartman has an enhanced version which she has posted here several times.
If you can group your forms/reports etc logically it is more than adequate.
For more than the switchboard could offer, I would supply a combo, which would open form/report with possibly a form just to get any parameters. Supplied FE was also an accde.

 

[Marshall Brooks]

How do you prevent users switching to DS view? (Found it - Property Sheet - Allow Datasheet View). (I have one user who will not be happy, but if it prevents the data from being corrupted.)

I have some of what you describe (Reply #47) in place.

They aren't prompted every time. We have confirm and undo buttons that are grayed out. Whenever they make a change, the buttons are enabled. If they try to go to a new record after making a change, they get a "Do you want to save?" prompt. Before we did this, they could type gibberish, overwrite a field, and Access would save the change without them realizing they messed up anything.

Valid point that it isn't checking whether any of the data is ACCURATE, it's just confirming that they did mean to make the change.

For dates, if they click a date field, a datepicker pops up and they enter they typically select the date from that. So dates are actual dates. Now again, they could say that a project is due to complete 100 years ago (although it would take a while to get there in the datepicker.) (Although they COULD click Cancel on the datepicker and then enter 7/3/203 - but that takes extra effort.)

Devil's advocate question: If editing the tables means changing the table structure, and I'm not concerned about them directly changing the table data... And (as I understand), they can't change the structure of the linked tables without exclusive access to the backend, and if they change the structure of a local table that is copied from the backend, it either is erased or crashes the front end when they re-open it ... - Then why am I worried about preventing them from getting to the tables?

I'll look at the switchboard examples - I somewhat like the extra screen real estate with the navigation pane hidden.

Typically, I only allow one form open at a time. I have code in the form load event that closes any other open forms when you select a new form. I also sometimes want to have more than one form open, so I have a macro that allows that.

Its always the small minority who cause the most trouble ... usually those who think they know what they are doing

In this case, the user that I said will be upset if I disable Datasheet view. (We have an inside joke that he broke the database again, but it might not have been that much of a joke ...)

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

I don't want to go "whole hog". Partly as follows: I have macros that I run sometimes: Database Tools-Run Macro. Most of our users don't know that command is there, but I don't want to put them on the switchboard, and I don't want to hide them and then I have to run the .accdb instead of the .accde if I want to use them.

 

[Pat Hartman]

and I'm not concerned about them directly changing the table data...

But you should be. This is very likely where the "corruption" happens with sloppy copy/paste or even worse cut/paste. This is why you always use forms. Only with forms can you prevent some of the more stupid actions users might attempt.

but I don't want to put them on the switchboard,

YOU know how to bypass the code that locks the FE. If you don't tell the users, they are unlikely, unless they know more about Access than you do, to be able to circumvent the locks you set in place.

Also, if you choose to use the versions of the switchboard I linked to that include security, you can have completely different switchboards for yourself and the users. This is what I do.

 

[Marshall Brooks]

Okay - the light somewhat turned on.

I don't think any of the users (except sometimes me) are directly changing the data in the tables. They are using datasheet view sometimes.

The real issue isn't to force them to use forms, but to add some logic to the code on the forms (which I think also works for datasheet view, but I personally almost never use that, so I'm not sure).

The way I have it set up, they can copy and paste gibberish and corrupt the table from the user form or from datasheet view or from the tables, so hiding tables and datasheets from them isn't really going to help anything.

OTOH - I don't think the backend has ever been exported to text and re-imported in over 20 years, so that might help.

 

[Pat Hartman]

OTOH - I don't think the backend has ever been exported to text and re-imported in over 20 years, so that might help.

It is worthwhile doing that ONCE just to satisfy yourself that the objects and data are "clean".

Continuous forms work exactly the same way as single forms. Your validation code still goes into the form's BeforeUpdate event. you still use "Cancel= True" to prevent a bad record from being saved, etc. Where it gets tricky is with cut and paste and copy and paste --- NONE of these should be allowed. If that is the reason they are using the DS view, then YOU need to give them an automated way of copying a row that you can control. Your objective is not to impede the user's ability to do their job. Your objective is to keep the data from being corrupted by sloppy technique. Therefore, YOU make a copy procedure. It is very simple. Add a button, the button runs an append query that copies the selected record and appends it as a new row - preferably by including some change to the data so that the data isn't totally duplicated.

You also need to add code to the BeforeDelete and BeforeDeleteConfirm events. I'm going to post another database. This one is critical for you to download and play with. I don't allow users to do what you are allowing them to do, so I am not exactly sure how the events run in the form when multiple rows are selected so I'm going to give you a tool you can use to educate yourself.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

You might want to watch the videos first so you get a sense of how the database works. At least read the first three menu items before you try to use the app. The whole point of it is to give you a way to examine how form and control events run and when. The test form has a subform in DS view, use that to test your multi-row copy paste action.

 

[Marshall Brooks]

I'll look at Reply #55 later. I think (and hope) I'm the only one that knows how to delete records. We have a copy record button - it doesn't use an append query, it creates a new record and then copies SOME of the fields from the previous record (and prompts to change other fields).

We don't copy multiple records at once. (At least I don't. The user that likes DS view ended up with three exact duplicates of one of his records that I couldn't delete without doing a C&R on the backend, but he probably would have figured out how to do that in form view also.)

Test code is not working.

I only tried one table. Let's say my table is called Names.

I copied the structure for Names from the backup file to a new table named Names in the blank new file.

ExportData worked. I used a different destination directory and I had to create that directory, but I ended up with a Names.csv file in the destination folder.

When I ran ImportData with this code:

DoCmd.TransferText acLinkDelim, , "LinkNames", strPath & "Names" & ".csv", True
strSQL = "INSERT INTO LinkNames  SELECT LinkNames.*  FROM Names;"
db.Execute strSQL

I got an error on line three, something like "Syntax error in SQL Insert statement".

And when I looked at my database, Names was still empty, but I have a new linked table (different icon) named LinkNames and the data is there. (And the PK still has a 9-million number for the 4 records re-added yesterday - not sure if that is a problem.)

And if I run it again, I get a new linked table LinkNames1, LinkNames2, etc.

So ...

 

[Pat Hartman]

look carefully at the way I named things in the sample code. You are not using the same pattern.

I copied the structure for Names from the backup file to a new table named Names in the blank new file.

Did you do this the way I described or did you let Access do it by using the import dialog? I was very specific about how to do certain things so you would be copying a text string rather than an object. If the object is corrupted in the source, it will almost certainly be corrupted in the target. The absolute safest procedure is to retype the structure. I just tried what I suggested and it doesn't copy text, it copies the object so do what you want.

 

[Marshall Brooks]

I might have mistyped my reply:
strSQL = "INSERT INTO LinkNames SELECT LinkNames.* FROM Names;"
should be:
strSQL = "INSERT INTO Names SELECT LinkNames.* FROM LinkNames;"

But that also gives me a syntax error and creates a linked table named LinkNames3.

I'm missing something somewhere (probably something obvious ...)

I didn't use the import function. I created a new table design and then I went into design mode in the old file and copies all of the field rows and pasted them into the new table, set the primary key field as primary key, and saved it with the same name as it had in the old file. Hopefully that was what I was supposed to do.

The new table doesn't have any data, but the linked tables that are being created do have data.

 

[Pat Hartman]

If you run the code multiple times, you need to delete the previous links

 

[Marshall Brooks]

Still not working and still giving me a syntax error on the SQL INSERT INTO line.

I tried stepping through the code and I see where it creates the linkNames table.

Also I tried:
strSQL = "INSERT INTO Names SELECT * FROM LinkNames;"
and that failed also.

I'm not sure where the error is b/c it looks similar to code I have that clears the data from a local table and then copies the data from the same table in the backend to the local table - that code looks like this:

strSQLinsert = "INSERT INTO table1 SELECT * FROM table1 IN '<backend path and file name>';"
CurrentDb.Execute strSQLinsert, dbFailOnError