Compile error

[Craig]

I'm using Access 2003. When I run the sub below I get the following error
message: "Compile error. Wrong number of arguments or improper property
assignment."

I'm trying to append about 50 tables through vba to a table called "tblAll".
I don't know what the issue is. The code is as follows:

Sub AppendTables()
Dim db As Database, rs As Recordset, i As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("qry_Table_Names")

rs.MoveFirst

Do While Not rs.EOF
DoCmd.RunSQL "INSERT INTO tblAll ( Field1, Field2, Field3) SELECT " &
rs("Name") & ".[Field1]", rs("Name") & ".[Field2]" & rs("Name") & ".[Field3]"
& " FROM " & rs("Name") & ";"

rs.MoveNext
Loop
MsgBox "Done."
End Sub

Thanks for any and all help.

 

[RoyVidar]

I'm using Access 2003. When I run the sub below I get the following
error message: "Compile error. Wrong number of arguments or
improper property assignment."

I'm trying to append about 50 tables through vba to a table called
"tblAll". I don't know what the issue is. The code is as follows:

Sub AppendTables()
Dim db As Database, rs As Recordset, i As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("qry_Table_Names")

rs.MoveFirst

Do While Not rs.EOF
DoCmd.RunSQL "INSERT INTO tblAll ( Field1, Field2, Field3) SELECT " &
rs("Name") & ".[Field1]", rs("Name") & ".[Field2]" & rs("Name") &
".[Field3]" & " FROM " & rs("Name") & ";"

rs.MoveNext
Loop
MsgBox "Done."
End Sub

Thanks for any and all help.

From the look of it, it shouldn't compile.

For selects from only one table, there's no need to prefix the column
list, so I think I'd probably try something like this

dim strSql as string
strsql = "INSERT INTO tblAll ( Field1, Field2, Field3) " & _
"SELECT [Field1], [Field2], [Field3] " & _
"FROM [" & rs("Name") & "];"

But - the whole operation seems a bit suspicious to me. Having the
need to perform something like this, indicates that something that
perhaps belongs in one table in the first place, is separated into
several tables.