Ok I've figured out some code to transfer data to Access but I can't figure
out how to make this loop for different records. As you can see in my code it
is transferring data for record 19. How do I add another statement to be able
to do this for another record?
Sub ADOFromExcelToAccesss()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Documents and Settings\My Documents\Work Databases\BC Prod Stds &
Calcs.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblSurcharge", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
strSQL = "select * from tblSurcharge where SurchargeID = '19'"
On Error Resume Next
..Open strSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
On Error GoTo 0
If .State = adStateOpen Then ' successfully opened the recordset
If .EOF Then ' no records returned

Else ' one (or more records returned)
' edit existing record
..Fields(1) = Range("D" & 25).Value
..Update ' stores the new record
End If
..Close ' close the recordset
End If
End With
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


Additional Info:

It seems to only work if the first "SurchargeID" is used. For example, my
first "SurchargeID" is '19' so it updates fine but if I change that '19' to
'20' it won't work. How do I have it look through the records to find the
right ID?