3

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

Tony Toews
  • 7,850
  • 1
  • 22
  • 27
Brian Hooper
  • 21,544
  • 24
  • 88
  • 139
  • From what I can see you are trying to roll your own version of an auto increment field. I would just change the run number field to be an AutoNumber in access and let JET handle the creation of the numbers as opposed to trying to do it yourself. – Kevin Ross Jan 05 '11 at 11:59
  • @Kevin Ross, not really. The plan is that the program will then write many rows to the ERROR_LOG table with the same RUN_NUMBER (which serves to identify the instance responsbile), so an autoincrement wouldn't be suitable. Thanks for the suggestion. – Brian Hooper Jan 05 '11 at 12:37
  • 2
    @Brian Hooper @Kevin I still think Kevin has something. You could create another table with one record per run number, and use auto-increment in that table. It would be worth it for the simplicity. Possibly there might be information that is recorded once per run, and you could store that info in that new table too (PC identity? username? ...) – MarkJ Jan 05 '11 at 12:43
  • @MarkJ, you're quite right. I expect a use for the additional table would be found; if I can't find how to lock the table I'd certainly go with that (it may be I need locking for similar sorts of things later). Thank you. – Brian Hooper Jan 05 '11 at 12:56
  • 2
    @MarkJ Excellent idea, when the users opens up the app it makes a new record in the session table with the user’s details etc. The auto number would be returned to the user and it would use that to log an errors. You can then enforce referral integrity to make sure that no one can put data into the errors table without having a session – Kevin Ross Jan 05 '11 at 13:24
  • @Kevin Ross, @MarkJ, I gave this notion some thought, but (unless there's something I've completely overlooked), there would still be the same gap between inserting the row into the auto-numbered table and getting the value back again. Having looked around, I still haven't found any way to do this. Thank you for your help and suggestions. – Brian Hooper Jan 06 '11 at 08:50
  • Access will handle this for you using the AutoNumber feature and/or you set the session ID as a unique index. The JET DB Engine will not allow the same number to be used twice as it will be a unique key on the table. Just insert a record into the table and read back the ID number that is generated, no need to do anything like “SELECT MAX(ID)+1” – Kevin Ross Jan 06 '11 at 09:39
  • @Kevin Ross, thank you for that; if you would care to paste your comments into an answer, I'll vote for it, and accept it. You've earned it, I think, and any further difficulties I have I'll turn into another question. I find it rather frustrating that the ruthlessly efficient databases are so easy and simple to use, but the helpful caring ones are so difficult. – Brian Hooper Jan 06 '11 at 10:54

3 Answers3

1

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.

RolandTumble
  • 4,633
  • 3
  • 32
  • 37
  • thank you for the suggestion, but this would require exclusive access for the connection, something I don't think it is likely to get if there is a lot of activity. – Brian Hooper Jan 06 '11 at 08:49
  • I thought I remembered an option that issues exclusive locks *for the life of a transaction*, rather than the life of the connection, but I didn't check it so I could very well be wrong. – RolandTumble Jan 06 '11 at 18:05
1

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

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
1

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.

Kevin Ross
  • 7,185
  • 2
  • 21
  • 27