No "shortcuts", but I think some clarifying comments might
be helpful here.
First, **every** operation that sets/retrieves data in a
relational database (not just Access) uses an SQL statement.
This is true regardless of what user interface you use to
specify the operation. Access's query design grid and its
"properties" is just a convenience that Access translates
into the SQL statement that does the work.
If you create a query using the query design grid, then you
can switch to SQL view to see the constructed statement.
When you specify the source database "property", it is used
in the IN phrase in the FROM clause and (since the other
database may be on another machine run from a database
engine that could conceivably be on yet a third machine) it
must be a full path.
To do what you want, you can not use a preconstructed SQL
statement because the query may need a different IN phrase
each time you execute it.
What all that adds up to is that you need to use some VBA
code to construct the SQL statement each time you want to
run the query. This is not a particularly complicated thing
to code.
If you are just using the "other" database as a backend,
then you only need to relink to the tables (modify the
linked tables' Connect property). Code to do this is
readily available on the web.
As for using the code database's path to construct the path
to the tables' database, this is readily available by using
the code database's CurrentProject.Path property or parsing
the CurrentDb.Name property.
If you'll explain where/how you need to use the "other"
database, someone will almost certainly be able to help you
work out the code to do it.
--
Marsh
MVP [MS Access]
Well...I *figured* that would work. But that's just what I *don't* *want* to
do. I often take my db home (or move it over my VPN). The pathname is
different. I *was* hoping that I could reference the directory in which the
"code" db resides, and go from there. But it would seem Access has its *own*
ideas.
*HAS* to be the full pathname, huh? No "shortcuts"?
Tom said:
What do I have to enter in the "Another Database" field, besides the actual db
name, to get Access [2000] to accept it? It keeps telling me that "C:\Documents
And Settings..." isn't valid. Well of *course* it isn't valid. The db I'm
working with is in "D:\Projects\AS400\Utility Billing". Both the code db, which
is the one giving me the grief, and the data db are in the same place.
Marshall said:
Enter the full path (including the file name).
