Error Handling Tips

[mbath]

I manage and maintain a few multiuser Access databases (each one has no more than 5 users at any given time).

One occassional nagging problem that I encounter is my db being placed in an 'unuseable' state (i.e. db needs to be repaired before continued use).

I am assuming that this problem is most likely attributed to some bugs or run-time errors in some of my code that is somehow locking up my db.

Would like to solicit any advise or opinions on how best to outfit my code with some error handling that will best 'trap' these errors and help me come up with code that will 'gracefully' close out the offending procedure thus preventing my db from getting wrecked.

Would you suggest code in the OnError event of each form, each procedure, or both? And can I both trap and report the error and close out the form or procedure without disrupting my users?

thanks

 

[ghudson]

All of your subs and functions should have proper error trapping. You should always ensure that your code compiles after you have made any changes to a module.

I supect your corruption problem is caused by something else. Is your db split with the front end on each user's hard drive which should be linked to the back end on a server? If not then that is a big reason for constant corruption.

Are the users exiting the db with a close button that you have created on a form? The old Ctrl+Alt+Delete combo is a db killer. Not closing the db correctly will corrupt your table indexes.

Search this forum for the error message you receive in regards to it needing to be repaired.

 

[mbath]

G,

thanks for the comments. do not have the db split. i have indeed seen that approach recommended many times over and maybe i'm just being stubborn but it just seems to me to be an extraordinarily inefficient way of doing things.

i keep avoiding this due to the time and effort expended everytime i need to modify or upgrade the db. means, of course, that i will have to update 5 copies instead of one.

as it stands, i have one self-contained .mdb file. tables, forms, queries, reports and macros all-in-one. i think i sensed a cringe but as i said in my original post, it is 95%+ reliable.

i do indeed have a Close or Cancel button on any and all forms that my users can interact with. my suspicion is that i may have an instance or two where users are being impatient with some of my buttons that launch procedures and are getting 'click-happy'. they can be slow to fess up to this but i think i may have observed this for myself the other day.

any thoughts on how to compensate for this? i.e. 1. user clicks 2. nothing happens 3. user clicks again 4. database generates error message and gently either resets the module or returns user to main menu.

thanks again for your input G.

 

[Mile-O]

maybe i'm just being stubborn but it [using a split database] just seems to me to be an extraordinarily inefficient way of doing things.

It's more efficient and less hazardous.

i keep avoiding this due to the time and effort expended everytime i need to modify or upgrade the db. means, of course, that i will have to update 5 copies instead of one.

No, you only update/amend your development copy of the frontend. Once ready you can compile it and redistribute the new frontend to as many users as you like (within reason) and you don't have to chase people (via phone call, email, whatever) to get out of the database because you are updating it.

as it stands, i have one self-contained .mdb file. tables, forms, queries, reports and macros all-in-one. i think i sensed a cringe but as i said in my original post, it is 95%+ reliable.

That's 5% not reliable.

 

[mbath]

point taken ....

safe to say then that you're fairly confident that my corruption problems are due to multiple users sharing one database?

 

[feelinfroggy]

Tips

I have 4 multiuser databases which have anywhere from 2 to 10 users. Each is a front end for a SQL database. I use batch files to copy the original front end (stored on a network drive) down to the user's hard drive. I can make whatever changes I want to the front end and the next time the users open the database the new front end is copied down for them. Sure does make life easier.

 

[mbath]

nice !!!

any significant delay when the databases are copied down? users need to be given any kind of heads up as to what's going on behind the scenes?

 

[feelinfroggy]

Tips

Delays occur occasionally if the machine is slow to process the request. Some PCs are just faster than others, anyway. The most delay I have seen is about 5 seconds before Access opens and displays the file. Keep in mind I am only copying down the front end and not any of the tables. Tables are stored on a SQL server. I have both adp and mdb files running. I also have code strategically placed in my forms to cause the record to save immediately after the Primary key is created to eliminate two (or more) users from using the same record. This eliminates some nasty errors that can contribute to that "unusuable state" your database occasionally ends up in. It also eliminates unhappy users who are ticked off b/c they have to reenter all their information.