Why does this SQL query work in query designer but not in VBA for Access?

Question

When I execute the following two queries in Create > Query one after the other they work perfectly. When I attempt to execute the queries in VBA only the first one (invoiceline) works.

I know for a fact it is not the query itself that is the problem, it is the way that I am perhaps establishing the connection. I get the error invalid lexical when attempting to execute the query.

When pressing a button this query is executed correctly

buttonOne_Click()

Const adOpenStatic = 3
Const adLockOptimistic = 3


Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

sSQL = "INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('80002436-1519061496', 'Building permit 1', 1.00000, 1.00, '80000001-1478562826', 1)"

Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Invoice details were gathered!"
MsgBox sMsg

End Sub

This one fails with error

buttonTwo_Click()

Const adOpenStatic = 3
Const adLockOptimistic = 3


Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL




sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

sSQL = "INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2020#, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', #10/31/2020#, #10/01/2020#, '8000295C-1541711590', 'Memo Test', 0, '80000001-1478562826')"


Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Invoice was Sent to QuickBooks"
MsgBox sMsg

End Sub

The second one relies on the first one to finish, once again both queries work perfectly in the Query designer in Microsoft Access, but the second one fails when running in VBA SQL in a macro or procedure.

This is the error I see:

Run-time error '-2147217900 (80040e14)':

[QODBC] [sql syntax error] Expected lexical element not found: =)

UPDATE

I have tried all of these with no luck, any other suggestions?

01-10-2005

2005-01-10

01/10/2005

2005/01/10

Take the SQL over to SSMS and try to submit it directly to your SQL Server instance. You should get better diagnostics. — nicomp
– nicomp, Commented Jan 16, 2019 at 14:35
@Corion what is the proper way to submit the date ? Instead of using " # " ? — user10891568
– user10891568, Commented Jan 16, 2019 at 14:37
When run through the query window Access will use it's interpreter to handle the dates the correct way, when invoked in VBA you are passing the query directly to the ODBC connection which doesn't understand the Access syntax. — Minty
– Minty, Commented Jan 16, 2019 at 14:44
@Corion #9/23/2020# is correct ACCESS SQL usage for dates. The use of "# # " is how you indicate dates in Access. — Karlomanio
– Karlomanio, Commented Jan 16, 2019 at 15:53

Minty · Accepted Answer · 2019-01-16 16:22:31Z

1

Have a read here https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2638/44/qodbc-desktop-troubleshooting---how-to-use-date-and-dateadd-function-in-qodbc

The correct format appears to be {d 'YYYY-MM-DD'} There is a function provided to make a date the correct string;

Function:

Function fncqbDate(myDate As Date) As String
myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function

edited Jan 16, 2019 at 16:22

answered Jan 16, 2019 at 16:17

Minty

1,6261 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Parfait Over a year ago

Simply use Format: Format(Now(), "YYYY-MM-DD")

user10891568 Over a year ago

I just tried to submit the date as , {d'2005-09-23'},

user10891568 Over a year ago

Still no luck, same issue

Minty Over a year ago

Note there is a space between the d and the '

user10891568 Over a year ago

The problem is still the same, but quick side question, is it correct to have two separate sSQL = " " query statements for queries that are related? Or could have the two query statements under the same sSQL= " " ?

|

Parfait · Accepted Answer · 2019-01-16 17:33:45Z

As @Minty commented and posted, your dates are the issue.

When running queries inside the MS Access GUI such as with Query Designer, you are governed to the Access SQL dialect.
When connecting via ODBC driver to an external database, you are governed to the connecting database's SQL dialect, here being Quickbooks.

No two SQL dialects are ever the same but most attempt to conform to ANSI standards. Therefore, the same queries can fail between Query Designer and VBA.

MS Access

MS Access dates if sent literally, should be encapsulated with hashtags, #:

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber,
                     BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, 
                     BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, 
                     DueDate, ShipDate, ItemSalesTaxRefListID, [Memo], IsToBePrinted, 
                     CustomerSalesTaxCodeRefListID) 
VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2020#, '1', 'Brad Lamb', 
        '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', 
         #10/31/2020#, #10/01/2020#, '8000295C-1541711590', 
         'Memo Test', 0, '80000001-1478562826')

Alternatively, use CDate() to convert string to date:

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber,
                     BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, 
                     BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, 
                     DueDate, ShipDate, ItemSalesTaxRefListID, [Memo], IsToBePrinted, 
                     CustomerSalesTaxCodeRefListID) 
VALUES ('800001F6-1482536280', '8000001E-1478562986', CDate('9/23/2020'), '1', 'Brad Lamb', 
        '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', 
        CDate('10/31/2020'), CDate('10/01/2020'), '8000295C-1541711590', 
        'Memo Test', 0, '80000001-1478562826')

Quickbooks ODBC

In Quickbooks, you must adhere to its date requirement of {d 'YYYY-MM-DD'} or the functional form fncqbDate(). However, documentation on these methods is sparse to non-existent and can vary with ODBC driver versions.

sql = "INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, " _
       & "                  BillAddressAddr1, BillAddressAddr2, BillAddressCity,  " _
       & "                  BillAddressState, BillAddressPostalCode, BillAddressCountry,  " _
       & "                  IsPending, TermsRefListID, DueDate, ShipDate,  " _
       & "                  ItemSalesTaxRefListID, [Memo], IsToBePrinted,  " _
       & "                  CustomerSalesTaxCodeRefListID)  " _
       & " VALUES ('800001F6-1482536280', '8000001E-1478562986', {d '2020-09-23'}, '1',  " _
       & "         'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0,  " _ 
       & "         '80000002-1478562832', {d '2020-10-31'}, {d '2020-10-01'},  " _
       & "         '8000295C-1541711590', 'Memo Test', 0, '80000001-1478562826')"

Parameterization

With that said, even if above does not work, this is yet another good reason for parameterization, a programming industry standard for any application layer like VBA to run SQL. Parameterization facilitates data type mapping between client and server without the need of quote or other symbolic closures. ADO does support parameters with CreateParameter method inside an ADO Command object (not ADO recordset).

' PREPARED STATEMENT WITH PLACEHOLDERS (NO LITERAL DATA)
sql = "INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, " _
       & "                  BillAddressAddr1, BillAddressAddr2, BillAddressCity,  " _
       & "                  BillAddressState, BillAddressPostalCode, BillAddressCountry,  " _
       & "                  IsPending, TermsRefListID, DueDate, ShipDate,  " _
       & "                  ItemSalesTaxRefListID, [Memo], IsToBePrinted,  " _
       & "                  CustomerSalesTaxCodeRefListID)  " _
       & " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?," _ 
       & "         ?, ?, ?, ?, ?, ?, ?, ?, ?)"

' OPEN CONNECTION
Set oConnection = CreateObject("ADODB.Connection")   
oConnection.Open sConnectString

