Append data from a table in DB1 to a table in DB2

J

jsccorps

Need VB code to take all of the data from a table in Database1 and append
this data to a table in Database2
 
D

David C. Holley

Is this something that needs to occur on a regular basis or are you just
doing a 1 time copy? If its 1 time, then import the data using the
FILE>Get External Data(option name?) Other wise, in 1 of the DB's create
a link to the other DB and then run an append query selecting the
records from 1 table to be appended to the other. I am, of course,
assuming that the table names aren't identical as that might cause some
issues when you create the link, however I've never tried this specific
scenario with linked tables.
 
D

Douglas J Steele

Assuming you're running this from DB1, you can use:

SELECT * INTO MyTable IN DB2.mdb FROM MyTable

Alternatively, create a linked table, as David suggested.
 
J

jsccorps

Clarification: The table that I am appending to is an existing table (that
has relationships with other tables). Seems that the "SELECT * INTO"
statement creates a new table - which is not what I need.
 
D

Douglas J Steele

Sorry about that: wasn't thinking.

INSERT INTO MyTable IN DB2.MDB (field1, field2, ...)
SELECT field1, field2, ...
FROM MyTable
 
Top