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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jsccorps - 06 Dec 2005 02:11 GMT

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

Reply to this Message

David C. Holley - 06 Dec 2005 02:33 GMT

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.

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

Reply to this Message

jsccorps - 06 Dec 2005 02:49 GMT

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

> 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
[quoted text clipped - 7 lines]
> > Need VB code to take all of the data from a table in Database1 and append
> > this data to a table in Database2

Reply to this Message

Douglas J Steele - 06 Dec 2005 12:23 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> This will be done on a regular basis. So I'm looking for VB code.
>
[quoted text clipped - 9 lines]
> > > Need VB code to take all of the data from a table in Database1 and append
> > > this data to a table in Database2

Reply to this Message

jsccorps - 06 Dec 2005 15:56 GMT

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.

> Assuming you're running this from DB1, you can use:
>
[quoted text clipped - 16 lines]
> append
> > > > this data to a table in Database2

Reply to this Message

Douglas J Steele - 06 Dec 2005 21:23 GMT

Sorry about that: wasn't thinking.

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Clarification: The table that I am appending to is an existing table (that
> has relationships with other tables). Seems that the "SELECT * INTO"
[quoted text clipped - 20 lines]
> > append
> > > > > this data to a table in Database2

Reply to this Message