Use a from to select the table for the query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an unspecified number of tables that will all need the same bulk
update query run. They have the same field names to update, so I just need to
be able to change the corresponding tables.

I'd like to be able to select the table in a form and then have the query
update fields of that table (without having to build a separate query for
each table).

I was thinking of using an "AfterUpdate" event on the form field to modify
the query, pointing it to the right table. Then I can put a command button to
run the query. The problem is that I don't know how to make the query point
to the right table.

Any suggestions?
 
What is the structure of your tables? What are you changing?

I fear you have a poorly designed database if you are having to go back and
update field names in multiple tables. Each table should be unique, so I
can't imagine an update that would change field names in multiple tables.

If your field names contains a year, then your structure is flawed.

If you have multiple tables with the same structure and each table holds a
different subset of the client/product/people/etc. then your structure is
flawed.
 
A little tricky, but can be done. There are two ways I can think of that are
very similar in nature. One is to read the SQL property of a stored query,
modify it, and save it back to the query. The problem with this method is
being sure the query hasn't changed due to someone playing with it or because
an error prevented completion of the process. So, my suggestion would be to
first create the query using one of your tables. Flip into SQL view, copy
the SQL statement, and past it into your VBA code. Then you can use the
Replace function to change tables and run it again. I will use a simple
example here:

Dim strSQL as String
Dim dbf As Database

strSQL = "UPDATE BillCurrMonth3345 " & _
"LEFT JOIN Actual_res_export " & _
"ON BillCurrMonth3345.EmployeeId = Actual_res_export.EmployeeId " & _
"SET BillCurrMonth3345.Homeroom = [Actual_res_export].[Homeroom] " & _
"WHERE (((BillCurrMonth3345.EmployeeId)= " & _
"[Actual_res_export].[EmployeeID]));"

Set dbf = CurrentDb
dbfExecute(strSQL), dbFailOnError

'Do the next table
strSQL = Replace(strSQL,"BillCurrMonth3345", "BillCurrMonth3499")
dbfExecute(strSQL), dbFailOnError

etc, etc, etc (as the king said)
 
Back
Top