asked on

Querying across multipkle Access DB's

I'm developing a reporting application which must read two MS Access databases: one on a server (core data), the other on the users local drive (lookup tables). What I'd like to be able to do is perform queries that combine tables from *both* the databases WITHOUT resorting to using MS Access' approach to linking tables in different databases.

Has anybody found a way of doing this? I assume it's a matter of knowing the correct SQL syntax to use to add in a table from a different MDB...if such exists. (It used to be so easy to do this in the good-ol' Paradox days!!)

...any suggestions?

I can think of plenty of work-arounds (breaking queries into a sequence of steps, then moving blocks of temporary data from a TAdoQuery to a temp table in the local database, etc etc etc) but before I resign myself to such a less-than-satisfactory approach, I'd be interested to know if what I'm wanting to do can be done!

Thanks!

CrazyOne

I am not sure how well this works for Access. The following comes form the Local SQL help file.

Heterogeneous joins

Joins two tables from different databases.

SELECT column_list

FROM ":database_reference:table_reference", ":database_reference:table_reference" [,":database_reference:table_reference"...]

WHERE predicate [AND predicate...]

Description

Use a heterogeneous join to join two tables that reside in different databases. The joined tables may be of different types (like dBASE to Paradox or Paradox to InterBase), but you can only join tables whose database types are accessible through the BDE (local, ODBC, or SQL Links). A hetergeneous join may be any of the joins supported by local SQL. The difference is in the syntax for the table reference: the database containing each table is specified in the table reference, surrounded by colons and the whole reference enclosed in quotation marks. The database specified as part of the table reference may be a drive and directory reference (for local tables) or a BDE alias.

SELECT *
FROM ":DBDEMOS:Customer.db" C, ":BCDEMOS:Orders.db" O
WHERE (C.CustNo = O.CustNo)

The Crazy One

ASKER CERTIFIED SOLUTION

rmaranhao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DavidLeeding

ASKER

Thanks rmaranhao,

This has got me a bit closer...I hadn't previously tried specifying an alias for tables (ie 'A' and 'B' in your example), and this seems to make a difference:

SELECT a.ClientCode, a.ReferDate, c.CountryName
FROM TblClients a, c:\data\ccdc\lookups.mdb.LuCountry c
WHERE (c.Id = a.BirthCountry);

fails with the message "Not a valid password" because lookups.mdb is also protected...this is actually a lot more promising result that previously! But I can't figure out the syntax for passing a password. I tried:

SELECT a.ClientCode, a.ReferDate, c.CountryName
FROM TblClients a, c:\data\ccdc\lookups.mdb.LuCountry c [;PWD=SecretPassword]
WHERE (c.Id = a.BirthCountry);

but no luck. Any ideas? (If not, I'll close this question and post as a separate question)

David

CrazyOne

I am using DearSofts ADO component and it has a DMaster component which has user name and password paramaters as part of the Connenction property. Is there anything like the DMaster with ADO components you are using?

DavidLeeding

ASKER

Hi CrazyOne

I'm using the standard ADO components that come with Delphi 6 (and am not a position to purchase an alternate ADO components <sigh>).

The TAdoConnection component does provide an opportunity to assign username/password paramater...but only for a single database per component. Hence my dilema of trying to figure out how I can run queries across two (or more) databases, each connected via a different TadoConnection.

CrazyOne

Hmmm yeah I didn't think about that. Bummer

BTW DearSoft is free but I don't think it is any better then the ones that come with D6.