How to match Access and Excel records and update Access database?

Question

I have a column with vendor name in an Access table which updates every day (from blank to name) with respect to delivery number (unique).

Every day I want to extract the data from SAP and update the records whose vendor name got updated in System.

I need a SQL query which will match the delivery numbers in Access and Excel workbook and according from Excel work it will capture name of vendor and update Access database.

I have written code that can update only one name at a time, but I want to update everything in one go.

Sub Update()
    Sheets("Carrier Updated").Select

    'Initialize all variables
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim stDB As String, stSQL As String, stProvider As String

    Dim X As Variant

    X = Range("A2").Value

    Dim Y As Variant

    Y = Range("B2").Value

    stDB = "C:\Users\yemdema\OneDrive - Ecolab\Desktop\Drive - A\Delivery Creation\DB Backup\Test_1.accdb"
    stProvider = "Microsoft.ACE.OLEDB.12.0"

    'Opening connection to database
    With cn
        .ConnectionString = stDB
        .Provider = stProvider
        .Open
    End With

    'SQL Statement of what I want from the database
    stSQL = "UPDATE Delivery_Creation set [Carrier Updated Later] = '" & Y & "' where[Delivery] = '" & X & "'"

    Set rs = cn.Execute(stSQL)
    MsgBox ("Carrier has been updated")

    'Looping through the records I pulled and inserting the data into the comboBox
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Sub

What are the field types for [Carrier Updated Later] and [Delivery]? Also, you need a space after the word where. — braX

Gustav Gustav · Accepted Answer · 2019-12-08T09:35:03

As you probably have different values for X and Y for each pair of values found in the worksheet, you can either update one set (as you do now), or collect these in a (temporary) table and call an update query using that table. No big difference, though.

Or you could "reverse" the process, using Access to link the range in the worksheet as a linked table, and then run an update query using that table.

How to match Access and Excel records and update Access database?

1 Answers