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?

 

[ranman256]

you dont need vba, use a macro, put all the append queries in it.

now if you named the tables tbl01, tbl02, etc,
then you can use vb to cycle thru the tables in a few lines.
but not if the tables are random names.

 

[one3nine0]

I wanted to use VBA because I didn't want to write/set up 100ish append queries. I'd rather have one vba code I can use once, and use that same code in different databases.

The table names look like:
1-15 XXX
1-15 YYY
2-15 XXX
2-15 YYY
etc...

 

[xenou]

Code to get table names. You can adapt to manipulate a sql command to script your updates.

Public Sub foo()
 
Dim tdf As DAO.TableDef
Dim sTable As String

For Each tdf In CurrentDb.TableDefs
 
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
        
        sTable = tdf.Name
        Debug.Print sTable

    End If

Next
 
  
End Sub

 

[xenou]

This is untested (not even compiled for a syntax check). But it is the general idea:

Public Sub foo()
 
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 = "SomeTable" Then '//Don't insert the destination table into itself. Other tables may need to be excluded.

            SQL = ""
            SQL = SQL & " insert into SomeTable (OriginTableName, Field1, Field2, Field3, Field4)"
            SQL = SQL & " select '" & sTable & "' as OriginTableName, Field1, Field2, Field3, Field4"
            SQL = SQL & " from " & sTable
            Debug.Print SQL

            docmd.SetWarnings False
            '//DoCmd.RunSQL SQL
            DoCmd.SetWarnings true
        
        End If
            
    End If

Next
 
  
End Sub

 

[one3nine0]

Thanks! I am going to try this out tomorrow

This is untested (not even compiled for a syntax check). But it is the general idea:

Public Sub foo()
 
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 = "SomeTable" Then '//Don't insert the destination table into itself. Other tables may need to be excluded.

            SQL = ""
            SQL = SQL & " insert into SomeTable (OriginTableName, Field1, Field2, Field3, Field4)"
            SQL = SQL & " select '" & sTable & "' as OriginTableName, Field1, Field2, Field3, Field4"
            SQL = SQL & " from " & sTable
            Debug.Print SQL

            docmd.SetWarnings False
            '//DoCmd.RunSQL SQL
            DoCmd.SetWarnings true
        
        End If
            
    End If

Next
 
  
End Sub

 

[xenou]

Hopefully it goes without saying that you will backup all your data and your database first.

 

[one3nine0]

So I am getting a Syntax error with the insert into statement.

Here's the code so far:

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

Also, in the select line, I see two apostrophes, one after select and one before as OriginTableName. Are these meant to be there or at they supposed to asterisks?

 

[Joe4]

Note an important difference between your code and xenou's.
As you adds more text to the SQL string, he starts it off with a blanks space, i.e.

SQL = SQL & " insert into SomeTable (OriginTableName, Field1, Field2, Field3, Field4)"

note the space between the " and the word insert.
You do not have that. So each line will run into the next, causes syntax errors.
You need to have at least one space either at the end of each SQL statement or at the beginning of them to create the needed separation.

 

[one3nine0]

OO. Didn't realize

Here is the updated code, and I'm still running into syntax error for the insert into statement

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