Solved

Querying across multipkle Access DB's

Posted on 2002-07-25
6
185 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

816 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

11 Experts available now in Live!

Get 1:1 Help Now