QB 2008 Prem, QODBC V9
For some of my sales i need to build assembly items. I wrote a VBA script that extracts the assembly items from a sales receipt or invoice and puts it on an Excel. Next I process the Excel and do an INSERT INTO BuildAssembly. When building the assembly I want to use the sales receipt date as the transaction date for the assembly build.
Everything works fine if I don't use the transaction date or hard code it in the SQL string (see code). The problem is when i use the transaction date as a parameter in the SQL string and execute it. I get an Run-time error '-2147217887 (80040e21)' Automation error.
In debug mode I see that my TxnDate (defined as String) has the correct format of {d'2008-12-23'}, nevertheless, I gt the run-time error.
I cannot figure out what's wrong with my SQL string and would appreciate if somebody could have a look. I have put several SQL strings as comment to show how the parameters are passed. The one with the TxnDate is giving me the grieve.
Here is the code
Sub ADOExcelQuickBooksBuildAssembly() Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3
Dim oConnection Dim oRecordset Dim sSQLStr1 As String Dim lRow As Long Dim sItemName As String Dim dBuildQty As Double Dim sRefNumber As String Dim sTxnDate As String Dim sDate As String Dim iRowCount As Integer Dim sMemo As String Set oConnection = CreateObject("ADODB.Connection") Set oRecordset = CreateObject("ADODB.Recordset") ' Connect to server and get table oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2" 'Update ItemInventory for each row For lRow = 2 To ActiveSheet.UsedRange.Rows.Count 'Set build assembly values sDate = Format(ActiveSheet.Cells(lRow, 1), "yyyy-mm-dd") sTxnDate = "{d'" & sDate & "'}" sRefNumber = ActiveSheet.Cells(lRow, 2) sItemName = ActiveSheet.Cells(lRow, 3) dBuildQty = ActiveSheet.Cells(lRow, 5) sMemo = "QB Sales #: " & ActiveSheet.Cells(lRow, 2) s = "INSERT INTO BuildAssembly (TxnDate) VALUES ({d'" & sDate & "'})" sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, TxnDate, RefNumber, Memo, QuantityToBuild) VALUES ('" & sItemName & "', '" & sTxnDate & "', '" & sRefNumber & "', '" & sMemo & "', " & dBuildQty & ")" 'sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, RefNumber, QuantityToBuild, Memo) VALUES ('" & sItemName & "', '" & sRefNumber & "', " & dBuildQty & ", '" & sMemo & "')" 'sSQLStr1 = "INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, TxnDate, RefNumber, QuantityToBuild, Memo) VALUES ('" & sItemName & "', {d'2008-12-20'}, '" & sRefNumber & "', " & dBuildQty & ", '" & sMemo & "')" 'Now update record oRecordset.Open sSQLStr1, oConnection, adOpenStatic, adLockOptimistic Next
' Stop application updating Application.ScreenUpdating = False ' Tidy up oConnection.Close Set oConnection = Nothing End Sub
|