solution needed - 2 users running a program

Question

Hi guys, So I've developed this Access 2007 application with about 2 forms, a lot of VBA code and a bunch of tables.

Now the business wants to run this off a network drive (call it G:\ for example). My current solution (which I've already implemented is have a table similar to:

__________________
|Setting | Value |
==================
Updating    1
UpdateBy   User1

So let me give you a context. When the application runs there is a button called "update" which updates the local table from a remote server so we can apply filtering. Now when two people (user1, user2) launch the application, and one person clicks update then the updating field is set to true, and the updateby is set to their name. So User number 2 tries to update, it checks if the updating field is true, if it is then it gives them a message (to user two, not to user one).

It works beautifully right now, but here is the problem: Lets say user1 is updating, and closes his program (or taskkills it) or the power disrupts, then the application shuts off with the updating field set to to true. Now no matter who launches it, they can not update because its "already updating"

Can you guys think of a solution to this? Maybe a workaround?

Answer 1

Please do not run an Access application with more than one user when you have not split the database. It will cause endless trouble. The data portion (back-end) should be put on the server and the code and forms (front-end) should be put on each users desktop.

More information: http://support.microsoft.com/kb/162522

Answer 2

Consider a different locking strategy. In the click event of your "update" button, you can first open a recordset based on your tblUpdateStatus (the table where you've been writing UpdateBy) with dbDenyWrite + dbDenyRead options.

Set rst = db.OpenRecordset("tblUpdateStatus", _
             dbOpenTable, dbDenyWrite + dbDenyRead)

Then do your other operations for the "update" button. Afterward, close and release the recordset ... which releases the tblUpdateStatus lock.

Trap the error when a user is unable to open the recordset (because another user has the table locked), give them a message to try later and exit your click event subroutine.

With this approach, when user1 locks tblUpdateStatus but exits Access uncleanly, her lock on tblUpdateStatus is released. You may not even need to update tblUpdateStatus unless you want to record which user has it locked.

See Create and Use Flexible AutoNumber Fields (from the Access Cookbook) for more details about using a recordset with dbDenyWrite + dbDenyRead.

Answer 3

Read my article on why you split here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

In the above, I don't just tell you to do this, but I tell you WHY you split.

It should help you a lot in terms of users tripping over each other.