Help request - VBA to copy data from one set of tables to another (1 Viewer)

[gellerche]

I need to copy data from one set of tables (source) to another (destination). In the destination tables, some of the tables have less fields than the source tables (eg. Table2 (has Field1 and Field2) will be copied to Table1 (has only Field 1)). All of the fields in the destination tables are in the respective source tables.

I'd like to use this SQL to do the job for each pair of tables:

SQL = "INSERT INTO Table1 SELECT * FROM Table2 "

, but I get an error complaining that there's nowhere for Field2 to go. Is there a way to have the SQL copy over the data only where the two tables have fields in common without explictly naming the fields? I would rather not have to code something like:

INSERT INTO table1 (field1, field2) SELECT field1, field2 FROM table2....

because some of these tables have 25 fields, and I don't want to have to type each one.


Thank you,

Steve Geller

 

[WayneRyan]

Steve,

If the columns in the tables don't match exactly, SQL will not
try to referee the inserts. It will not make decisions on its
own.

It looks like you will have to individually name the columns
to transfer data from dis-similar tables.

Wayne

 

[gellerche]

I was afraid of that. Thank you for giving me the information, Wayne.

 

[WayneRyan]

Steve,

It is possible to use VBA to traverse the TableDefs and
where the field names are the same move the data over.
I haven't done it, so have no examples.

Wayne

 

[atbaranet]

If Table1 and Table2 Structur the same you can use

DoCmd.RunSQL "INSERT INTO Tbl1 SELECT Tbl2.* FROM Tbl2 LEFT JOIN Tbl1 ON [Tbl2].ID = TBL1.ID WHERE ((( Tbl1.ID) Is Null));"
i have used it

i hope it works