I need to create a make-table query. The table will be created in another database. I can enter destination database when I set properties of the query in design view. But since the destination database may change each time(users can choose), how can i set this property of query in Access VBA.
Your help will be highly appreciated!

You will have to use a variable and pass the variable using Code.

I wrote this to insert a string into a table on the fly using a users input. You can adapt it for what you need. Or at least get some ideas as to the direction you need to go:

Option Compare Database
Option Explicit

Dim strResponse As String
Dim strSQL As String

strResponse = InputBox("New table name?")

strSql = "INSERT INTO TblAccessDate ([Date],[Time],[TableName])" & _
         " VALUES (#" & CStr(Date) & "#, #" & CStr(Time) "#, '" & strSaveAs & "');"

That last response seems to have a few mystery variables thrown in. Try this:

    ' These 2 variables get their values from perhaps a form or an inputbox function (you decide)
    Dim Tablename as String
    Dim DatabasePath as String

    Dim qs as String

    qs = "SELECT <and some other stuff>"

    ' The key piece of code
    qs = qs & " FROM " & Tablename " AS T IN '" & DatabasePath & "'"

    qs = qs & " WHERE <and other magic>;"

You don't need to give the table an alias but it just seems like the thing to do.


-Phil-