vba needed to edit access table

[greegan]

Hello,
I'm using Excel 2007, although others in the office may use 2010, and Access 2010 which I'm the only one that has it installed.

I have the following code (website included) which adds the data to my access table nicely.

http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_in_Microsoft_Excel/427.html

What I need to do is have vba edit any updates I make to my entry and well as read the table when entering the ID (or primary key)

Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("I:\Call-Centres\Access Test\transfers.mdb")
    ' open the database
    Set rs = db.OpenRecordset("tblTransfer", dbOpenTable)
    ' get all records in a table
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("ID") = Range("A" & r).Value
            .Fields("Style") = Range("B" & r).Value
            .Fields("FromStore") = Range("C" & r).Value
            .Fields("ToStore") = Range("D" & r).Value
            .Fields("Qty") = Range("E" & r).Value
            .Fields("tDate") = Range("F" & r).Value
            .Fields("Sent") = Range("G" & r).Value
            .Fields("Receive") = Range("H" & r).Value
            ' add more fields if necessary...
            .Edit ' update existing record
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Thank you for any help available.


-- g

 

[greegan]

Sorry, i didn't think of asking this in the access forum of mrexcel. if the PTB think this question is better suited for that forum, please bump it.
Thank you

-- g