0

I'm begging for help, since I'm too stupid. What I do:

  1. Open connection to Access DB
  2. Download Table to a Recordset
  3. Disconnect the RecordSet
  4. Update a record
  5. Update the same record in DB (by another user)
  6. Connect RecordSet back to DB
  7. UpdateBatch affected record

Yesterday it was throwing an error that the record has been modified in the meantime. Today it's updating the record without any issues. I would bet my arm I haven't changed anything...

I open the recordset with following settings (first set them, then open):

  • .CursorLocation = adUseClient
  • .LockType = adLockBatchOptimistic

I beg everyone for help

Example Code:

Dim DB_FOLDER_PATH As String
Dim DB_FILE_NAME As String
Dim DB_FILE_PATH As String
Dim CONNECTION As ADODB.CONNECTION
Dim CONNECTION_STRING As String

Dim QUERY_STRING As String
Dim tmp_RS As ADODB.Recordset
Dim tmp_RS2 As ADODB.Recordset

DB_FOLDER_PATH = "\\XXXXX\userdata\XXXXX\home\Documents\Data Base\"
DB_FILE_NAME = "TEST"
DB_FILE_PATH = DB_FOLDER_PATH & DB_FILE_NAME & ".accdb"

Set CONNECTION = New ADODB.CONNECTION
CONNECTION_STRING = "Provider=Microsoft.ACE.OLEDB.12.0" & ";" & "Data Source=" & DB_FILE_PATH & ";" & "Persist Security Info=False"
CONNECTION.Open CONNECTION_STRING

QUERY_STRING = "SELECT" & " " & "*" & " FROM [" & "DATA" & "]" & ";"

Set tmp_RS = New ADODB.Recordset
With tmp_RS
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .Open QUERY_STRING, CONNECTION
    .ActiveConnection = Nothing
End With

Set tmp_RS2 = New ADODB.Recordset
With tmp_RS2
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .Open QUERY_STRING, CONNECTION
    .ActiveConnection = Nothing
End With



With tmp_RS
    .Fields("FIELD_LONG_TEXT_PLAIN").Value = "ABC"
    .Update
    .ActiveConnection = CONNECTION
    .UpdateBatch
End With

With tmp_RS2
    .Fields("FIELD_LONG_TEXT_PLAIN").Value = "ZXC"
    .Update
    .ActiveConnection = CONNECTION
    .UpdateBatch
End With

Best Regards, Michal

Michael Mazur
  • 33
  • 3
  • I am a bit confused about why you would disconnect, change the record, then reconnect and update. I believe leaving the connection open, then disconnecting after the records are modified would prevent that. – Marx Mar 14 '22 at 22:25
  • This is just example code but leaving connection open and not disconnecting recordset(s) behaves the same - meaning changes are overwritten without any error. To explain why to disconnect - I'm using Excel worksheet as "GUI" to display and edit data. Then after many changes are done user can export data back to main DB. Due to connection lag it's much better to cache changes and then send bulk to main DB. – Michael Mazur Mar 14 '22 at 22:40

0 Answers0