how to dynamically refer to a table in a query

[lmid]

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

I want to make a query that will return records ONLY from the tables 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.

 

[dhookom]

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.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lmid]

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

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

Do you have any other ideas?

 

[dhookom]

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lmid]

Thank you! This looks like it will do the trick!