MS Access ADO recordset update

Question

Apparently, I need help with ADO record updates in my MS Access 2019 program which uses linked tables to a MySQL database via ODBC.

The following code fails at the .Update and also .MoveNext lines for each record in the recordset. This code works great if I take out the attempt to write a new calculated value to the [linetotal] column in the linked table.

The error msg I get says "The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time."

Well, there are no other users in this test module I am developing. Just me!

Any ideas where I have errored?

--------------------------------
Private Sub cboVendorPO_Change()
On Error GoTo Err_cboVendorPO_Change

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Dim strSQL As String
    Dim intVendor As Long
    Dim strName As String
    Dim intLineNo As Integer
    Dim dblNextLine As Integer
    Dim strCurrency As String
    Dim strPriceUM As String
    Dim dblPriceFactor As Double
    Dim intLineZero As Integer
    Dim dblLineTotal As Double
    Dim dblOrderTotal As Double
    
    dblOrderTotal = 0
    dblLineTotal = 0
    intNextLine = 0
    intLineZero = 0
    intLineNo = 0
    
    intVendor = Nz(DLookup("vendor", "venpoheader", "company = '" & lblHiddenCompany.Caption & "' and " & _
                "ponumber = " & cboVendorPO))
                
    strCurrency = Nz(DLookup("currency", "venpoheader", "company = '" & lblHiddenCompany.Caption & "' and " & _
                 "ponumber = " & cboVendorPO))
                 
    Me.txtCurrency = strCurrency
                   

    strSQL = "select * from venpodetail where company = '" & Me.lblHiddenCompany.Caption & "' and " & _
              "status = 'E' and vendor = " & intVendor & " and ponumber = " & cboVendorPO
     
    Set conn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    
    rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText
        
    rs.MoveLast
    rs.MoveFirst
    
    Do While Not rs.EOF
        With rs
            strPriceUM = Nz(rs!price_um)
            
            dblPriceFactor = 1
            
            If strPriceUM = "C" Then
                dblPriceFactor = 0.01
            ElseIf strPriceUM = "M" Then
                dblPriceFactor = 0.001
            End If
            
            dblLineTotal = Round(rs!poqty * rs!price * dblPriceFactor, 2)
            dblOrderTotal = dblOrderTotal + dblLineTotal
            
            If dblLineTotal = 0 Then
                intLineZero = intLineZero + 1
            End If
            
            .Fields("linetotal").Value = dblLineTotal
            .Update

            intLineNo = intLineNo + 1
            
            .MoveNext
            
        End With
        
    Loop
    
    rs.Close
    Set rs = Nothing
    
    conn.Close
    Set conn = Nothing
    
    strName = Nz(DLookup("name", "vendormaster", "vendor=" & intVendor))
    Me.lblName.Caption = strName
    Me.lblVendor.Caption = intVendor
    strName = ""
    
    Me.txtOrderTotal = dblOrderTotal
    
    Me.lstDetails.Requery
    
    If intLineZero > 0 Then
        MsgBox ("Errors found - " & intLineZero & "line(S) are missing price info.")
        Me.txtOrderTotal.BackColor = vbYellow
    Else
        Me.txtOrderTotal.BackColor = vbWhite
        
    End If
    
    dblNextLine = intLineNo + 1
    cboLineNo = dblNextLine
    
    cboPartNo.SetFocus

Exit_cboVendorPO_Change:
    Exit Sub

Err_cboVendorPO_Change:

    MsgBox Err.description
    Resume Next

End Sub
_________

Just need to be able to update each record with the calculated amount. I have researched this extensively in my 4 MS Access books and also online, both here in Stack Overflow and anywhere I could find online.

Saving calculated value (data dependent on data) is usually unnecessary and can be bad design. Do these calcs in query or report when needed. "User" is the code. Somehow, somewhere record is getting edit from two actions. This is usually caused from initiating record edit on form by normal data entry and then code try to run UPDATE action SQL. — June7, Aug 08 '23 at 17:18
Why looping recordset instead of an UPDATE action SQL? Again, don't advise saving these calcs but UPDATE action should be possible. Is this is a bound form with bound controls? Consider Conditional Formatting instead of VBA to set textbox BackColor. — June7, Aug 08 '23 at 17:34
I didn't do a looping SQL update because I do not know how to deal with the price units of measure calcs necessary at the same time. There are no bound controls on the form. I've use conditional formatting in Excel but not Access. Will look into that. — Dale, Aug 08 '23 at 20:17
With an UPDATE action SQL there would be no looping. SQL could call VBA function that returns appropriate unit info needed for calc. However, still advise to do these calcs when needed. Exception would be if some info (such as unit price) could change in future and don't want historical data impacted. In that case, save the unit price info and calculate totals when needed. I would need sample of data structrue to advise further. — June7, Aug 08 '23 at 20:30
This error can also be caused by Access / the ODBC driver *thinking* that the record has changed between reading and writing it. Especially with floating point data. See https://stackoverflow.com/questions/71142993/how-to-resolve-write-conflict-error-in-ms-access-when-writing-unchanged-record-t and https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html. With SQL Server as backend, a `rowversion` column would probably solve the issue. — Andre, Aug 08 '23 at 22:45
June7 is correct, a SQL UPDATE would also prevent the issue. It may pop up elsewhere again though. — Andre, Aug 08 '23 at 22:47
Thank you to everyone who contributed to this inquiry's solution. I have successfully resolved my update problem by adding a boolean field to the records in my table, and changing the value in it each time I process a record. Apparently MySQL cannot update a record that has not changed at all. — Dale, Aug 08 '23 at 23:55
Ok, in the interest of truthfulness and full disclosure. I find that while I had a DATETIME field in the MySQL record layout, I had not made this field NOT NULL. It is likely that this would have avoided the problem had I done so. I know better, and I apologize to this group if this is the case. I include this info here for others who may make this mistake. — Dale, Aug 09 '23 at 20:23

MS Access ADO recordset update

0 Answers0