Locking Databases with ADO

I have a utility which queries a table for a counter value, then increments it. All was working well until more than one person decided to use it at the same time. As a result, it is possible for two people to get the same counter value. I would like to make it that the second person can not even get the results until after the first one has completed the update. I have not worked with locking before, so I do not know how to progress.

Also, how should it be handled when one utility tries to access the locked data? Is this automatic, or should error trapping be used to loop back and try again?

The code looks like the following:

Sub UpdateNextDocID(oCustomDB As CustomDB)
Dim QueryString As String
Dim RSresults As Recordset

On Error GoTo ErrorHandler

QueryString = "SELECT Next_Doc_Id FROM tblControl_File WHERE Control_Key= 1;"

If Not oCustomDB.RunQuery(QueryString, 1) Then
' Raise Error
End If

Set RSresults = ADOconnection.Execute(QueryString)

If Not RSresults.EOF Then
If IsNull(RSresults.Fields(0).Value) Then
DocID = ""
Else
DocID = RSresults.Fields(0).Value
End If
End If

QueryString = "UPDATE tblControl_File " & _
"SET Next_Doc_Id = Next_Doc_Id + 1 " & _
"WHERE Control_Key = 1;"

Set RSresults = ADOconnection.Execute(ExecuteString)

Exit Sub

ErrorHandler:
' Error Handling Code
End Sub

True record locking and transaction isolation in Access is quite difficult. You will get much more consistent, robust results from SQL Server or Oracle. Here a nice article which will provide you with some insight. And it links to another discussion as well.

http://www.visualbasicforum.com/showthread.php?t=174674