Block committing changes to Microsoft Access database

Question

Is there a way to write a hook that will run before changes are committed to an Access DB? I'm looking for a way to block changes if a specific process is currently querying the DB.

Answer 1

You didn't give us much information to work with. Can you adapt something like Create and Use Flexible AutoNumber Fields so specific process first opens a table exclusively? Then any other operations which might change data would have to wait until they can lock that same table.

What is specific process? Do you have a method to determine when/if it is reading data from your database?

If specific process is external to the database, like web server ASP code which uses ADO to fetch data, you could see whether the ADO connection Mode and IsolationLevel properties can help.

Update: I used a form to experiment with the adModeShareDenyWrite Mode property for an ADO connection. While the form is open, other users can open the database, but not make any changes. However, if another user already has the db open when the form attempts to open the connection, it triggers an error.

Perhaps your deployment script could attempt to open a adModeShareDenyWrite connection, and bail out on error.

Option Compare Database
Option Explicit
Dim cn As ADODB.Connection

Private Sub Form_Close()
    If Not (cn Is Nothing) Then
        cn.Close
        Set cn = Nothing
    End If
End Sub

Private Sub Form_Load()
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=\\Cmpq\export\Access\backend\links2003.mdb;" & _
        "User Id=admin;Password=;"
    cn.Mode = adModeShareDenyWrite
    cn.Open
End Sub