How do I change table name in Query through Form?

[francesco]

My environment:
Query1 (source table changes every week - same structure-, SQL does not
change).
Report1 (source record always Query1).
Currently, I am manually replacing the table name in Query1, SQL view and
then my users can run Report1.
Is there a way to create a Form, so my users can just type in the new table
name, replacing the previous table in Query1?
After doing that, then users will have a "Print Report1" button with the
updated Query1.
Thank you very much for your help.

 

[Douglas J. Steele]

You can try something like:

Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

Set qdfCurr = CurrentDb.QueryDefs("Query1")
strSQL = qdfCurr.SQL
strSQL = Replace(strSQL, "OldTableName", "NewTableName")
qdfCurr = strSQL
qdfCurr.Close
Set qdfCurr = Nothing

 

[francesco]

Doug,
Thank you very much for the code provided. This, however, doesn't do what
I'm looking for.
I just had a thought to accomplish my goal via a different approach.
(All tables always have the same structure, but different data).
I create a new "master" table, Table1.
Query1 will always have as source record Table1, so Query1 and Report1 will
remain untouched.
Create a form with a dropdown.
Every time a new table is created, it will be automatically added to the
dropdown.
Selecting a table (ex: tblData1) from the dropdown, data from tblData1 will
be copied to master Table1.
Users will just press the Print Report1 button.
Upon closing Report1, data from master Table1 will be deleted.
Easy for me to say, but how do I implement it?

Thank you very much for your time.

 

[francesco]

New data from different sources, new table. Must keep existing tables; may
not overwrite them.
If you have suggestions or a better solution, I would greatly appreciate it.

Thank you for your time.

Francesco