How to lock a table in this VB6 / Access application?

Question

I am working on a VB6 application using an Access database. The application writes messages to the log table from time to time. Several instances of the application can be launched simultaneously and distinguish them, each of them has its own launch number. The start number is derived from the log table, so ...

Set record_set = New ADODB.Recordset
query_string = "SELECT MAX(RUN_NUMBER) + 1 AS NEW_RUN_NUMBER FROM ERROR_LOG"

record_set.CursorLocation = adUseClient
record_set.Open query_string, database_connection, adOpenStatic, , adCmdText
record_set.MoveLast

If IsNull(record_set.Fields("NEW_RUN_NUMBER")) Then
    run_number = 0
Else
    run_number = record_set.Fields("NEW_RUN_NUMBER")
End If

command_string = "INSERT INTO ERROR_LOG (RUN_NUMBER, SEVERITY, MESSAGE) " & _
                 "    VALUES (" & Str$(run_number) & ",                 " & _
                 "            " & Str$(SEVERITY_INFORMATION) & ",       " & _
                 "            'Run Started');                           "

database_connection.Execute command_string

Obviously, there is a slight gap between calculating the run number and the appearance of a new row in the database and preventing another instance from accessing between the two operations that I would like to lock the table; something along the lines

SET TRANSACTION READ WRITE RESERVING ERROR_LOG FOR PROTECTED WRITE;

How should I do it? Will a record record a record (a row in a recordset does not match any particular row in the database)?

Answer 1

( @MarkJ )

tblSession . , , .. , , , ID , .

Answer 2

, (, ...) Connection. ADO.

Answer 3

Since the Microsoft Jet database engine has a read cache and lazy writes, you can get duplicate values ​​in the user field field if two applications add records in less time than it takes to update the cache and the lazy write mechanism for flushing to disk. This article presents a method that takes these factors into account ...

How to implement multi-user custom counters in Jet 4.0 and ADO