CODEWITHSUNDEEP
excelvbams-accessado
Samuel Lindemulder
Samuel Lindemulder

Reputation: 25

Edit specific record key in Access using ADO recordset

Our work uses an Excel userform as a front end that feeds into an Access database.

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.

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.

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

Upvotes: 1

Views: 289

Answers (1)

Gustav
Gustav

Reputation: 55921

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

Upvotes: 1

Related Questions