Solved

Querying across multipkle Access DB's

Posted on 2002-07-25
6
182 Views
Last Modified: 2010-04-04

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!
0
Comment
Question by:DavidLeeding
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7179403
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
0
 
LVL 5

Accepted Solution

by:
rmaranhao earned 200 total points
ID: 7180295
In Access:

select *
from TableName A, filename.tablename B

where A.Key = B.Key


This should work.

PS.: I tested with files in the same dir, don't know if it will take network names.

rmaranhao
0
 
LVL 1

Author Comment

by:DavidLeeding
ID: 7184111
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
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 44

Expert Comment

by:CrazyOne
ID: 7184142
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?
0
 
LVL 1

Author Comment

by:DavidLeeding
ID: 7184223
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.
0
 
LVL 44

Expert Comment

by:CrazyOne
ID: 7184267
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now