Appending 300 tables into 1 table

[jean paul levahn]

I need to append 300 tables (same columns, possible diff formats) onto 1 in Access.

All the tables start with the same name.

I used to have a piece of code that i ran that would simply pull all tables into 1 but have since lost it.

I have to be honest and say i have no VB skills what so ever and i think this is the only way this could be done quickly.

[tasawer]

Hi what do you mean by diff formats?

How many coloumns are there and what are the formats?

1. longwinded way would be to create a union query of 300 tables. But this would be view only.. data can only be added to original tables.

2. a.we can store all the table Names into a temporary Table
b. Read the contents of each table
c. Append to new table usng SQL

[jean paul levahn]

"Hi what do you mean by diff formats?" What I mean by this is that the excel sheets have different formats in the columns. But they all should be text columns.

I would prefer to use method 2 as i have to do this for several lots of data.

What do i need to put in the module?

[TheSmileyCoder]

Here is some example code, you will need to modify as appropriate.

Public Sub CreateAndAppend()
    'Create table
        Dim tblDef As DAO.TableDef
        Set tblDef = CurrentDb.CreateTableDef("tempTable", , , CurrentProject.Connection)
 
        With tblDef
            .Fields.Append .CreateField("KEY_Hazard", dbLong)
            .Fields.Append .CreateField("mem_Title", dbMemo)
            .Fields.Append .CreateField("ID_CreatedBy", dbLong)
            .Fields.Append .CreateField("dt_Created", dbDate)
        End With
 
        CurrentDb.TableDefs.Append tblDef
 
    'Cleanup
        Set tblDef = Nothing
    Dim oTbl As Object
    For Each oTbl In CurrentData.AllTables
        If Left(oTbl.Name, 5) = "excel" Then
            DoCmd.SetWarnings (False)
            DoCmd.RunSQL "INSERT INTO tempTable ( KEY_Hazard, mem_Title, ID_CreatedBy, dt_Created )" & _
                " SELECT [Hazard ID], Title, User, Date " & _
                " FROM [" & oTbl.Name & "]"
            DoCmd.SetWarnings (True)
        End If
    Next
 
    'cleanup
        Set oTbl = Nothing
End Sub

You write that all your tables start with the same letters, in this example I append all tables called "excel....." to the first table I create.