' INITIALIZE AND RUN COMMAND
Set oCmd = CreateObject("ADODB.Command")              ' NEW ADO OBJECT
With oCmd
   .ActiveConnection = oConnection
   .CommandText = sql
   .CommandType = adCmdText

   ' BIND PARAMETERS
   .Parameters.Append .CreateParameter("pm1", adVarChar, adParamInput, ,"800001F6-1482536280")
   .Parameters.Append .CreateParameter("pm2", adVarChar, adParamInput, ,"8000001E-1478562986") 
   .Parameters.Append .CreateParameter("pm3", adDate, adParamInput, , CDate("9/23/2020"), 
   .Parameters.Append .CreateParameter("pm4", adVarChar, adParamInput, ,"1") 
   .Parameters.Append .CreateParameter("pm5", adVarChar, adParamInput, ,"Brad Lamb")
   .Parameters.Append .CreateParameter("pm6", adVarChar, adParamInput, ,"1921 Appleseed Lane")
   .Parameters.Append .CreateParameter("pm7", adVarChar, adParamInput, ,"Bayshore")
   .Parameters.Append .CreateParameter("pm8", adVarChar, adParamInput, ,"CA")
   .Parameters.Append .CreateParameter("pm9", adVarChar, adParamInput, ,"94326")
   .Parameters.Append .CreateParameter("pm10", adVarChar, adParamInput, ,"USA")
   .Parameters.Append .CreateParameter("pm11", adInteger, adParamInput, , 0)
   .Parameters.Append .CreateParameter("pm12", adVarChar, adParamInput, ,"80000002-1478562832")
   .Parameters.Append .CreateParameter("pm13", adDate, adParamInput, , CDate("10/31/2020"))
   .Parameters.Append .CreateParameter("pm14", adDate, adParamInput, , CDate("10/01/2020")) 
   .Parameters.Append .CreateParameter("pm15", adVarChar, adParamInput, ,"8000295C-1541711590")
   .Parameters.Append .CreateParameter("pm16", adVarChar, adParamInput, ,"Memo Test")
   .Parameters.Append .CreateParameter("pm17", adInteger, adParamInput, ,0)
   .Parameters.Append .CreateParameter("pm18", adVarChar, adParamInput, ,"80000001-1478562826")

   ' RUN PARAMETERIZED QUERY
   .Execute
End With

oConnection.Close
Set oCmd = Nothing: Set oConnection = Nothing

Ok so I tried your code right under "Quickbooks ODBC" it seemed to work because now I get a different error: [QODBC] Not supported - At least one Line record is required. Please insert Child/Detail record(s) before inserting Parent/Header record. For more details please visit:godbc.comilinks/2953 "
The issue is that InvoiceLine, the first piece of code I posted is the child its looking for
I know for a fact that both should work because once again it did in Query Designer
Usually, the parent table rows are inserted before related children as this appears to be a relationships issue in Quickbooks. Did you go to their url: support.flexquarters.com/esupport/index.php?/Knowledgebase/…? Like the Query Designer runs, you will also need to run both append queries but think carefully the order. Possibly too this query relates to other tables like Customer (which should exist prior to Invoice). Even more, your earlier test runs may invalidate next test runs since keys may need to be unique. Try testing on new invoice example.
It should just append itself once the first query runs, right?

Parfait · Accepted Answer · 2019-01-17 14:25:00Z

0

If this is an Access application, I would recommend that instead of rewriting the query into sql/vba code to instead take another approach: leave the working query as a query object and instead use vba to trigger it:

DoCmd.OpenQuery "QueryName"

This has the advantage of having that query object existing in the navigation pane so that you can run it manually for testing - without having to run any vba. It is a much simpler debug strategy.

edited Jan 17, 2019 at 14:25

Parfait

108k19 gold badges102 silver badges138 bronze badges

answered Jan 16, 2019 at 23:16

Cahaba Data

6321 gold badge4 silver badges4 bronze badges

4 Comments

user10891568 Over a year ago

I’d consider that, but I also using a form with text boxes for input in the query. The code I am using in my post includes only static inputs I have made up for the example. Can what you are proposing also include inputs from a form?

Parfait Over a year ago

@RichardWalton, you can point values in a saved query to form controls: Forms!myForm!mycontrol (no quotes or other enclosures). Then call DoCmd.OpenQuery. Alternatively, you can pass parameters to a saved query using QueryDefs but this uses DAO (not ADO) implementation. Finally, you can use ADO as I proposed above, passing dynamic values into CreateParameter calls (last argument). Did you attempt this? StackOverflow has many, many posts on SQL parameterization in VBA.

user10891568 Over a year ago

@Parfait I got the saved query to work, is there a way to make the all of the prompts that appear, auto accept (press yes on its own)?

Parfait Over a year ago

It should not prompt you if the form is open to screen for controls to be available by query. If form is open, then you mis-typed the absolute form control path. Do note subforms have different paths. Consider bookmarking this page: access.mvps.org/access/forms/frm0031.htm (do not use Me., the relative reference). And remember no quotes, hashtags, or other enclosures.

Collectives™ on Stack Overflow

Why does this SQL query work in query designer but not in VBA for Access?

3 Answers 3

6 Comments

MS Access

Quickbooks ODBC

Parameterization

6 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

MS Access

Quickbooks ODBC

Parameterization

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related