Edit specific record key in Access using ADO recordset VBA-CodePudding

Our work uses an excel userform as a front end that feeds into an access database currently. I want to allow the user to edit the access records from the same front end without holding up the database by going into access. I can't seem to find any good or straightforward answers to this question on the forums, as most all of the questions only concern bulk updates of records in access. Here is what I have so far. The database name is "database3" (it is an mdb access db). I want to look for a match from the userform (textbox2) in the ID field in access. Then I want the textbox1 value to replace the current value in column1. Any help would be appreciated as I am pretty new to working with ADO, but excited to learn more about it.

`Sub Save_Data()

    On Error GoTo ErrorHandler
    
    Application.EnableCancelKey = xlDisabled
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Dim nConnection As New ADODB.Connection
    Dim nRecordset As New ADODB.Recordset
    
    Dim sqlQuery As String
    
    'Connection Strings - Dynamic Path

    #If Win64 Then
        nConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\sam\Desktop\Database3.mdb" & ";Jet OLEDB:Database"
    #Else
        nConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Users\sam\Desktop\Database3.mdb" & ";Jet OLEDB:Database"
    #End If
    
    
    sqlQuery = "Select * from Table1"
    
    'Open the recordset
    
    nRecordset.Open Source:=sqlQuery, ActiveConnection:=nConnection, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    
    
If nRecordset.Fields("Column1").Value = UserForm1.TextBox2.Value Then


        nRecordset.Edit.Fields("Column1") = TextBox1.Value
        
        .Update
        
        .Close
    
End If

    
    nConnection.Close
    
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    Exit Sub
    
ErrorHandler:

    MsgBox Err.Description & " " & Err.Number, vbOKOnly   vbCritical, "Database Error"
    
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
     nConnection.Close

End Sub`

CodePudding user response：

No Edit is need for ADO, so try:

If nRecordset.Fields("Column1").Value = UserForm1.TextBox2.Value Then
    nRecordset.Fields("Column1").Value = UserForm1.TextBox1.Value        
    nRecordset.Update   
End If
nRecordset.Close