update a certain row and column

[kdggt]

This is a Module in an Excel VBA program. I am working in Excel 2010 and Access 2010.

This works fine if I am adding data to the end of the file.

How do you change "rs.AddNew" to update a certain row and column already entered in the database, db1?

Sub Initial()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ConnStr As String
    Set cn = New ADODB.Connection
    ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        "C:\path\db1.mdb;"
    cn.Open ConnStr
    Set rs = New ADODB.Recordset
    rs.Open Source:="table1", ActiveConnection:=cn, _
        CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _
        Options:=adCmdTableDirect
    rs.AddNew
    rs!Index = Sheets("2013").Cells(104, 1).Value
    rs!DatePaid = Sheets("2013").Cells(104, 2).Value
    rs!WhatPaid = Sheets("2013").Cells(104, 3).Value
    rs!AmtPaid = Sheets("2013").Cells(104, 4).Value
    rs!total = Sheets("2013").Cells(104, 5).Value
    rs!AmtRec = Sheets("2013").Cells(104, 6).Value
    rs!WhatRec = Sheets("2013").Cells(104, 7).Value
    rs!DateRec = Sheets("2013").Cells(104, 8).Value
    rs.Update
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

 

[Catalina]

How about rs.Edit instead of rs.AddNew

Catalina