CODEWITHSUNDEEP
ms-accessvb6jet
Brian Hooper
Brian Hooper

Reputation: 22034

How should I lock the table in this VB6 / Access application?

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)?

Upvotes: 3

Views: 1767

Answers (3)

Kevin Ross
Kevin Ross

Reputation: 7215

To sum up all of my comments into an answer (also thanks to @MarkJ for the initial idea)

You need to make a table called tblSession or similar. This table would have an autonumber primary key and then a few helper fields such as user name and machine number etc. When the application opens it creates a record in this table and reads back the ID number, the application then uses this as its session number. As we have defined the ID field as a unique key it will not issues out duplicate numbers.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

Because the Microsoft Jet database engine has a read cache and lazy writes, you can get duplicate values in your custom counter field if two applications add records in less time than it takes for the cache to refresh and the lazy-write mechanism to flush to disk. This article presents a method that takes these factors into account...

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO

Upvotes: 1

RolandTumble
RolandTumble

Reputation: 4703

The comments about adding a table are spot-on, but to directly answer your question (sort of, at least...), the record locking strategy is controled by options on the Connection object. Check the ADO help.

Upvotes: 1

Related Questions