REPLACE data in Access table-CodePudding

I use VBA Word with Access, to create/store medical visit notes for Nursing homes.

The following is an example of how I get data out of Access (obviously picking up mid-Sub). This is populating a ComboBox in Word which gives me a list of all my patients, it is working great!

'....

    Set Conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    strConn = ActiveDocument.CustomDocumentProperties("strConn").Value
    Conn.Open (strConn)
    qry = "SELECT * FROM tblPatientInfo ORDER BY LastName, Firstname"
    rs.Open qry, Conn, adOpenKeyset
    rs.MoveFirst
    x = 0

    While Not rs.EOF
   
    F1.ComboDashPtList.AddItem
    F1.ComboDashPtList.List(x, 0) = rs.Fields("LastName").Value & ""
    F1.ComboDashPtList.List(x, 1) = rs.Fields("FirstName").Value & ""
    F1.ComboDashPtList.List(x, 2) = Format(rs.Fields("DOB").Value, "MM\/dd\/yyyy") & ""
    F1.ComboDashPtList.List(x, 3) = rs.Fields("MedNumber").Value & ""
    rs.MoveNext
    x = x   1
    Wend
    rs.Close
    Exit Sub`

'....

This is an example of how I send my data back to Access (again picking up mid-Sub).

`   Set Conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    Conn.Open (strConn)
    rs.Open strDBPtInfo, strConn, adOpenKeyset, adLockOptimistic, adCmdTable
    rs.AddNew
    rs!MedNumber = strMedNum
    rs!LastName = strLastName
    rs!Firstname = strFirstName
    rs!DOB = dtDOB
    rs.Update `

'....

Sometimes I need to completely overwrite or add to a specific field in a certain Access table. For years 'someone may have an allergy to penicillin, but suddenly they are also allergic to codeine, so that has to 'be updated. This is the approach I've taken but I keep getting an error:

'....

`   Set Conn = New Connection
    Set rs = New Recordset
      
    Conn.Open (strConn)
    strUpdateqry = "SELECT * FROM tblMedHxInfo WHERE MedNumber = " & Chr(34) & strMedNum & Chr(34) & ""
    rs.Open strUpdateqry, strConn, adOpenKeyset
    rs!Allergies = "Penicillin, Codeine"                
    rs.Update
            
    If rs.State = 1 Then rs.Close
    If Conn.State = 1 Then Conn.Close
    Set rs = Nothing
    Set Conn = Nothing`
                    
....

This is the Error:

"Run-time error '3251': Current Recordset does not support updating. This may ne a limitation of the provider, or of the selected locktype"

'Any help would be greatly appreciated!

'Thanks, 'Derek

'I've tried using the recordset to create a temporary table and then use that to update but it's getting over my head

CodePudding user response：

Explicitly declare connection and recordset type. Then Set lines are not required.
Set the lock type argument.
Reference connection object not string variable for opening recordset.

Dim cn As ADODB.Connection, rs As ADODB.Recordset
cn.Open (strConn)
strUpdateqry = "SELECT * FROM tblMedHxInfo WHERE MedNumber = " & Chr(34) & strMedNum & Chr(34)
rs.Open strUpdateqry, cn, adOpenKeyset, adLockOptimistic               
rs.Update "Allergies", "Penicillin, Codeine"

Or instead of opening recordset object for insert or update:

Dim cn As ADODB.Connection
cn.Open (strConn)
cn.Execute "UPDATE tblMedHxInfo SET Allergies = 'Penicillin, Codeine' WHERE MedNumber='" & strMedNum & "'"