Avoiding Database Corruption

[danikuper]

Avoiding DB corruption - any tips?

Hi!

I've created an Access 2000 DB with security at the user level and placed the .mdb file on my network. As there will be only 3 or 4 people accessing it at a given time, I thought I didn't need to split it into FE/BE.

By reading other posts in this forum I concluded that DB corruption is an unavoidable fact of life with Access. There must be, however, a few things one could do in order to avoid (or postpone) having a corrupted database.

What would you guys recommend to avoid DB corruption? Creating FE/BE databases or .mde instead of .mdb will help? Will Access give up on me after a certain number of records in the database (if yes, how many?)?

Your thoughts on this subject is greatly appreciated!!

Thanks in advance

 

[The_Doc_Man]

The cynic's view of this, is .... data reliability with a Gates product? Surely you jest.

OK, jokes aside, the best way I've found to really do this right is to have a very regular schedule of backing up the database during a quiet time. During this quiet time, you tell your users to get the hell out of the db so you can do a repair and a compress. Then, before you let everyone back in, make copies. More than one, on more than one disk!

Also, an issue that can sometimes become important is that the more you are doing at one time, the higher your exposure. So suppose, for example, that you have a form that opens some recordsets, does things, and goes about its business. If you are done with the recordsets, don't let them hang around. Close them. Even if they were read-only, their locks are still around as long as they are open.

Having too many locks sometimes has something to do with our problems. One of the things I did was to take a hard look at whether every form had to assert locks for everything. When I made certain sub-forms read-only in certain contexts and changed the locking strategy accordingly, we had a FEW less failures. Wish I could say a lot less failures, but that just wouldn't be true. And it might even have been coincidental since I had also scrubbed the code for open recordsets at the same time, so it is hard to say which action had which effect.

 

[danikuper]

Thanks for the advice.

I have in my database the "compact on close" option checked. Does this help at all or will only slow the shut-down process?

 

[Cosmos75]

Re: Avoiding DB corruption - any tips?

By reading other posts in this forum I concluded that DB corruption is an unavoidable fact of life with Access.

It is??


Why?


Can you link to the other posts you mentioned here?

 

[WayneRyan]

Cosmos,

From many previous posts here, one can come to many
conclusions:

Forms go "Brain Dead" and have to be re-created.

Databases go "Brain Dead" and import into a new db is the
only way to fix it.

Things don't work anymore - Compact/Repair.

I don't "trust" access that much. Backup frequently.

Wayne