0

I would like to know if it is possible to lock database tables to one user at a time to perform the ACID terminology on the database, and allow one full transaction at a time in Microsoft Access database using VBA. I am looking for something like the following in pseudocode:

  • Lock all tables (or certain tables).
  • Perform all SQL or other inner operations.
  • Perform the transaction and unlock all tables (or certain tables).

But, in the case of client failure, e.g. the client application hangs and the user have to forcefully shut it down while the client application still processing a transaction, it will revert the changes and never actually do the transaction, and unlock the lock. What I am looking for is a READ and WRITE lock, and the client that tries to lock the database while it is already locked, it will have to wait until it gets unlocked?

falhumai
  • 163
  • 15
  • this is the beginnings of what you want http://stackoverflow.com/questions/5792169/opening-access-database-in-exclusive-mode#answer-5792773 – S Meaden Feb 18 '17 at 20:38

2 Answers2

1

Wrap your transactions within a transaction of the workspace:

Dim wks     As DAO.Workspace
Dim dbs     As DAO.Database

Set wks = DBEngine(0)
Set dbs = wks.Databases(0)

wks.BeginTrans
    ' Do stuff using dbs and DAO.
wks.CommitTrans

Set dbs = Nothing
Set wks = Nothing

If errors are potential, include an error handler that skips CommitTrans and calls:

wks.RollBack
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • But that doesn't block reading as well. I have tried that before!! – falhumai Feb 18 '17 at 10:00
  • 2
    You should have mentioned that in your question, acompanied by why you think this doesn't solve your problem. It is still unclear to my why other users shouldn't have read access while the transaction is running. ACID (although Access does not fully comply to it) guarantees that there will be no inconsistent state in the database: Either the transaction is *successfully* completed, or not at all. There is nothing in between. Concurrently reading during another user's transaction is a central feature that is guaranteed by ACID. – Leviathan Feb 18 '17 at 10:36
  • 1
    @Leviathan is right. There is no way to block reading if the user has access to the file. Reading is the lowest possible level of access. Access can only be blocked if actually no users are using the database; the first user can then open the file exclusively (you may call it _single user mode_). Then no other user can open the database. This, however, can't be done in VBA - it is controlled by command line. – Gustav Feb 18 '17 at 11:00
0

Actually, I found a way to simulate the single user access for reading and writing through a made up hacky lock type. The following would describe how I achieved it.

So, I made a small table in access, say, called Lock, and has one column called SomeValue. This column must be a primary key, and it could be of any value, so I made it of type number, for instance. This table will store all the locks that will be made in it, and the sides that are trying to acquire the lock must agree on the value of the lock. For instance, two clients will try to acquire lock of value 1, so they should request for lock 1, and release lock 1.

First, here are two helper queries I made to set and release the lock, by passing the value of the lock the sides that try to acquire it:

SetLock query:

INSERT INTO Lock (SomeValue)
VALUES ([GetLockValue]);

ReleaseLock query:

DELETE *
FROM Lock
WHERE SomeValue=[GetLockValue];

Then, here is the TrySetLock Function (which would try to set the lock of the passed in value, and return the set results, where 1 is a pass, and 0 is a fail) and SetLock Sub (which will wait until the lock by the passed in value is empty to acquire it - it uses spin lock method for lock acquiring):

Public Function TrySetLock(LockValue As Integer) As Integer
    Dim dbs As dao.Database
    Dim qdf As dao.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("SetLock")
    qdf.Parameters("GetLockValue").Value = LockValue
    qdf.Execute
    TrySetLock = qdf.RecordsAffected
End Function

Public Sub SetLock(LockValue As Integer)
    Do While TrySetLock(LockValue) = 0
    Loop
End Sub

And here is the ReleaseLock Sub (which would release the lock by the passed in value - this Sub will always succeed, even if there no such lock exists):

Public Sub ReleaseLock(LockValue As Integer)
    Dim dbs As dao.Database
    Dim qdf As dao.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("ReleaseLock")
    qdf.Parameters("GetLockValue").Value = LockValue
    qdf.Execute
End Sub

As you may see here, I have used the help of the primary key property of SQL and Microsoft Access tables to make sure insertion (or as referred to here by locking) can only succeed for one side or client at a time, and will never succeed for the other side unless the first side removes (or releases) the lock of the same value of the lock both sides agree on.

However, this would pose a problem of blocking ALL clients relying on a same lock if one client failed to release the lock (say a client's program is frozen, and had to force kill the program). I would like to know if destructors for Class modules will be called when a program is forced to be killed or not? If it gets called, then I think this problem can be fixed by making a lock class with some value, and the destuctor of that class would release the lock, and not having to make other clients wait for that certain lock.

falhumai
  • 163
  • 15