import from one table to another in Access 2003

[joebox888]

Hi,
At the moment i have roughly 200 tables in my DB, I am working on cutting this down to only 7 tables.
Lets say you have two tables with the same fields in each, one of the tables is empty(fields only).. How could I import the data and structure of one into the other?
When i import through the external data option it just creates a new table with a "1" on the end!
This would be very handy and Im sure im blind to the obvious answer...
Any help would be great!
thanks

 

[sxschech]

If the table you are importing is also from access and the structure (col names and number of cols) is the same, then you could do an append query instead of importing. Something like:

INSERT INTO tblDestination
SELECT *
FROM tblSOURCE  IN 'c:\temp\Source.mdb';

The "IN" statement is how you get the data from the other file, not to be confused with the IN statement of a where clause.

 

[joebox888]

Thanks sxschech,
For multiple tables though how would i phrase that? My destination Table is LAC and i have about 60 Tables, each tables name begins with LAC_BP_xxx.
Could I use a wildcard to pick out tables beginning with "LAC_BP_" and if so how would i go about doing this?
Also when i append each table to the LAC table will they list one after the other or will they just keep overwriting each other?
Thanks for the help!

 

[joebox888]

Sorry, Meant to say that the LAC(destination table) has all fields needed however the source tables may not have 1 or 2 fields as they would not have been needed!
Can an Append query still be used or would i have to use a union query?
thanks

 

[sxschech]

If the source table contains more fields than the destination and you are not going to use all the fields, you would need to specify which fields you want to update. You can set this up using the design view of the query.

An append query does not overwrite the data. Append adds new rows, to overwrite, you would use an update query.

To do the wildcard to pick the tables, you would need to use VBA code. However, if this is going to be a one time operation to append the data, you could consider doing a union, or if you do each table individually, then you could try one of the following:

A) copy the table to a common name such as copy LAC_BP_001 to LAC_BP and have your update query "FROM LAC_BP in "

B) if you are comfortable with remembering the original table name, instead of copying the table you can rename it to the generic name and then rename it back afterward

C) perhaps doing a union may not be too bad an idea then you could grab all the data in one or two goes, depending on how much data are being unioned

D) You could also use VBA to code the operations, but then you would need to set up a loop or counter or have some other way to choose the tables to work with.

E) Semi manual, you could build a form and have a combo box on it that would allow you to choose the table and then have the code do the copy and run the update for you.

It really depends on the time you have to do code versus manually copying the table to a new name or copying the data directly.

 

[joebox888]

Ended up just doing a simple insert query on each table individually..

INSERT INTO XLAC
SELECT LAC_QOD_40020.*
FROM LAC_QOD_40020;

took a while but Seems to work grand!

Thanks for the help!