It sounds like a design problem, but you problem can normally be solved by a
temper table which it built via vba then queries are run against the temp
table. If you go down that route consider multi-user problems with the temp
table.

I urge you to review the structure of your data as there maybe a much
simpler/faster solution.

Hi ats,

One method I would use is to save a query (say "qryExtDB"),
(it doesn't matter initially what it's SQL is).

In your VBA, construct the SQL with "IN somepath"

Then change the SQL for the stored query with a public
function in a module, like the following adapted from a
a routine once provided by Duane.

Public Function ChangeSQL(pstrQueryName As String, pstrSQL As String)
On Error GoTo Err_ChangeSQL
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef

   Set db = CurrentDb
   Set qd = db.QueryDefs(pstrQueryName)
   qd.SQL = pstrSQL

   qd.Close
   db.Close

Exit_ChangeSQL:
   Set qd = Nothing
   Set db = Nothing
   Exit Function
Err_ChangeSQL:
   MsgBox Err.Description
   Resume Exit_ChangeSQL

End Function

[NOTE: need to make sure you reference DAO library]

=======================
So...all your other queries are based on "qryExtDB"
=======================
and in your VBA routine you do something like:

'{not password protected}
strSQL = "SELECT * FROM " & pTable _
           & " IN '" & pPath & "';"
'{or password protected}
'strSQL = "SELECT * FROM pTable _
'     & " IN '' [MS Access;PWD=" & pPWD _
'     & ";DATABASE=" & pPath & "];"

'use the function to change SQL of stored query
ChangeSQL("qryExtDB", strSQL)

'or just do it w/o function (again using DAO)
CurrentDb.QueryDefs("qryExtDB").SQL = strSQL

Does that help (or just confuse things)?

gary