Append data through VBA

[Sreedhar]

I've almost 50 to 60 tables imported from everywhere which have very
different names but the same structure. I'm required to create a master table
by appending all the data into one table to provide meaningul interpretation
of data.

I tried the following code with erratic results.(i.e., it appends some of
the data from some of the tables and then an error message shows up, saying
"Run-time error:3061 = Too few parameters.Expected 11")

Can someone please show me the light ? Thank you.

The code:
Sub AppendMaster()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strSQL As String

strSQL = "INSERT INTO tblMaster " & _
"( fld1,fld2, fld3, fld4, fld5) " & _
"SELECT fld1, fld2, fld3, fld4, fld5 FROM "
Set db = CurrentDb

For Each tdf In db.TableDefs
db.Execute strSQL & tdf.Name & ";"
Next
End Sub

 

[Duane Hookom]

You have several system tables in your MDB that will not have the same
structure.

Sub AppendMaster()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strSQL As String

strSQL = "INSERT INTO tblMaster " & _
"( fld1,fld2, fld3, fld4, fld5) " & _
"SELECT fld1, fld2, fld3, fld4, fld5 FROM "
Set db = CurrentDb

For Each tdf In db.TableDefs
If Left(tdf.Name,4)<>"msys" And _
tdf.Name <>"tblMaster" Then
db.Execute strSQL & tdf.Name
End If
Next
End Sub

 

[Sreedhar]

If Left(tdf.Name,4)<>"msys" And _

"Debug.Print tdf.Name" confirmed what you just said. Thank you Duane.

tdf.Name <>"tblMaster" Then

How foolish I was not to think of this ? Obviously, you saved me from a
major embarrasment. Thanks again.