Table-Level Lock

[kingle1]

I have a multi-user application that requires a table-level lock
during an update so no users can write to the table during the update.
(All records are written out to a temp table, the records in the main table are then deleted, the records are sorted in the temp table, and the sorted records are then re-written to the original table during the update. This is done to maintain the table relationships.)

I tried adLockPessimistic, but that only accomplishes a record-level
lock. I need a table-level lock that will either return an error or simply wait when a user tries to write during the time the table is locked out.

Any help will be greatly appreciated.

 

[Banana]

While that is a first for me and looks suspiciously like overkill to me, I would say that you can achieve same effect by opening the Access database exclusively. Search the forum on kicking out the users and opening exclusively.

 

[kingle1]

It is overkill, but it is the hand I've been dealt. This is not my application -- I've merely been brought in to clean up someone else's mess. This involves a flat (non-normalized) table that has to be sorted horizontally. I can make everything work except keeping users out of the table while the VBA code is running -- less than 60 seconds.

 

[Banana]

Ov vey. Hope you get it cleaned out eventually. Exclusive access is pretty much the way to do it. There's plenty of posts about that on the board.

Best of luck!

 

[kingle1]

But if I'm not mistaken, are you not able to log in exclusively if other users are already logged in? This is an application in which the front and back ends are all shared, the forms are tied directly to the tables (not recordsets) and I have over 300 users that could be logged in at any given time. It's a mess, and I've been asked to fix it without modifying the interface.

 

[Banana]

Right, which is why I said to look for posts on how to kick out the users, which can be done, then you can get in exclusively.