Appending 100 tables to 1 table w/0 query?

[Faction21]

Is it at all possible to append 100 or so tables into one main table? All attempts have failed, and i do not want to make 100 append queries to do this.

Is there any possible oter way to do this, prefferably in VBA?

-Thanks in advance

 

[sfreeman@co.mer]

Maybe... :{B

First create a Union query that will join all of the 100 tables under one roof [presuming that you have compatible fields].

Then create the append query to append all of that data into your target table.

Hopefully, you can get creative with cut and paste when setting up the large Union query.

 

[The_Doc_Man]

By the time you wrote the VBA for this, you could have written one Append query, then done 99 cut-and-paste operations.

The VBA trick would be that you had better have a way to identify the tables to be appended AND to distinguish those tables from the one being appended to.

Then you could write VBA code to step through the TableDefs collection one table at a time. (TableDefs is a collection for which extensive help is available from the Help facility in Access.) From each TableDef you could get the .Name property for the table (as a string). From there, compare it to your selection template or list to see if it is a valid source. From there, build the SQL query as a string.

Now, execute the query. The next step is to either erase the source table or rename it so that you cannot accidentally append twice. Eventually you would come to the end of the list.

Alternative: Make a table with one field listing the tables to be appended. Don't include the receiving table. Build one of the queries in design view. Switch to SQL view so you can see the exact string that got built. COPY it but don't execute it.

Now in a VBA routine, open a recordset to your one-field table that lists source tables. (If you wanted, you could add a Yes/No field to your list table, and that field would be updated after each successful append to prevent double-dipping.)

For each record of the recordset build the SQL string based on the stuff you saw in SQL view. The only thing that will be different is the name of the table in the FROM clause, so you will have two constants and one variable part in the VBA code. Something like

strMyAppend = "INSERT INTO .... FROM" & table-name & ";"

(The above assumes no selection of the source records using a WHERE clause, no ORDER-BY clause to alter presentation in the table, and no SQL aggregates with a GROUP-BY clause.) Again, execute the SQL.

Now, here is the catch. NEITHER of these methods work unless the field names in all 100 tables are identically the same. Oddly enough, they do NOT have to appear in the same order, because this method causes dynamic name association. But all source-table fields must have the same names, data types, and data sizes if you are going to expect consistent results.

 

[Faction21]

Yes, all tables do have the exact same names every time. The union query is not looking mighty tempting, i will experement, and post some results if all goes wrong.

Thanks a whole bunch guys, this really helps me out, if you still have any suggestions please let me know.

*EDIT*
ok I need a bit of help with the syntax of the union query. THis is what im trying to do...

SELECT [Index0], [Index1], [Index2], [Index3], [Index4], [Index5], [Index7], [Index8], [Index9], [FileName], [index11], [IndexNum], [OpNo], [Operations], [ScanDate], [ScanPerson], [IndexDate], [IndexPerson], [DoubleKeyDate], [ProcessDate], [ProcessPerson], [VerifyDate], [VerifyPerson], [OutputDate], [OutputPerson], [ModifyDate], [ModifyPerson], [PagesInTiff], [Batch], [SortNo]

FROM [Indexing-1]

UNION SELECT [Index0], [Index1], [Index2], [Index3], [Index4], [Index5], [Index7], [Index8], [Index9], [FileName], [index11], [IndexNum], [OpNo], [Operations], [ScanDate], [ScanPerson], [IndexDate], [IndexPerson], [DoubleKeyDate], [ProcessDate], [ProcessPerson], [VerifyDate], [VerifyPerson], [OutputDate], [OutputPerson], [ModifyDate], [ModifyPerson], [PagesInTiff], [Batch], [SortNo]
FROM [Indexing-2]

UINION SELECT [Index0], [Index1], [Index2], [Index3], [Index4], [Index5], [Index7], [Index8], [Index9], [FileName], [index11], [IndexNum], [OpNo], [Operations], [ScanDate], [ScanPerson], [IndexDate], [IndexPerson], [DoubleKeyDate], [ProcessDate], [ProcessPerson], [VerifyDate], [VerifyPerson], [OutputDate], [OutputPerson], [ModifyDate], [ModifyPerson], [PagesInTiff], [Batch], [SortNo]]
FROM [Indexing-3]
...............
Indexing is named up until 104, tehn a few otehr tables are included, all with teh exact same type of data and fields

this is not working, what am i missing or what do i need?

 

[Faction21]

Maybe... :{B

First create a Union query that will join all of the 100 tables under one roof [presuming that you have compatible fields].

Then create the append query to append all of that data into your target table.

Hopefully, you can get creative with cut and paste when setting up the large Union query.

I got the Union Query working, it only allows 50 tables at a time, or is says "QUERY TOO COMPLEX".

But when i make an Append Query for the Uniion query1-50, it says "QUERY TOO COMPLEX" when i try to execute.... any help?

 

[WayneRyan]

Faction,

Dim dbs As DAO.DataBase
Dim rstOld As DAO.RecordSet
Dim rstNew As DAO.RecordSet

Dim ctr As Long

Set dbs = CurrentDb
Set rstNew = "Select * From NewTable"

For ctr = 1 to 104
   Set rstOLD = "Select * from Indexing-" & Cstr(ctr)
   While Not rstOLD.EOF and Not rstOLD.BOF
      rstNew.AddNew
      rstNew![Index0] = rstOLD![Index0]
      .
      . Reference each field ...
      .
      rstNew![SortNo] = rstOLD![SortNo]
      rstNew.Update
      rstOLD.MoveNext
      Wend
   Set rstOLD = Nothing
   Next ctr

Wayne