tables from different databases but in single query

L

leon

Hi all,
i am aware of the following code to use single query in selecting from
multiple databases at once:

SELECT PERSON.NAME DEPARTMENT.LOCATION
FROM

PERSON IN 'C:\DB1.MDB',
DEPARTMENT IN 'C:\DB2.MDB'

WHERE

PERSON.DEPT = DEPARTMENT.ID

my question is this:

is it possible to NOT use absolute path when specifying the databases,
but rather relative paths - SPECIFICALLY if such databases are to be
configured via ODBC Data connection (DSN in control panel in windows).

in other words, what i want is to specify the odbc configured name for
each of two databases rather than an absolute file path.

Kind regards,
Leon.
 
G

Graham R Seach

Leon,

Why not just link the tables from the external database(s)? Then you can
refer to them directly.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
G

GB

If the files are located still in some semblance of a PC path, for example if
the database is in the following path

C:\temp\Temp1\Temp2\filename1

and the referenced files are say

up two directories, then down one like

C:\temp\Temp1_1\filename2

you could should be able to set the path by using
'..\..\Temp1_1\filename2'
Or at worst case, get the path of the database, and set it to some variable
then append the above statement to the path, and it would refer the program
to the desired non-specific location.
 
L

leon

Because from what I understand, the links are specified in ABSOLUTE path
(i.e. i could not link to external tables with relative paths). the
whole point of my question was to provide RELATIVE addressing and
it would appear that the most "relative" or flexible way to specify a
database is via odbc managed entry - this way system admins can change
the location of databases to their hearts' content.
 
L

leon

GB said:
you could should be able to set the path by using
'..\..\Temp1_1\filename2'
Click to expand...

could or should or both?
Or at worst case, get the path of the database, and set it to some variable
then append the above statement to the path, and it would refer the program
to the desired non-specific location.
Click to expand...

that seems very non-elegant indeed...
 
D

Douglas J. Steele

It's not possible. (And, for what it's worth, you can only use IN to connect
to one external database at a time)

The most appropriate approach would probably be to have linked tables, as
Graham suggested. You can write VBA code to link (or relink) your tables
based on where the linked databases should be. It's simple to determine
where your current database is (CurrentProject.Path or from CurrentDb.Name).
Once you know that, you can derive where your external databases should be
for linking purposes.
 
M

Marshall Barton

leon said:
Hi all,
i am aware of the following code to use single query in selecting from
multiple databases at once:

SELECT PERSON.NAME DEPARTMENT.LOCATION
FROM

PERSON IN 'C:\DB1.MDB',
DEPARTMENT IN 'C:\DB2.MDB'

WHERE

PERSON.DEPT = DEPARTMENT.ID

my question is this:

is it possible to NOT use absolute path when specifying the databases,
but rather relative paths - SPECIFICALLY if such databases are to be
configured via ODBC Data connection (DSN in control panel in windows).

in other words, what i want is to specify the odbc configured name for
each of two databases rather than an absolute file path.
Click to expand...


I have (rarely) done something like that using code like
this:

Dim db As Database
Dim rs As Recordset
Dim strPath As String

strPath = CurrentProject.Path
ChDir strPath
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * " _
& "FROM [System Colors] " _
& "IN ""..\WUTemp\General Utilities.mdb""")
 
L

leon

Douglas said:
The most appropriate approach would probably be to have linked tables, as
Graham suggested. You can write VBA code to link (or relink) your tables
Click to expand...

ah - i see. sorry for not being clear - just because i am using Ms
Access - this does not mean that I am using VB... :)

In fact I MUST use Java (JDBC) to connect to multiple Ms Access
databases and query/join two tables from two separated databases... the
whole thing is rather more likely to be setup by marking lecturer (it is
a uni assignment) as an odbc driver entry (thus the original question of
specifying the databases via odbc "coordinates")... but since you have
suggested that one can only select one external database at a time in
one's query... then i probably should be lookin at another approach
completely... (i presume in linking tables, one has no option to link to
an ODBC configured database - i.e. do not provide file paths, but rather
odbc setup name for a given database?)

Kind regards,
Leon.
 
D

Dirk Goldgar

leon said:
ah - i see. sorry for not being clear - just because i am using Ms
Access - this does not mean that I am using VB... :)

In fact I MUST use Java (JDBC) to connect to multiple Ms Access
databases and query/join two tables from two separated databases...
the whole thing is rather more likely to be setup by marking lecturer
(it is a uni assignment) as an odbc driver entry (thus the original
question of specifying the databases via odbc "coordinates")... but
since you have suggested that one can only select one external
database at a time in one's query... then i probably should be lookin
at another approach completely... (i presume in linking tables, one
has no option to link to an ODBC configured database - i.e. do not
provide file paths, but rather odbc setup name for a given database?)
Click to expand...

It sounds to me like you're *not* using Access; rather, you're using
Jet databases (.mdb files), but not actually using Access, the
application.

Here's a thought -- how about creating a separate .mdb file with linked
tables to the tables that are actually stored in the *other* .mdb files?
Then (I think) your Java code could connect to this linking database and
run queries on the linked tables in that database.
 
L

leon

Dirk said:
It sounds to me like you're *not* using Access; rather, you're using
Jet databases (.mdb files), but not actually using Access, the
application.
Click to expand...

oops - you are right - sound to me like i don't know what i am talking
about :)
Here's a thought -- how about creating a separate .mdb file with linked
tables to the tables that are actually stored in the *other* .mdb files?
Click to expand...

i've decided against that option:
a) links to tables from other databases are always saved with absolute
path (which is extremely bad - my situation is to write Java code to
access distributed MS databases, the location of those may not be on the
same computer)
b) the databases should (i.e. expected to) really be interfaced via ODBC
or JDBC information (i.e. not an explicit file path)...

That's ok anyways - i just wanted to make sure that there is no other
option but to code a lot of this at the application level (which is what
i am going have to do by the looks of things...)

Kind regards,
Leon.
 
Top