Ok so I have a database that is a front end for the main database on a shared server drive. The original was written in VBA of course and its Access 2003 and its been used now for about 8 years without incident (no more than normal)
Recently, there has been a lock file on the backend database created by different users (not a single user issue). I end their MSACCESS process and reboot their machine, and the lock remains in the network drive. So I start digging in deeper and after several restores later, it seems that the database is closing faster than the queries can finish updating the database. (the users are closing ACCESS with the red x)
Normally I would add some error catching to the "on exit" and "close" procedures but I wanted to see if anyone had some ideas or examples that they would be willing to share.
After this next restore, I will purge about 5years worth of data to see if that helps. Im assuming that the size of the database is making the update queries take too long and Access is closing before it can finish the updates. (not sure if my theory is correct but thats what all the evidence points to)
Thanks in advance.
EDIT: Rebooting the server is only a temporary fix, I want to find something or some error handleing procedure that will prevent this from happening when they close the database.
Ok so I have a database that is a front end for the main database on a shared server drive. ... it seems that the database is closing faster than the queries can finish updating the database....
If both front-end/back-end databases are in Access format, all the data processing (queries, recordsets) is performed locally by the front-end. Is this front-end stored on the local drive of every client machine (it should) or is it shared on a network drive (it should not)?
Both are located on the H drive for backup purposes. The front end is located on each machine as well. Its not the front end that is locking up, it is the back end that the front end updates. But I do understand that it could still be the front end that is causing the issue.
I did not write this database and there's quite a bit of unnecessary stuff, like placeholder tables for one value (10 of them) instead of making one for all ten. It will take me at least a month to re-write it. Got spoiled by having programmers working for me that I havent actually done any myself for about 3 years now, so Im really rusty. Im not seeing any close connection error handeling that would prevent the program from closing until the connection is closed or anything that would prevent an update if the program is closed improperly.
Just looking for ideas on a catch all that would make sure the connections are closed before access can close (even from the red x)
... Just looking for ideas on a catch all that would make sure the connections are closed before access can close (even from the red x)
If you want to be sure that a procedure is executed every time the front-end database is closed, here's a possible solution:
1. Create a form (name: Frm_DbCloseChecker)
2. Paste this code in its class module:
Private Sub Form_Open(Cancel As Integer)
Me.Move 0, 0, 0, 0 ' Form becomes invisible.
' If there was already a startup form defined
' for the database, open it here
' e.g. DoCmd.OpenForm <Startup Form>, <Options>
Private Sub Form_Close()
' Place the code that must be executed
' every time the database is closed here.
3. If there is a startup form already defined for the database, replace it by the new form Frm_DbCloseChecker (see comments in code).
4. If there is no startup form but an Autoexec macro exists in the database, edit it and open the form Frm_DbCloseChecker there before performing any other action.