All queries not working in a code snippet

[greaseman]

I've got a piece of code that contains a few queries. My problem is that some of the queries work, while others give me a "Syntax error in INSERT INTO statement" message. I'm going crazy trying to figure out why some queries work, but others don't.

If someone could take a look and offer a thought or two, or three..... I would appreciate t before I go postal. And, thanks so much in advance!! Can't believe how much this forum offers....it's great!

Here's my piece of code:

THIS QUERY WORKS

strSQL = ""
strSQL = strSQL & "INSERT INTO MasterPN "
strSQL = strSQL & "(PN, FSCM, Keyword, Description, SPN, ATAPN, DimData, ESDS) "
strSQL = strSQL & "VALUES ('" & strElement & "', '" & strFSCM & "', "
strSQL = strSQL & " '" & strKey & "', '" & strDesc & "', "
strSQL = strSQL & " '" & strCPN & "', '" & strATAPN & "', "
strSQL = strSQL & " '" & strdd & "', " & intESDS & ") "

conJetMasterDb.Execute strSQL, , adExecuteNoRecords

THIS QUERY WORKS, WITH THE USE OF BRACKETS.

strSQL = ""
strSQL = strSQL & "INSERT INTO Chapter "
strSQL = strSQL & "([Chapter], [Key]) "
strSQL = strSQL & "VALUES ('" & strChapter & "', '" & strChapterKey & "')"

conJetDestDb.Execute strSQL, , adExecuteNoRecords

THIS QUERY DOES NOT WORK, EVEN WITH USING BRACKETS ???????????

strSQL = ""
strSQL = strSQL & "INSERT INTO Section "
strSQL = strSQL & "([Chapter], [Section], [Key]) "
strSQL = strSQL & "VALUES ('" & strChapter & "', '" & strSection & "', '" & strSectionKey & "')"

Debug.Print strSQL
conJetDestDb.Execute strSQL, , adExecuteNoRecords

 

[Ian Mac]

Hi,

I can't see how amy of them work as they don't have a semi-colon at the end of the strings.

strSQL = ""
strSQL = strSQL & "INSERT INTO Section "
strSQL = strSQL & "([Chapter], [Section], [Key]) "
strSQL = strSQL & "VALUES ('" & strChapter & "', '" & strSection & "', '" & strSectionKey & "');"

Should work for you.

 

[greaseman]

Interestingly enough, they DO work without the semi-colon at the end..... turned out my problem was solved by putting brackets around my table names and around my field names.

 

[Bat17]

I think that Section is another of Access's reserved words.

Peter