From Data from Word to Access using SQL INSERT INTO 2

[riccaldwell]

I am trying to create a survey in a Word form and dump the values in to an Access table. Following is a part of the code from my module to load the responses (in this case only question 1). I get a 3134 "Syntax error in INSERT INTO statement" error on any one of the 3 ways I tried to run the SQL. I'm not a programmer; really just one of those users that drive you real programmers nuts. Any ideas will be greatly appreciated.

' Load the DB using SQL statements
' Open Access database
With appAccess
.OpenCurrentDatabase "C:\My Documents\CD_V4.mdb", False
.Visible = True
'.DoCmd.OpenForm "class entry form" 'db connectivity test purposes

'Load Response to Question 1
' .DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Surv_Quest_ID, Comment) VALUES(strUserID, strCourseAttended, strCourseDate, intSession, intQID1, strEL1_Q1_Comments)"
' .DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Surv_Quest_ID, Comment) VALUES ('" & strUserID & "','" & strCourseAttended & "','" & strCourseDate & "','" & intSession & "','" & intQID1 & "','" & strEL1_Q1_Comments & "')"
' .DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Surv_Quest_ID, Comment) VALUES (" & strUserID & "," & strCourseAttended & "," & strCourseDate & "," & intSession & "," & intQID1 & "," & strEL1_Q1_Comments & &quot"

No matter which I uncomment, I get the aforementioned error.

 

[SBendBuckeye]

You need to have the proper delimiters around your data values, eg ' around text data and # around date data. Your second example above is pretty close. Assuming that your type prefixes are accurate, try this instead:

.DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Surv_Quest_ID, Comment) VALUES ('" & strUserID & "','" & strCourseAttended & "','" & strCourseDate & "'," & intSession & ",'" & intQID1 & "','" & strEL1_Q1_Comments & "')"

All I did was remove the single quote ' just before and just after intSession because that is a numeric value and does NOT need a delimiter. If you have a data variable, use a pound sign # instead of a single quote ' around the value.

Good Luck!

 

[lespaul]

Looks like intQID also has the ' delimiter! I don't use Access that much I use Delphi which has a QuotedString function that basically adds the delimiter and makes the code easier to read, maybe Access has a similar function?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SBendBuckeye]

Sorry, I missed one. Leslie was right. Here is the corrected version:

.DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Surv_Quest_ID, Comment) VALUES ('" & strUserID & "','" & strCourseAttended & "','" & strCourseDate & "'," & intSession & "," & intQID1 & ",'" & strEL1_Q1_Comments & "')"

Good Luck!

 

[riccaldwell]

Great catches! Thanks! In looking at your solutions, I noticed that I also made some very "rookie errors," i.e. omitted a field value that is in my db and didn't exactly match field names. I've ammended the following docmd to the above code, and I am still getting the same syntax error. Anyone still willing to take a gander at this?

.DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Survey_Question_ID, Answer, Comment) VALUES ('" & strUserID & "','" & strCourseAttended & "','" & strCourseDate & "'," & intSession & "," & intQID1 & "," & intEL1_Q1 & ",'" & strEL1_Q1_Comments & "')"

Thanks!
Ric

 

[lespaul]

The only thing I can think of is the # around your date string, I think Access needs the dates to be delimited with it like this:

.DoCmd.RunSQL "INSERT INTO User_Survey (User_ID, Course_Num, Date, Session, Survey_Question_ID, Answer, Comment) VALUES ('" & strUserID & "','" & strCourseAttended & "',#" & strCourseDate & "#," & intSession & "," & intQID1 & "," & intEL1_Q1 & ",'" & strEL1_Q1_Comments & "')"

But it may also still need the ' (which I removed).





Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[SBendBuckeye]

If that is a date field, Leslie is correct. You delimit date strings with #, not '. If it keeps giving you problems try using just a couple fields and then adding to it from there. Your syntax looks good.

Good Luck!

 

[riccaldwell]

lespaul & SBendBuckeye ... Thanks a million; you really got me going in the right direction. I was still getting the syntax error message and a friend of a friend took a look at the code and found the crux of the problem; I thought I'd pass it on in case it could help others.

If you look at the field names in my User_Survey table, one is named "Date" ... Although Access is willing to live with it, apparently ANSI SQL isn't, as it is a reserved word. When he changed the name of that field in my table and the associated SQL string ... presto! ... my table is populating without complaint.

Again, thanks for the help. This is an awesome set of forums ... made awesome by the people who participate.

 

[lespaul]

Those pesky reserved words! You would think that there would be a better error message than 'Syntax Error'!!! Glad you got it all straightened out and hopefully won't name anymore fields 'DATE'.

I agree about the forum, this is the best one I have found, I've pretty much stopped going elsewhere. And it IS the people who make it great!!!


Les

 

[SBendBuckeye]

Nice catch by whoever. It is a good idea to never use Date and Name or any other reserved word by themselves for that reason. If you must at some point because you can't control it, then square brackets [] around the field name will allow your code to work correctly, but it's easier to just avoid them. Glad to hear you got it working. Have a great day!

 

[riccaldwell]

Yep ... rookie mistakes that I'll not make again. Thanks again for all your help!