Why Does DoCmd.RunSQL Report Syntax Error In Query?

[polpoint]

I am using access to insert records into quickbooks using qodbc. I have a VBA DoCmd.RunSQL to insert an InvoiceLine using a statement I build dynamically. I have a MsgBox that reports the statement right before calling DoCmd.RunSQL and it says this:

Insert Into "InvoiceLine" ("CustomerRefFullName", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "FQSaveToCache") VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-834207-002000009H001', 150, 1, 0)

When I copy the statement verbatim (using ctrl-c on the MsgBox) and paste it into a query it executes just fine. What is the deal here? I am new to access and vb in general.

When I execute the code i receive a "Syntax error in query. Incomplete query clause." error.

 

[pr2-eugin]

Loose the double quotes for the table name and try again.??

 

[polpoint]

Tried that too. Here was the final statement:

---------------------------
Microsoft Access
---------------------------
Insert Into 'InvoiceLine' ( 'CustomerRefFullName', 'InvoiceLineItemRefListID', 'InvoiceLineDesc', 'InvoiceLineRate', 'InvoiceLineAmount', 'FQSaveToCache') VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-834207-002000009H001', 150, 1, 0)
---------------------------
OK
---------------------------

Same error. The above does not work if I paste it into a query. I get ODBC--call failed [QODBC] Expected lexical element not found: <identifier> error.

I also get the ODBC call failed if I change back to double-quotes in the INSERT INTO portion of the call. But only when I paste the SQL into a query. When calling the statement through code I receive the same "Syntax error in query. Incomplete query clause."

Here is my string builder code (kind of messy):

Dim insertString As String
insertString = "Insert Into " & Chr(34) & "InvoiceLine" & Chr(34) & " (" & Chr(34) & "CustomerRefFullName" & Chr(34) & ", " & Chr(34) & "InvoiceLineItemRefListID" & Chr(34) & ", " & Chr(34) & "InvoiceLineDesc" & Chr(34) & ", " & Chr(34) & "InvoiceLineRate" & Chr(34) & ", " & Chr(34) & "InvoiceLineAmount" & Chr(34) & ", " & Chr(34) & "FQSaveToCache" & Chr(34) & ") VALUES ('"
'insert customerRefFullName
Dim insertString2 As String
insertString2 = "', '"
'insert InvoiceLineDesc
Dim insertString3 As String
insertString3 = "', "
'insert InvoiceLineRate
Dim insertString4 As String
insertString4 = ", "
'insert InvoiceLineAmount
Dim insertString5 As String
insertString5 = ", "

'then I use this to create it:
strInsertStatements(whileCounter) = insertString & s.Fields("CommunityName") & insertString2 & "80000001-1354637734" & insertString2 & "Charge for unit #" & r.Fields("LotUnit Number") & "-" & r.Fields("Account Number") & "-" & r.Fields("Owner ID") & insertString3 & "150" & insertString4 & "1" & insertString5

'then if it is the last unit for the community I set FQSaveToCache to false, otherwise to true and DoCmd.RunSQL:
Dim insrt As String
insrt = strInsertStatements(a_counter)
If a_counter = whileCounter - 1 Then
insrt = insrt & "1)"
MsgBox insrt
DoCmd.RunSQL insrt
Else
insrt = insrt & "0)"
MsgBox insrt
DoCmd.RunSQL insrt
End If


FQSaveToCache, when true, tells qodbc to store the previous insert in a buffer. Then when you insert something with FQSaveToCache=false it will batch insert the prior statements. I tried setting this to false for everthing, but no changes.

 

[pr2-eugin]

Okay Try this..

Insert Into InvoiceLine (CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-834207-002000009H001', 150, 1, 0)

I have not used Quickbooks, but I do not think there is any need to use single quotes for Table/Field names..

 

[polpoint]

Ok, when I do that (remove the quotes for the table & field names) I get "Could not find output table 'InvoiceLine'"

If I add the quotes to the table name:

Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-834207-002000009H001', 150, 1, 0)

then I get the same Syntax error in query. error.

Both of these statements work fine if I paste them into a pass-thru query.

Does the pass-thru have anything to do with it?

 

[denomc100]

Use the Debug.Print option to review your output
In VBA code - clt + G (opens Imeddiate window)

Change this line
strInsertStatements(whileCounter) = insertString & s.Fields("CommunityName") & insertString2 & "80000001-1354637734" & insertString2 & "Charge for unit #" & r.Fields("LotUnit Number") & "-" & r.Fields("Account Number") & "-" & r.Fields("Owner ID") & insertString3 & "150" & insertString4 & "1" & insertString5


to

Debug.Print insertString & s.Fields("CommunityName") & insertString2 & "80000001-1354637734" & insertString2 & "Charge for unit #" & r.Fields("LotUnit Number") & "-" & r.Fields("Account Number") & "-" & r.Fields("Owner ID") & insertString3 & "150" & insertString4 & "1" & insertString5

Then review the output in the Imediate window

 

[polpoint]

///ok got this:
Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-#####', 150, 1,
Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #305-#####', 150, 1,
Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #309-#####', 150, 1,
Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #404-#####', 150, 1,


Which looks correct. I have 4 insert statements corresponding to the 4 records to be inserted. It is missing the "1)" or "0)" that is added in the following if-else.

When I add the debug.print to just before the DoCmd.RunSQL I get this:

Insert Into "InvoiceLine" ( CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('Casa Grande Condominium', '80000001-1354637734', 'Charge for unit #207-#####', 150, 1, 0)

Which works if I copy and paste into a pass-thru query. but I still get Syntax Error in Query.

Thanks for your help with this frustrating problem, by the way.

 

[pr2-eugin]

Hello polpoint, I did not realise you had added more code.. after I posted.. anyway.. that is good.. you provided the code.. What I do not understand is why have you added so many variables for , "?? Just type them in?? Eliminating that...

[COLOR=Green]'While[/COLOR]
    Dim insertString As String
    insertString = "INSERT INTO InvoiceLine (CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES ('"
   [COLOR=Green] 'then I use this to create it:[/COLOR]
    strInsertStatements(whileCounter) = insertString & s.Fields("CommunityName") & "', '80000001-1354637734', 'Charge for unit #'" & r.Fields("LotUnit Number") & "-" & r.Fields("Account Number") & "-" & r.Fields("Owner ID") & ", 150, 1,"

   [COLOR=Green] 'then if it is the last unit for the community I set FQSaveToCache to false, otherwise to true and DoCmd.RunSQL:[/COLOR]
    Dim insrt As String
    insrt = strInsertStatements(a_counter)
    If a_counter = whileCounter - 1 Then
        insrt = insrt & "1)"
        MsgBox insrt
        DoCmd.RunSQL insrt
    Else
        insrt = insrt & "0)"
        MsgBox insrt
        DoCmd.RunSQL insrt
    End If
[COLOR=Green]'Loop[/COLOR]

Make sure that you are not passing text into numbers..