Update multi tables in MS-Access via one SQL Code

pls answer the following questions.

what is the name of the field in the 100 tables that correspond to the field name in the "ReferenceTable"?

what is the name of the "ReferenceTable"?
is the "ReferenceTable" in the same database?

u can try:

Dim tbl As TableDef
    For Each tbl In CurrentDb.TableDefs
        SQL = "Update " & tbl.Name & " " & _
                    "Set Column X = (SELECT Ref from ReferenceTable WHEN ColumnX.Value = Ref.Value) "
        CurrentDb.Execute SQL
    Next

Select allOpen in new window

customize the looping logic and update SQL accordingly

Thank you Ryan

as I said, not all of the tables in the database but a majority of thyem may have this script applied for them

what about having a list of tables .. how we can do that ?

>>not all of the tables in the database but a majority of thyem may have this script applied for them
Yup, so you gonna tell us what tables to be excluded or included, and also try to answer on Rey's questions

Rey,

Table(i).Manufacturer = FinalUnion.Manufacturer

@venmarces

pls answer the questions above..

here is the code to list all tables

Dim td As DAO.TableDef, db As DAO.Database, sql As String
Set db = CurrentDb
For Each td In db.TableDefs
    If Not td.Name Like "Msys*" And Not td.Name = "ReferenceTable" Then
  debug.print td.name
    End If
Next

try this codes, replace [NameOfFielfdHere] with the actual name of field you want to update.


Sub updateTables()
Dim td As DAO.TableDef, db As DAO.Database, sql As String
Set db = CurrentDb
For Each td In db.TableDefs
    If Not td.Name Like "Msys*" And Not td.Name = "FinalUnion" Then
        sql = "update [" & td.Name & "] Inner Join FinalUnion" _
            & " On [" & td.Name & "].Manufacturer=FinalUnion.Manufacturer" _
            & " Set [" & td.Name & "].[NameOfFielfdHere]=FinalUnion.[NameOfFielfdHere]"
           
        db.Execute sql, dbFailOnError
    End If
Next
End Sub

is this a VBA -Code ? I don't have VB6 program ... how I can test this ?

If we use the TableDefs then I will use all the tables in the database. What about using a specific list of tables instead ? how this code may be changed to support it

thanks

getting error on

           
        db.Execute SQL, dbFailOnError

Object Required

try the codes i posted