Merge data into existing table 2

[vamoose]

I am using Access 2000. What I have is one Access database that is my "Master" with one table. I would like to insert data into this "Master" table from another Access 2000 database and not alter the data or structure in the destination table. I am currently trying to use:

DoCmd.TransferDatabase acExport, "Microsoft Access", [My Server Filename], acTable, "SAP_Import", "ABS_Daily", False

and this works but the entire "Master" table and data is replaced with the new incomming file. Basically I want to copy from 1 and Paste Append into the other. Any suggestions, Thank you.

 

[jrbarnett]

You need to use an append query, which runs an SQL insert statement to transfer the data.

Set up in your master table a new linked table to the master table in the other database.

Create an append query that selects all data from the new table and inserts it into the existing table. You will need to check that primary keys won't be duplicated, but I can't really help with that.

John


John

 

[vamoose]

Maybe I can explain this a little better. I would like to use only VBA to accomplish this task. The first database containing 1 table (table1) is currently open. I would like to copy the contents of table1 to another database located on the same computer, in the same folder, and with the same table1 name . I really don't want to use linked tables if at all possible to do this. Thank you

 

[Ed2020]

I really don't want to use linked tables if at all possible to do this.

Why not? It's by far the simplest method of doing this and can be fully automated in VBA.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PWise]

run this code

mydb.execute ("INSERT INTO Table1( [Field1], Field2, [Field3] )
SELECT [Field1], Field2, [Field3] FROM Tablename IN 'c:\foldername\databasename.mdb")

 

[PWise]

Sorry Should be

dim mydb as database 
set mydb=currentdb
mydb.execute ("INSERT INTO Table1( [Field1], Field2, [Field3] )
SELECT [Field1], Field2, [Field3] FROM Tablename IN 'c:\foldername\databasename.mdb")

 

[vamoose]

Dim mydb As Database: Set mydb=CurrentDB
mydb.Execute ("INSERT INTO Table1( [Field1], [Field2], [Field3] )
SELECT [Field1], [Field2], [Field3] FROM Tablename IN 'C:\foldername\databasename.mdb'")

Yes, this is what I trying to accomplish, but I think it might backwards ? I want to Select from CurrentDB and Insert Into C:\foldername\databasename.mdb

 

[vamoose]

Got it ! Thanks for the help. I just moved the "IN" upto the Insert Into statement. Thanks again to all.