I'm working on a VB6 application using an Access database. The application writes messages to a log table from time to time. Several instances of the application may be running simultaneously and to distinguish them they each have their own run number. The run number is deduced from the log table thus...
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 small gap between the calculation of the run number and the appearance of the new row in the database, and to prevent another instance getting access between the two operations I'd like to lock the table; something along the lines of
SET TRANSACTION READ WRITE RESERVING ERROR_LOG FOR PROTECTED WRITE;
How should I go about doing this? Would locking the recordset do any good (the row in the record set doesn't match any particular row in the database)?
