Automated Checking for Database Corruption

[kjv1611]

Is there any known method of checking a database for at least tale-tale signs of corruption via a VBA script?

Here's the thought I have in mind...

Where I work, particularly in the department where I'm currently working, we have LOADS of Access Databases for different processes, and many of them are VERY old.

I'd like to set up a database for keeping track of them all - for now at least - that would basically loop through, and list all databases, and then possibly throw up any flags about any of them.

I've already put a piece together that will take care of any open database-lock files - (*.ldb and *.laccdb).. basicaly delete the file if possible, if not, move on... if it's possible, then no one is actually using it, and it just remained after a database crash.

Then that got me to thinking, what about database corruption? I realize I couldn't check via a script for whether a database has corruption in it based on a "is it corrupted" of course, but what about checking for common signs of corruption? I don't yet know where to start, but thought that perhaps someone else has been down this road in the past.

Thanks for any thoughts, references, suggestions, opinions.

 

[jedraw]

See

http://allenbrowne.com/ser-25-01.html

http://allenbrowne.com/ser-25.html

http://allenbrowne.com/ser-47.html

I recommend if you can improve any aspect of identifying corrupted db, then let the rest of us know by posting same.

TIA

 

[kjv1611]

Thanks. I think I've already seen some of that, on the same website. I suppose I would need to think through and read through all the possible causes and symptoms of corruption, and come up with ways to test for what is really "testable", and base it on that..

Well, we'll see if I can ever get to that point.

 

[MajP]

You should spend your efforts employing the databases properly, not searching for signs of corruption.

Here is the bottom line. Multi user Access database have to be split, it not really an option if you care about keeping your data. If you do the following you will never get a corrupted database and never lose data. Although that may sound like a bold statement, I have just never seen a properly employed database get corrupted.

1) Split the db
2) Backend on the server
3) Front end is a compiled mde or accde located on each users machine
4) Set the front end to compact on close

I have built hundreds of Access databases and have never seen a non-replicated backend get corrupted. The only time I have seen a backend get corrupted is when it was replicated and synching memo fields. This is a known bug, and a rarely done employment

If anyone has ever seen a properly employed backend database get corrupted, I would be curious to hear about it.
If a front end gets corrupted who cares? Just download another.

If you are not employing your multi user databases as described then this really is an utter waste of time.

 

[kjv1611]

Thanks for the list, MajP.

That's also something I'm working on incorporating into databases as I rebuild those that need rebuilding.

For those already in place, I can't do anything about whether they were properly set up to begin with. That's why I'm curious about setting up a checker/fixer type app. I could run it on a schedule once a week or so, and at least give me a list of ones that might need some attention.

And actually, most of the databases don't really ever (or at least rarely) have more than one user accessing the database at the same time. Some do, but it's not like we've got 100 people accessing the same database. Most anything of that level (if not everything at that level) is based on larger SQL-backend applications, totally outside Access.

 

[MajP]

For the existing databases, you could still split-them if they are not split.

In my opinion an Access database should not be run from a server ever. Even if only one user is on the database at a time, the front end should be on the users desktop. If not you are pulling all the form, report, and other data over the network, and this is the real cause of corruption. I have seen numerous times when I have tried to open a database that is on the network and it locks up and gets corrupted. Move a copy to the local machine it works fine. Split it and put the backend on the network and it works fine.

 

[kjv1611]

Good point. I did see similar issues on some databases that only get/got used for a day or a few days, and then they're finished with them. In setting them up, I had all sorts of errors - related to Windows 7 on our network, actually. I moved them to my C drive for building instead of over the network, and it worked great.

So, maybe I should have it set up to where the users can open one form on a "main" database that would create the local front-end copy on their machine in a usable location. If not, it'll end up that I spend more time answering questions, telling folks where to put their database, than actually getting work done.

But that's definitely something to think about. It makes sense that if only the data travels the network, the overall performance should increase. And if you multiply that many times for many users of different databases, it could be pretty good affect on the network, I suppose.

Now, if I only had about 10 full-time weeks to fit into one.

 

[kjv1611]

MajP,

Back on this idea - just thinking about it at the moment..

What about this thought...

I was thinking about how to create the database on the user PC when many of these users have almost NO rights on their local PCs.

So that got me to thinking - what about saving it to their %temp% directory upon clicking one button on a form located on a network database. So basically, I'd have the front-end sitting in a network folder, and a different database "front end" that would copy the correct database to the user's machine's temp folder for as long as they have it open. Then if they close the database, in order to view it again, they just click the same button from the network.

Any thoughts on that method, or what other suggestions might you have. I've already wondered quite a bit about getting front-ends running on local PCs instead of over the network, but that has not historically been the case here.