How can I add a table from a different DB to excel Query 2002?

[Guest]

Hi Folks! Spent much time looking for a solution. I need to add a table to an
existing excel query, but it is coming from a different database.

I have a foxpro database call it xx.dbf the table I need is not witihin the
same container, but it is related. Within my current query how can I add a
new table from another DB. I have a named a DSN for the other data source,
but cannot seem to find the correct syntax, or am I asking excel to do too
much.

Any words of wisdom would be greatly appreciated.

Thanks

CJ

 

[Guest]

You may be asking too much from MSQuery. I know of no way to have more than
one data source (DSN) open in MSQuery and, in general, this is a tricky
feature to support so a simple tool like MSQuery may not implement the
capability to do it.

3 possible ways to work around this:
1) bring the 2nd table in via a 2nd query in Excel and use Excel formulas to
relate the two sets of data - if possible.
2) Code a routine that uses ADO, open the two connections to read and
combine the info from your two data sources and output it to the worksheet.
3) Use something like MS Access as an intermediate step: link the two tables
in Access and write your query there. You may need to make a temporary table
with the results since I am not sure if MSQuery can read a linked table
directly out of Access. You can even automate this part through an Excel VBA
sub.

None of these are particularly easy, but they are all I can think of to
solve your problem - maybe someone else knows more and has a better solution.

 

[DM Unseen]

CJ,

I agree with K Dales, MSQuery will not really help there.
Succesfully combining data from different databases within a query
depends on the actual driver used.
For e.g. the Jet engine (which is used for everything from
excel/access/dbase and more) the following trick works:

SELECT tblAddresses.*
FROM tblPeople INNER JOIN [C:\Data.mdb].tblAddresses ON
tblPeople.PersonID =
tblAddresses.PersonID

I would advise experimenting with drivers and synatx to see if you can
get it working for dbase/foxpro files.

Dm Unseen