MS Access Update Query where column updated depends on field in another table

Question

I have a table (InfoTable) that I would like to update based on another table (UpdTable). The UpdTable has three columns, OldEntry, NewEntry, and UpdateField. InfoTable has columns that will match UpdateField. I will have one line per update. So, for example, UpdTable:

OldEntry	NewEntry	UpdateField
Peter	Paul	Customer
123khjiv	3546sfdg	Serial Number

I would like to feed a SQL query or SQL code that takes UpdTable and have it update InfoTable. InfoTable has many columns, but I only want the columns in UpdateField modified.

I am trying to streamline this process since I am currently doing the updates one by one by hand. At this time, I do not have any code written as I have been unable to formulate a working plan.

Thank you.

Answer 1

Consider looping through the records of UpdTable and pass values into a parameterized UPDATE query using DAO Recordset and QueryDef objects:

Dim updRST As DAO.Recordset
Dim qDEF As DAO.QueryDef

' OPEN RECORDSET
Set updRST = CurrentDb.OpenRecordset("UpdTable")
updRST.MoveFirst

' LOOP THROUGH RECORDS
Do While Not updRST.EOF
    ' PREPARED SQL STATEMENT
    sql = "PARAMETERS [new_val] TEXT, [old_val] TEXT; " _
        & "UPDATE InfoTable SET [" & updRST!UpdateField & "] = [new_val] " _
        & "WHERE [" & updRST!UpdateField & "] = [old_val]"

    Set qDEF = CurrentDb.CreateQueryDef("", sql)
    qDEF!new_val = updRST!NewEntry                  ' BIND PARAMS
    qDEF!old_val = updRST!OldEntry

    qDEF.Execute dbFailOnError                      ' EXECUTE ACTION
    Set qDEF = Nothing

    updRST.MoveNext
Loop 

updRST.Close
Set updRST = Nothing