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

[Guest]

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

 

[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.

 

[Guest]

This will be done on a regular basis. So I'm looking for VB code.

 

[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.

 

[Guest]

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.

 

[Douglas J Steele]

Sorry about that: wasn't thinking.

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