vba - 第一个成功后如何在 VBA 中运行这个 SQL 脚本？

Question

我目前有这个 SQL 查询：

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

成功后，我希望运行此查询：

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')

我可以在两个查询之间添加什么？也许是“如果”“那么”语句？

更新

按顺序运行它们确实有效，但我似乎看到了另一个问题：

在 Access 中运行第二个查询时，一切正常，但是在 VBA SQL 中运行第二个查询时，我收到错误：

“ [QODBC] [sql 语法错误] 未找到预期的词法元素”

所有值都是硬编码的，所以我不明白问题是什么？

代码：

Private Sub send_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,IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2005#, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', #10/31/2005#, #10/01/2005#, '8000295C-1541711590', 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

标签： vbams-accessqodbc