Find Record in Access Using Excel

[Guest]

Hi
Would someone be able to provide the code to find a record in an Access
table using data in excel.

ie have an order number in excel and I want to find the record in the Access
database for the order number so I can update the record for that order in
Access table.

thanks
Noemi

 

[merjet]

Here is some code you can modify to fit your specific database, table,
etc. You need to check Microsoft DAO x.x Object Library using the menu
Tools | References.

Sub ModifyAccessRecord()
Dim db As Database, rs As Recordset
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
Set rs = db.OpenRecordset("TableName", dbOpenTable)
Do
If rs.Fields("FieldName1") = "something" Then
rs.Edit
rs.Fields("FieldName2") = "something else"
rs.Update ' stores the new record
End If
rs.MoveNext
Loop Until rs.EOF
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

HTH,
Merjet