append query using variables

J

jpb

I am trying to run an append query using variable tables. Dependant upon
other fields, AppropriateTable is modified to be the title of a prexisiting
table name in order to add the data from DataTable. I am getting a Type
Mismatch Error #13 on the below code. Can anyone see why? I am at a loss.
I have built the query using the design viewer - and it runs correctly - but
the SQL code that it generates does not translate dirrectly.

Thanks.
jpb


DoCmd.RunSQL "INSERT INTO " & AppropriateTable(Field1, Field2, Field3) _
& " SELECT DataTable.Field1, DataTable.Field2,
DataTable.Field3" _
& " FROM DataTable"
 
D

David Lloyd

One thing you may want to check is this portion of the query:

AppropriateTable(Field1, Field2, Field3)

Your post does not show and quotes around this part, and as such it may not
be interpreted properly.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to run an append query using variable tables. Dependant upon
other fields, AppropriateTable is modified to be the title of a prexisiting
table name in order to add the data from DataTable. I am getting a Type
Mismatch Error #13 on the below code. Can anyone see why? I am at a loss.
I have built the query using the design viewer - and it runs correctly - but
the SQL code that it generates does not translate dirrectly.

Thanks.
jpb


DoCmd.RunSQL "INSERT INTO " & AppropriateTable(Field1, Field2, Field3) _
& " SELECT DataTable.Field1, DataTable.Field2,
DataTable.Field3" _
& " FROM DataTable"
 
D

Douglas J. Steele

Is AppropriateTable returning the correct table name? I noticed that your
example has embedded spaces in the table name: anytime you have embedded
spaces, you need to surround the table (or field) name with square brackets:

DoCmd.RunSQL "INSERT INTO [" & AppropriateTable(Field1, Field2, Field3) &
"] " _
& " SELECT [DataTable].Field1, [DataTable].Field2,
[DataTable].Field3" _
& " FROM [DataTable]"

Of course, since you only have one table in that query, you don't really
need to qualify the field names:

DoCmd.RunSQL "INSERT INTO [" & AppropriateTable(Field1, Field2, Field3) &
"] " _
& " SELECT Field1, Field2, Field3" _
& " FROM [DataTable]"
 
Top