Retrieving data from two databases in different locations

Posted on 2009-04-30
Medium Priority
Last Modified: 2012-05-06
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?
Question by:EGormly
  • 4
  • 3

Expert Comment

ID: 24270924
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.

Author Comment

ID: 24271631
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.


Expert Comment

ID: 24272099
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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 24272108
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"

Author Comment

ID: 24272776
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.

Accepted Solution

EverLearningCodeMonkey earned 2000 total points
ID: 24273347
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:  http://www.devguru.com/Technologies/ado/QuickRef/recordset_getrows.html

Also, just found this, you might want to check out this thread - building a disconnected recordset and adding rows to it manually:  http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20267399.html

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.

Author Closing Comment

ID: 31576465
thank you.. helps me understand it better.

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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