VBA to Combine Tables

[one3nine0]

I have about 100 tables with mostly the same fields. There might be 1 missing from random tables.

I want to combine all the tables with all the fields into 1 table, but also add a column for each tables respective name so that I know which table the data came from.

I do not think I can do this with a query (at least not all at once), so looking to VBA to solve this.

Any suggestions?

 

[xenou]

there needs to be a closing parenthesis at the end of your column list...

...,[Connect Time)]"

Should be:
...,[Connect Time)])"

 

[one3nine0]

wow haha, such a simple mistake...

So I updated the code, but now I am running into an issue with the "From" clause.

I thought it was because there needed to be a semi-colon to finish the SQL, but that didn't fix it.

Public Sub Compile_Tables()

Dim tdf As DAO.TableDef
Dim sTable As String
Dim SQL As String


For Each tdf In CurrentDb.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        sTable = tdf.Name
        If Not sTable = "[2015 Call Data]" Then
            SQL = ""
            SQL = SQL & " Insert into [2015 Call Data] ([Table Name], [Dialed Number],[RTN], [City],[Connect Date],[Elapsed Time],[Elapsed Time (seconds)],[Originating Number],[Orig NPA],[State],[Zip Code],[Connect Time)])"
            SQL = SQL & " Select ' " & sTable & " ' as [Table Name], [Dialed Number],[RTN], [City],[Connect Date],[Elapsed Time],[Elapsed Time (seconds)],[Originating Number],[Orig NPA],[State],[Zip Code],[Connect Time]"
            SQL = SQL & " From " & sTable & " ;"
            Debug.Print SQL
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL
            DoCmd.SetWarnings True
            
        End If
        
    End If
    
Next


End Sub

 

[xenou]

You mentioned not all your tables have the same columns, exactly. Is that a problem here? the syntax looks okay, but to really know we need to be sure of the tables - the one selecting from, and the one inserting into. You also have to generally be sure the datatypes are compatible.

 

[one3nine0]

So all of the columns are the same: same column name, same datatype. It is just some tables don't have the [Connect Time] column.

I tried removing the [Connect Time] column from the code, but the same error shows up.

"From" syntax error.

 

[one3nine0]

Oh ok, I figure it out.

I needed to add brackets around the table name in the from syntax as well as remove the connect time column.

I didn't really need the connect time, so this works.

Thanks so much for the help!!

 

[Joe4]

You actually do NOT want a space before the semi-colon.
Change:

" ;"

to

";"

 

[one3nine0]

Ok so it didnt work as intended.

The code runs, but nothing is appended into the table [2015 Call Data]
In the VBA immediate box, I can see it showing the correct sql, but it isn't doing anything to my tables.

I tried running one of the SQL queries that the VBA code spits out on its own, and it says there is a conversion type failure, which set everything to 0.
But that doesn't make sense to me because the datatype for all the tables are the same.

Here is the code:

Public Sub Compile_Tables()

Dim tdf As DAO.TableDef
Dim sTable As String
Dim SQL As String


For Each tdf In CurrentDb.TableDefs
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        sTable = tdf.Name
        If Not sTable = "[2015 Call Data]" Then
            SQL = ""
            SQL = SQL & " Insert into [2015 Call Data] ([Table Name], [Dialed Number],[RTN], [City],[Connect Date],[Elapsed Time],[Elapsed Time (seconds)],[Originating Number],[Orig NPA],[State],[Zip Code])"
            SQL = SQL & " Select ' " & sTable & " ' as [Table Name], [Dialed Number],[RTN], [City],[Connect Date],[Elapsed Time],[Elapsed Time (seconds)],[Originating Number],[Orig NPA],[State],[Zip Code]"
            SQL = SQL & " From [" & sTable & "];"
            Debug.Print SQL
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL
            DoCmd.SetWarnings True
            
        End If
        
    End If
    
Next


End Sub

 

[one3nine0]

I figure it out. Although the datatypes were the same, the format "long, double, single, etc" were different. Made them the same and it worked fine.

Thanks Joe4 and big thanks to xenou!

 

[xenou]

Great.

I should have realized this:

I needed to add brackets around the table name in the from syntax as well as remove the connect time column.

Any table names, field names, or query names with spaces in them will need brackets around them.