Error handling

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi there,

I have developed a shared database that runs VBA code at regular intervals
(extracting and joining data from other data sources) and at times during
the day, other users will access the database to run reports against some
tables and so forth.

I've been encountering problems such as locking errors, network errors and
so forth, that have stopped the app with a dialog box waiting for user
input. This often happens overnight and unfortunately, my app runs a daily
routine just after midnight which it never can do because of these errors.
(this is a problem because the first user in the morning has to reset the
app, and rerun the overnight jobs manually which can take up to 2 hours,
hence the reason I do it overnight).

Ideally, I'd like to be able to trap such errors and be able to instruct my
app to take another course of action if a stop error is encountered. For
instance, if I can detect the error, I can skip certain parts, or retry at
predefined intervals, or even send an email to an administrator to take
manual action.

I'd love to hear from anyone with experience in this area or to point me in
the direction of resources to understand what strategies I can use to
improve the resiliency of this database.

Many thanks,
Pete
 
Firstly, if you have not already split the database into back end (tables
only) and front end (all other objects, and linked tables), see:
http://members.iinet.net.au/~allenbrowne/ser-01.html

Secondly, it sounds like you need to log users out after a period of
inactivity. See:
http://support.microsoft.com/?id=128814

Next, check your locking strategy. Optimistic is almost always the best.
Under Tools | Options | Advanced, set "Default Open Mode" to Shared, and
"Default Record Locking" ot "No Locks.

Presumably you already have error handling in your VBA procedures. If not,
see:
http://members.iinet.net.au/~allenbrowne/ser-23a.html

For each form, you will also want to trap its Error event. Create a function
in a standard module, and call it from Form_Error, so that you can deal with
the errors in an appropriate way.

Finally, if the problems you refer to are the result of executing action
queries during the night, you need to use transactions to be able to roll
them back if they did not succeed. For an example and discussion of the
pitfalls, see:
http://members.iinet.net.au/~allenbrowne/ser-37.html
 
Thanks Allen, some great resources here to help me out. Appreciate the
advice.

Cheers,
Pete
 
Thanks Steve, I've played around with timings myself, improved, but not
solved the problem. I'll keep at it.

Thanks,
Pete
 
Nice one Steve, I like this idea a lot and think it will really help me.

I'll start working on that and see how I go.

Thanks mate,
Pete
 
Back
Top