Dynamic tablename in query FROM clause using VBA

  • Thread starter Thread starter Michael Rolko
  • Start date Start date
M

Michael Rolko

In Access 2002 using Jet, I want the user to pick from a
(dynamic) list of tables (all with same column structure)
and then have ONE report dynamically run against a query
against the chosen table. I DO know how to dynamically set
the report source for the report (which is a query), but I
do not know how to dynamically set the tablename in the
FROM clause of the query underlying the report. That is, I
want the user to be able to create any number of tables
(of the same structure), and then pick one of the tables
in a form. The chosen table then dynamically gets
referenced in the underlying query. The user should not
have to manually create a query for each of the tables.
(The query is a join on the chosen table and some other
fixed tables.)

The desired architecture is:
A. 1 report uses 1 query that dynamically references the
chosen table, or
B. 1 report that uses a dynamically determined query as
record source, and that query (created in VBA) selects
from the previously created table. This approach would
require me to create a query in VBA for every table that
the user creates.

If Jet supported views, this problem would be a piece of
cake...

Maybe my approach is wrong, but any help would be
appreciated. The user insists on having multiple tables of
the same column structure, rather than having one table
contain all the data in all the tables, which would
greatly simplify things.

Thanks, Michael (Toronto)
 
Dear Michael:

I have seen variations of this question several times. The answer is
always the same.

It will work well if you put all the data into one table. Add just
one row, what I'll call "Source." The source of each row represents
which of your current, many tables this data came from. From this one
large table, you can simply filter by the Source column to get just
the rows from the specific Source you desire.

You can create this table by making a UNION query of all your tables,
with a literal value for each one identifying the new Source column.
In fact, for querying purposes, you can even just use this UNION query
the same way I just described using the combined table.

The difference in doing it this way is that you will be using the
database query capabilities in a way they are meant to work, instead
of struggling to get them to work in a way they weren't designed to
work. Dynamic table names are not a good option, although you can
write the SQL in code to do this if you prefer.

You have anticipated my response to some degree, but perhaps not the
UNION query approach. In any case, when a competent database designer
has an ignorant user who is making critical design decisions, this is
the sort of thing that is likely to happen. It would be like a
passenger telling the pilot of a 747 how to take a shortcut on the way
to Hawaii.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I have another option if you do not want to combine your tables. You said
all your tables have the exact structure. So create one more table (I'll
call it tblForReport) with the same structure. Base your report on this
table. Then from your form, populate this table based on the table chosen
from the list. You could just use a make table query and overwrite the
table over and over. You wouldn't have to worry about programming the
report and queries and things like that. This might be slow depending on
how much data you have, but if you have multiple tables with the same
structure, I am sssuming you are doing this to keep each table small. Then
speed shouldn't be a problem.

Kelvin
 
Back
Top