Record Locking error (3260) while adding new records via VBA

[matchorno]

Hi. I've only posted here a couple times, and it was quite a while ago. So if i need to post in another section or have any other errors in my post, apologies...just lmk and i will correct it.



Access: 365 Enterprise (version 2108 (Build 14326.20600))
OS: Windows 10

Error 3260 = Could not update; currently locked by user 'blah blah' on machine 'blah blah'

I'm pretty sure i know why it's happening. We have an import procedure that can take a while to run. The db is pulling the data from mainframe screens. The following sounds kind of wonky, but i don't have access to mainframe tables (this is a judiciary application and the tables are pretty locked down)...so this is my only option...

The vba manipulates the mainframe...simulating keystrokes (F1, F2, Enter, etc.) to "push" it from one screen to another. It "scrapes" the info from the mainframe screens as it goes, then creates a record in the db. The procedure is looping through and adding 10-80 records and can take a few min to run depending on the amount of records.

We ran into the 3260 error when more than one person was importing at the same time. It's a rare occurrence (the import procedure is only run sporadically), but it can happen. They're importing and adding different records, but i think the collision is happening due to the primary key.

The code creates a new record, which assigns it a new autonumber for the primary key (i believe this happens before committing the record). The other user's import is also creating new records (same table), and so also creates autonumbers. I think the vba is creating new records very close together in time for each user...however one commits the record before the other (or i guess sometimes the commit tries to happen at the same time), and thus attempts to commit record with same primary key. One of the users also got the "this will create duplicate key entries" error as we tested, so i think my assumption may be correct. Hopefully this all makes sense.

  '--Next create and populate the Def Hearing record (or EDIT it if it already exists)
  If CaseAlreadyExists = True Then
    strWhere = "[DefHearingID] = " & DefHearingID
    strSql = "Select * from [tblDefHearings] where " & strWhere
    Set rsDefHearings = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    rsDefHearings.MoveFirst
    rsDefHearings.Edit
'--NOTE: No one would be editing the same record that already exists at the same time...it's the below (adding new records) that's causing the issue
  ElseIf CaseAlreadyExists = False Then
    strSql = "Select * from [tblDefHearings]"
    Set rsDefHearings = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
    rsDefHearings.AddNew
  End If
  '--Now that we are in ADD record or EDIT record "mode", go ahead and populate the fields.
  With rsDefHearings
    .Fields("JailIndicator") = JailIndicator
    ....more fields are added in this spot before updating
    .Update        '--this is where the procedure borks out
  End With
  Set rsDefHearings = Nothing

Is there maybe a way to maybe roll back the record creation if i trap this error, and then attempt to create a new record again which would assign a new (and hopefully different) primary key??

Any assistance is very much appreciated!

Thanks!

[orange]

For roll back etc. google MS Access transaction Processing

[Micron]

(i believe this happens before committing the record)

Not in Access, at least. You might see the autonumber but it is not committed until the record is saved. You can test this by cancelling a new record in a test table. Whatever autonumber it was going to use will be discarded and the next record creation will be greater than that last number. Perhaps you could initiate simultaneous record writes on a test table (on purpose) and see if there is a conflict. I would expect one user would see 2 coming after 1 and the second one in would see 3 but I don't know. Perhaps it behaves differently with code.

Also consider that it is possible to write code that attempts to update a record that is already being updated; i.e. you can generate that error even when you're the only user in the db. Another possible fix is to employ record locking - perhaps on the whole table, or work on a recordset instead (if one of the recordset types will work better for you).
https://docs.microsoft.com/en-us/off...set-object-dao

You might also initiate a flag in a be table (this db is split of course?) so that the process can't begin if someone has already set the flag.