Easy way to copy data from from data source to another?

[Maury Markowitz]

I have two databases I work with, one in SQL Server and another in
Pervasive. There are numerous times where I would like to run a query
comparing data in the two systems. This is easy enough to do from a
MDB, but our main application is an ADP.

Can anyone offer suggestions on easy ways to handle this situation?
For instance, consider this simple example: I need to know what
inventory items have been entered into one system but not the other.
Under the MDB I simply link to both tables and then issue a SELECT
that JOINs the two. Presto. But how to do this in an ADP?

One solution would be to have an MDB that simply links to every table
on either side, and then call _that_ from the ADP. Does anyone have
any examples of this? Does this require the entire MDB to be loaded
up? Or is this relatively "cheap"?

Another solution would be to use a linked server under SQL Server to
make the PSQL tables appear on the SQL system. However, I am not sure
if this makes cross-system queries possible. I have not seen any
examples of this anyway, can anyone point to some?

Maury

 

[Arvin Meyer MVP]

To my knowledge, ADPs only handle SQL-Server files, but I cannot be sure of
that because I've never tried to link files from multiple ODBC sources to
one. I do know that an ADP cannot see or link to any MDB file.

That said, I believe that an MDB is your only solution. As far as I know, it
is the only way to connect multiple datasources in a single query.

 

[Maury Markowitz]

To my knowledge, ADPs only handle SQL-Server files

Directly yes, but I talk to all sorts of things via ADODB in VBA. I
guess the question is better phrased "is there a way to have ADODB
talk to more than one data source in a single query"?

That said, I believe that an MDB is your only solution. As far as I know,it
is the only way to connect multiple datasources in a single query.

So then is there an easy way to use the MDB as a data source from ODBC/
ADODB?

Maury

 

[Arvin Meyer MVP]

To my knowledge, ADPs only handle SQL-Server files

Directly yes, but I talk to all sorts of things via ADODB in VBA. I
guess the question is better phrased "is there a way to have ADODB
talk to more than one data source in a single query"?

That said, I believe that an MDB is your only solution. As far as I know,
it
is the only way to connect multiple datasources in a single query.

So then is there an easy way to use the MDB as a data source from ODBC/
ADODB?


No. It can't be done. There are ODBC drivers for mdb's but only from a
connector to a front-end like a FoxPro database client or a Delphi client. I
am unaware of any other front end that can connect multiple different data
sources. I believe that's why it was originally named Access.