Compile error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig - 30 Aug 2006 07:51 GMT

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.

Signature

Craig

Reply to this Message

RoyVidar - 30 Aug 2006 08:31 GMT

"Craig" <Craig@discussions.microsoft.com> wrote in message
<23F5A3E1-C8F4-46A0-B776-E447DF0DEE72@microsoft.com>:

> I'm using Access 2003. When I run the sub below I get the following
> error message: "Compile error. Wrong number of arguments or
[quoted text clipped - 21 lines]
>
> 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.

Signature

Roy-Vidar

Reply to this Message