how to dynamically refer to a table in a query

[LMid]

I have the names of several tables in a table called "MyTables", eac
table name as its own record, together with a checkbox

I want to make a query that will return records ONLY from the table
where that table's record in MyTables is checked

Is this even possible? How can I go about doing this

Thank you in advance for your help

 

[Duane Hookom]

As per my answer to this question in www.tek-tips.com

You combine all you tables into a single recordset using a union query. Make
sure you derive a value that matches the value in MyTables.

SELECT "tblJan" as TableName, *
FROM tblJan
UNION ALL
SELECT "tblFeb", *
FROM tblFeb
UNION ALL
---etc---;

I always take the opportunity to question why a person would use multiple
tables when a single table with an extra field might work better.

 

[LMid]

Thank you Duane, but that won't work in this situation. I don'
always want to return records from all the tables. I don't even wan
to look in the tables that were not checked off in MyTables. I
doesn't matter if after quering them no results would be returned,
can' t look in them at all

The problem is that I will be using linked tables, and not all of the
will be able to reach the data source at all times. So I can onl
look in the checked tables

Do you have any other ideas

 

[Duane Hookom]

As per my response in www.tek-tips.com

If this is the case, you may need to use code to change the SQL property of
a saved query. This code is just an example. I would create a form with a
list box of tables that would allow the user to select the table. Then code
would run to change the SQL property of a saved query.

Dim strSQL as String
Dim strTableName as String
strTableName = InputBox("Enter table name")
strSQL = "SELECT * FROM [" & strTableName & "]"
CurrentDb.QueryDefs("NameOfSavedQuery").SQL = strSQL