Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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!
0
DavidLeeding
Asked:
DavidLeeding
  • 3
  • 2
1 Solution
 
CrazyOneCommented:
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
 
rmaranhaoCommented:
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
 
DavidLeedingAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CrazyOneCommented:
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
 
DavidLeedingAuthor Commented:
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
 
CrazyOneCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now