I have DAO connections to two databases dbNew and dbOld. I want to copy data from a table in dbOld to a table in dbNew. Currently I have done it by creating two recordsets and looping through them.

I wonder if it would be more efficient to use the INSERT INTO statement? But I cannot get it to work. I can do it from table to a table in the same database but not from a table in one database to a table in another database. Can anybody give me a clue how to start please.

Many thanks.

Clive Astley

__________________
Clive Astley

Typically, when you want to reference tables in dbnew from dbold or vice versa, you must "link" the databases. For example, Oracle provides functionality to LINK two databases as follows: Let's assume we want to link dbNew to dbOld...

Assuming we are logged onto dbOld...

CREATE DATABASE LINK linkname
  CONNECT TO username IDENTIFIED BY password
  USING dbNew

(This assumes that dbNew is the name the database is known by.)


Once this link has been established, then all tables/views/data of dbNew are now available in dbOld, and you reference them as such:

SELECT fieldname FROM tablename@linkname;

the "@" followed by linkname that you provide tells the database that you want to access the linked database.

Once you establish this sort of linking, you can then easily implement inserts from one table to another via SQL.

Other database engines apply similar principles.

Many thanks Cliff.

Clive Astley