MS Access query with dynamic from statements

Question

Ok this is vexing me. I have a query I created with an in statement in the from clause. What I am looking to do is have a global variable populate that from statement. Example

Select *
Form query1 in <Global Variable filename>

What is going on is I link to a file that has hundreds of linked table and queries in it. My database only has a few select queries and link table to different database. I did not want to re-build all the queries and linked table is my database. The issue is the file with all the links changes name once a month. I just want to read a text file with the current name of the database in it so I do not have to keep changing my queries every time the database name changes. Also this has to a query since I have other queries using the externally linked query.

Answer 1

I have one suggestion, but its pretty kludgy.

Rewrite the query on the fly with VBA call

Private Sub update_qtest()
Dim db As Database
Dim qd As QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs("qtest")
    qd.SQL = "SELECT * from query1 in " & g_file_name
End Sub

As I said, it's kludgy, but I don't think there's a way to pass the from clause as a parameter.

Answer 2

So you have a query like this:

SELECT *
FROM query1 IN 'C:\somewhere\AnotherDb01.mdb';

Next month, you want to substitute AnotherDb02.mdb for AnotherDb01.mdb

However, the database engine will not let you do that substitution at the time you run the query. There is no way around that.

Think I would pull query1 into your working database. Create links to any of the tables in the external database it needs. Then each month, use the linked table manager to identify which external database the links point to.

Answer 3

Another way to do this would be to just use the same file name each month so you wouldn't have to change anything in your Access app at all. You could easily code copying the file to the standard name over top of the previous copy (you'd have to delete it before copying, of course), which would retain the history.