Query a table in another access database in the same directory

  • Thread starter Thread starter ats
  • Start date Start date
A

ats

Trying to query a table in another access database in the same
directory, but Access defaults to the User's logged in directory
instead of the current database's directory.

I've tried to construct the query by assembling the file, name but I
get a syntax error.

Example:

SELECT name
FROM tbName
IN
Mid(currentdb.name,1,Len(currentdb.name)-Len(Dir(currentdb.name)))&"dbNames.mdb"

The text after the "IN" constructs the full filename with path. The
function "currentdb.name" has the complete path & filename of the
current database, and I'm just stripping it off and attaching it to the
desired database name.
 
ats said:
Trying to query a table in another access database in the same
directory, but Access defaults to the User's logged in directory
instead of the current database's directory.

I've tried to construct the query by assembling the file, name but I
get a syntax error.

Example:

SELECT name
FROM tbName
IN
Mid(currentdb.name,1,Len(currentdb.name)-Len(Dir(currentdb.name)))&"dbNames.mdb"

The text after the "IN" constructs the full filename with path. The
function "currentdb.name" has the complete path & filename of the
current database, and I'm just stripping it off and attaching it to the
desired database name.
Click to expand...


When asking a question about a coding or syntax error,
please post a Copy/Paste of the code so we can see exactly
what you're working with.

If your query above is in the query design window, then the
problem is that you can not use an expression for the path.

If you're constructing the SQL statement in a VBA procedure,
then you need to make sure the path is enclosed in quotes:

strSQL = "SELECT name " & _
"FROM tbName " _
"IN """ & Mid(currentdb.name, 1, _
Len(currentdb.name) - Len(Dir(currentdb.name))) _
& "dbNames.mdb"""
 
Back
Top