runtime error 3075 - missing operator

[ypmarg]

I have an existing table that contains all my data. I created a new table
using:

dbs.Execute "CREATE TABLE " & strCourse & " (LastName CHAR, FirstName CHAR,
" & strCrseCol & " DATETIME, " & strCrseNom & " YESNO)"

and this works fine. Now I need data from my existing table to my newly
created table. The code I wrote is:

strSQL = "INSERT INTO " & strCourse & " SELECT LastName, FirstName, " &
strCrseCol & "," & strCrseNom & " FROM Associates WHERE " & strCrseNom & "
=False"
CurrentDb.Execute strSQL

When I get to the last line to execute, I get a "runtime error 3075 - syntax
erro missing operator"

I can send the entire code I've written, if that will help.

 

[Stefan Hoffmann]

hi,

I have an existing table that contains all my data. I created a new table
using:

dbs.Execute "CREATE TABLE "& strCourse& " (LastName CHAR, FirstName CHAR,
"& strCrseCol& " DATETIME, "& strCrseNom& " YESNO)"

This sounds like a normalization problem in the first place. If it's not
a temporary table, you should consider using one table using the course
name as additonal field as discriminator. Also using variable field
names indicate a "poor" design. If you really need this kind of
information, you should use a kind of EAV model:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

So that your tables should look like:

Course: ID, LastName, FirstName, IdCourseCol (FK), CourseColValue,
IdCourseNom (FK), CourseNomValue
CourseCol: ID (PK), Caption
CourseNom: ID (PK), Caption

and this works fine. Now I need data from my existing table to my newly
created table. The code I wrote is:

strSQL = "INSERT INTO "& strCourse& " SELECT LastName, FirstName, "&
strCrseCol& ","& strCrseNom& " FROM Associates WHERE "& strCrseNom& "
=False"
CurrentDb.Execute strSQL

There is the field list missing.

INSERT INTO [tableName] (fieldList)
SELECT fieldList
FROM [tableName]
WHERE NOT [fieldName]

You should enclose all field names in square brackets as you are using
dynamic field names. This will avoid wrong SQL statements due to names
using invalid character sequences.

Why don't you use this select query for further processing instead of
inserting the data into a new table?


mfG
--> stefan <--