Modifying Make Table Query in code

  • Thread starter Thread starter Norbert
  • Start date Start date
N

Norbert

Please.
I have about 15 Make table queries that are executed when
user selects a month date in a dropdown box.

What I'd like to do is change the source table used in
these queries thru code without having to do away with
these queries. I originally executed the MakeTable thru
SQL strings in code but it proved too much maintenance.

For example, all queries use a table for September 2003
data, when user selects lets say December 2002 then the
code I want changes the source table. The reason its setup
this way is because the backend source is huge and
breaking it up to months increases the performance
dramatically.

thank you!
Norbert Beliso
 
Please.
I have about 15 Make table queries that are executed when
user selects a month date in a dropdown box.

What I'd like to do is change the source table used in
these queries thru code without having to do away with
these queries. I originally executed the MakeTable thru
SQL strings in code but it proved too much maintenance.

For example, all queries use a table for September 2003
data, when user selects lets say December 2002 then the
code I want changes the source table. The reason its setup
this way is because the backend source is huge and
breaking it up to months increases the performance
dramatically.
Click to expand...

The only way to do this is to dynamically create the SQL on the fly in
the afterupdate event of the combo box.

Have you carefully examined the potential of thoroughly indexing the
huge table? I *intensely* dislike using maketable queries (they bloat
the database something awful) and use them only after exhausting all
the other options.
 
Well, you can read the SQL from a querydef like so:
Dim varQDef As QueryDef
Dim strSQL As String
Set varQDef = CurrentDb.QueryDefs("YourMakeTable")
strSQL = varQDef.SQL

Now the interesting part is how you replace the old table name with the new
table name. That depends a lot on what the actual query looks like. At the
very least, you can use an InStr function to find FROM. If you made it a
practice to use brackets around the table name, you could then search from
there for those.

Once you find the table name and replace it with the new, you can set the
SQL property of the Querydef to the value of strSQL

varQDef.SQL = strSQL
 
Back
Top