How do I update multiple queries simultaneously?

[mbh]

I have an enormous database that I inherited from a predecessor. Each month,
20 queries need to be updated with new tables. Rather than going into each
query and updating each SQL statement separately, is there a way to do this
with a macro or something? Thanks.

 

[Allen Browne]

It is possible to write code where you build the SQL statement
programmatically (as a string variable), and then assign it to the SQL
property of the QueryDef like this:
Dim db As DAO.QueryDef
Dim strSql As String
Set db = CurrentDb()
strSql = "SELECT ...
db.QueryDefs("Query1").SQL = strSql

However, there's a good chance that the problem is with the database
structure you are using. If you have a different table for each month, you
need to redesign the structure so you have one table for all months. Add a
date field to indicate which month the record is for (typically the first of
the month.)

 

[mbh]

I like the idea of changing the table structure. The problem I am having is
that I get the data for the table each month--not all at one time. Should I
just do an append query to update the table? Thanks for your help.

 

[Allen Browne]

Yes: using an Append query to add the new data to your real table sounds
like an execellent approach.