Update multi tables in MS-Access via one SQL Code

Hello,

I have an old MS Access database that contains more than 100 tables, most of these tables are similar: so they have the same structure and the same number of columns with the same data-type.
Now, I want to make a SQL Script that allows me to open every table in this database and update a specific column values based on a JOIN on another table ... here is a simulation

For I=1 to 100
Open everytable
Update Table(i)
Set Column X = (SELECT Ref from ReferenceTable WHEN ColumnX.Value = Ref.Value)

I ++

Thank you
venmarcesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Open in new window

customize the looping logic and update SQL accordingly
venmarcesAuthor Commented:
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 ?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>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
venmarcesAuthor Commented:
Rey,

Table(i).Manufacturer = FinalUnion.Manufacturer
Rey Obrero (Capricorn1)Commented:
@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
Rey Obrero (Capricorn1)Commented:
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
venmarcesAuthor Commented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can create a form in Access, and then perhaps to have a button to run the script above. yea, this is VBA codes. You can also pressed Alt+F11 to switch to VBA Editor.

>>If we use the TableDefs then I will use all the tables in the database. What about using a specific list of tables instead
That's achievable as well, like:

Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("yourTableList", dbOpenTable)
    For i = 1 To rs.RecordCount
   
        sql = "update [" & rs("tableName") & "] Inner Join FinalUnion" _
            & " On [" & rs("tableName") & "].Manufacturer=FinalUnion.Manufacturer" _
            & " Set [" & rs("tableName") & "].[NameOfFielfdHere]=FinalUnion.[NameOfFielfdHere]"
           
        db.Execute sql, dbFailOnError

       rs.MoveNext
    Next

Open in new window


assuming a table as follows to store the list of the tables you want to verify:

Table: yourTableList
fields: tableName

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
to create a table with names of tables you want to update.
1. create a new table with two fields  TableName (Text), Include (Yes/No)
2. save the table as TableList

place this codes in a regular module
Sub getTables()
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"  And Not td.Name = "TableList" Then
  
        db.execute "Insert into TableList(TableName) values('" &  td.name & "')"
    End If
Next 
end sub

Open in new window



run the codes, then open the table "tableList" and check the field Include for every table you want to be updated..

use this codes to update the tables

Sub UpdateTables()

Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("select tableName from TableList where Include=-1")
    do until rs.eof
   
        sql = "update [" & rs("tableName") & "] Inner Join FinalUnion" _
            & " On [" & rs("tableName") & "].Manufacturer=FinalUnion.Manufacturer" _
            & " Set [" & rs("tableName") & "].[NameOfField_tobe_updated_Here]=FinalUnion.[NameOfFieldHere]"
           
        currentdb.Execute sql, dbFailOnError

       rs.MoveNext
    loop
	rs.close
end sub

Open in new window

venmarcesAuthor Commented:
getting error on

           
        db.Execute SQL, dbFailOnError

Object Required
Rey Obrero (Capricorn1)Commented:
try the codes i posted
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.