Improve company productivity with a Business Account.Sign Up

x
?
Solved

Querying across multipkle Access DB's

Posted on 2002-07-25
6
Medium Priority
?
199 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 600 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…

580 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