Substituting a variable as table name in a Saved query

[Andy]

A little unusual but I need to build a query using a dynamic table name. I
can do this in VBA code by building a query string and then calling RunSql
command e.g. :-

Dim tblName as string
Dim strSQL as string

tblName = "tblA"
strSQL = "Select [" & tblName & "].* From [" & tblName & "]"
docmd.RunSQL strSQL
etc.

Fine - but that is in VBA. Do you know if it is possible to create a Saved
query (these are usually more efficient) which also has the table name
substituted from, say, a Public variable?

If you save a query such as :-

Select [pubTN].* From [pubTN];

Then when you run this saved query, the public variable, pubTN (containing
the real table name), is not substituted and the query fails because table
pubTN does not exist.

Any ideas?

 

[Rick Brandt]

A little unusual but I need to build a query using a dynamic table
name. I can do this in VBA code by building a query string and then
calling RunSql command e.g. :-

Dim tblName as string
Dim strSQL as string

tblName = "tblA"
strSQL = "Select [" & tblName & "].* From [" & tblName & "]"
docmd.RunSQL strSQL
etc.

Fine - but that is in VBA. Do you know if it is possible to create a
Saved query (these are usually more efficient) which also has the
table name substituted from, say, a Public variable?

If you save a query such as :-

Select [pubTN].* From [pubTN];

Then when you run this saved query, the public variable, pubTN
(containing the real table name), is not substituted and the query
fails because table pubTN does not exist.

Any ideas?

As far as I know, it can't be done. Dynamic stuff in a saved query can only
refer to field criteria.