Append All Tables in Db to New table (w/ column indicating filename)

[pappyb]

Good afternoon, I'm hoping someone can help me with this bit of code I have. I've gotten this handy code from a user during a google search. I can't seem to get the adjustment I've made to allow this module to work.

What I'm trying to do: append all tables in an existing database into a new table. I only have about 8 fields to append, however I need to also create a field within this new table to indicate the table's name (so I know which table the record originated from).

I would greatly appreciate any tips given to help this work. My vba is novice, however I have years of Access experience and can't seem to get this to work. The need is required as I'm working with about 600 files i used a module to import.


Below is my code. The error I'm getting is:

Run-time error '3134':
Syntax error in INSERT INTO statement.

my code is
Public Sub CreateAndAppend()
'Create table
Dim tblDef As DAO.TableDef
Set tblDef = CurrentDb.CreateTableDef("tempTable", , , CurrentProject.Connection)

With tblDef
.Fields.Append .CreateField("Whse No", dbLong)
.Fields.Append .CreateField("Whse", dbLong)
.Fields.Append .CreateField("SKU Stk", dbLong)
.Fields.Append .CreateField("BO Qty", dbLong)
.Fields.Append .CreateField("Org", dbLong)
.Fields.Append .CreateField("Org Net Inv", dbLong)
.Fields.Append .CreateField("Status", dbLong)
.Fields.Append .CreateField("BO Date", 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, 21) = "tbl_Daily_BackOrders_" Then
DoCmd.SetWarnings (False)
DoCmd.RunSQL "INSERT INTO tempTable ( Whse No, Whse, SKU Stk, BO Qty, Org, Org Net Inv, Status, BO Date )" & _
" SELECT Title, User, Date " & " FROM [" & oTbl.Name & "]"
DoCmd.SetWarnings (True)
End If
Next

'cleanup
Set oTbl = Nothing
End Sub


Mark

 

[dhookom]

Lots of issues to clean up. First, do yourself a favor and don't create field names with spaces. If you truly "have years of Access experience" you should know that spaces require you to place []s around your field names.

An insert query must have the same number of fields in the "INSERT INTO..." as the "SELECT...".

You can select the table name if you want using:

  " SELECT Title, User, Date, '" & oTbl.Name & "' FROM [" & oTbl.Name & "]"

Duane
Hook'D on Access
MS Access MVP

 

[pappyb]

Thanks for your help.