Retrieving data from two databases in different locations

I have two databases:


(Yes, they are really old paradox dbf databases...)

Both of them hold the same column names but different data the column names needed are:
Acco, Invoice, Date, Total, Disc

I need to merge both sets of data into one select statement so I can display the results.

I can find how to join two tables within the same connection (database) but I cannot find how to join two different databases entirely.  I need to use a unique statement to open each database . How do I merge them?
Who is Participating?
EverLearningCodeMonkeyConnect With a Mentor Commented:
Hi EGormly,

I sympathize, it's not efficient but it'll probably get the job done - which is better than it not happening at all.  On the upside you'll might be able to use this as a good reason to make that final breakaway from Paradox.

Back on topic, you might want to try the array option as well and see which one has better performance.

ADO Recordsets have a method called GetRows() that would be helpful to this end, it'll return a 2D array of everything in the recordset:

Also, just found this, you might want to check out this thread - building a disconnected recordset and adding rows to it manually:

Perhaps some combination of these methods will help solve your problem.  You might be able to leverage the filtering and sorting abilities of a disconnected recordset to simulate the desired result.
Hi EGormly,

I think you might want to look at importing these databases into something like MySQL or SQL Server or something along those lines - that way you can reference the tables in each database by prefixing them with the database name - and with the one query get a combined result.
EGormlyAuthor Commented:
HI EverLearningCodeMonkey:

Thank you for the suggestion but that is not what I am looking for.
I do not have the option of using SQL Server or any other outside or otherwise different database. If I did, I certainly wouldn't be using the paradox databases in the first place,  believe me, I'd love to dump them.

As I noted in my question,  I already know how to get data from different tables in the same database (or linked in Acces, SQL etc..)

 I actually DO need to know how to retrieve data from two databases in different locations.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Hi EGormly,

From what I've been reading your options tend to boil down to these 2 basic choices:
  • Import the data from one database into the other, (from both into a third database), and then use a union to join it all together
  • Grab your recordsets from each database, convert them to arrays and merge them together from there
I would think how feasible these options are largely depends on what language you're using and what technologies you have at your disposal for data retrieval and manipulation.

Incidentally, what are you using to work with these blasts from the past?
That first option should read - "Import the data from one database into the other, (or from both databases into a third database), and then use a union to join it all together"
EGormlyAuthor Commented:
I am using vbscript because it is all I have access to and are permitted to use.
If I cant connect to two different databases I guess I will be forced to create two connections and fill a third temp database.  That stinks and is not an efficient option at all.
EGormlyAuthor Commented:
thank you.. helps me understand it better.
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.

All Courses

From novice to tech pro — start learning today.