Problem Description:

Ran this SQL to move an invoice from one Customer to another:

Update invoice set CustomerRefListID = '1500' where TxnNumber = 8453

It took about 15+ minutes, and the status dialog showed it waiting on QuickBooks as it went through all 2,500 customers - but it took over 15 minutes to complete! I have a situation where moving an invoice to another Customer may occur several times in a batch of invoices. Are there additional conditions I can put in the WHERE clause that would speed this up?

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

If you are executing an SQL statement for the first time on this table, QODBC has built the optimizer before processing the SQL statement. "Waiting on QuickBooks" may take longer than usual.

Please run the SQL statement again, and if you are still facing the same issue, please try the following options.

Make use of the index fields, such as TxnID or TxnDate, to filter the number of records scanned.

Update invoice set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

or

Update invoice set CustomerRefListID = '1500' where TxnDate>={d'2022-11-10'} and  TxnDate<={d'2022-11-15'} and  TxnNumber = 8453

If you are still facing the issue, please try using the UNOPTIMIZED keyword.

Update invoice UNOPTIMIZED set CustomerRefListID = '1500' where TxnID='ValueOfYourTxnID'

Problem Description:

We have low performance in SQL Server Linked Server using QREMOTE.

For example, the below statement takes less than 2 seconds in the QODBC 64-bit Test tool and more than 2 minutes in our linked server:
UPDATE Customer SET JobTitle = 'Job3' WHERE ListID='8000000F-1466614340'.

In SQL Server, the statement seems to scan the whole table to be executed (14000 records)

We are running QuickBooks and SQL Server on the same machine on a cloud server. All drivers are 64bits. The same system DSN is used in Test Tool and in SQL Linked server: QuickBooks Data 64-bit QRemote.

Solution:

Please make sure Optimizer is enabled.  - https://qodbc.com/links/2358

Update QREMOTE...customer set JobTitle = 'Job' where ListID='8000000F-1466614340'

When you use the above query, SQL Server will fetch all the data from the Customer table. SQL Server will then apply the filter of List ID.

SQL Server will generate an update statement from the derived result and send it to QODBC/QRemote.

Thus, for a single record update, the entire table is scanned.

To avoid this, use OpenQuery and select only the record you want to update.

Please use the Index fields in the "select" and "update" clauses.

Example:
update openquery (QRemote, 'select ListID, JobTitle from Customer where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'

Note: Here, we are using ListID (the index field) in the select statement and the OpenQuery update statement.

If you are still facing the issue, please use the UNOPTIMIZED keyword.

update openquery (QRemote, 'select ListID, JobTitle from Customer UNOPTIMIZED where ListID=''8000000F-1466614340''') set JobTitle = 'Job' where ListID='8000000F-1466614340'

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Please refer to how to configure QRemote.

Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote."

Also, Refer:

QODBC Reports Architecture Mismatch Problem When Connecting

Problem Description:

I am trying to update records through the select statement. But it is not working.

UPDATE Customer SET CustomFieldProjectNumber = i.JobNumber FROM ( SELECT ListID, {fn SUBSTRING("Name", 1, 5)} AS JobNumber FROM Customer WHERE TimeCreated > {ts '2016-01-01 00:00:00.000'} ) i WHERE i.ListID = Customer.ListID

I am getting the "Unexpected extra token: (" error in QODBC Test Tool.  

Any Assistance Is Appreciated.

Solution:

You can execute the query below in MS Access linked table without any issue.

Please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 32-bit

You need to link the Customer table in MS Access & execute the query in the Access query editor.

MS Access>> create tab >> Query Design >> Paste query >> Execute query using Run button

Update Customer qe inner join Customer de on qe.ListID = de.ListID set qe.CustomFieldProjectNumber = MID (de.Name,1,5) WHERE qe.TimeCreated >#2016-01-01 00:00:01#

Problem Description:

Good Day To You. Using This Sp_report And Having Some Problems With The Column Layout. Command Text Is As Follows: Sp_report Profitandlossbudgetvsactual Show Account name, Account number, Amount, Budget Parameters Fiscalyear=2015, Classfilterfullnames =' health Center,' 'administrative.'

I'm Getting 12 Columns, As One Would Expect, But The 12th Column Is The Sum Of The Previous 11 Columns, Not The 12th Month. This Is True For Both The Amount And Budget Segments. Also, The Budget Amount For The 11th Month is exactly one-half of the Actual Budgeted Amount. I Have Run The Actual Report, And The Totals For Each Month Are Correct For Both the Amount And Budget Until The Last Two Months In The report.

Also, if I replace the FiscalYear=2015, with DateMacro=’ThisYear’ I get this



Any Assistance Is Appreciated.  

Solution:

Please ensure that you are passing the same parameter in the report query you passed in QuickBooks UI.

Also, for the budget report, the Fiscal Year parameter is required. Without it, a report will not execute. So you need to keep the Fiscal Year parameter & add other date parameters.

For Example:

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, DateFrom={d'2015-10-01'}, DateTo={d'2016-09-30'}, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

First, you need to create a Linked Server in SQL Server.

For 32-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote.

For 64-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote.

I have already created a linked server, "QRemote."

After creating a Linked Server, you need to create a stored procedure, which will read data from the Linked Server table & write it into the SQL Server local table.

For storing Linked Server data in the local database. It would be best if you used the INSERT INTO SELECT query as below.

For Example:

SELECT * INTO SQL_Local_TableName FROM YourLinkedServerName...TableName;

First, we will add a single table in the local database. After that, we will create a stored procedure for all tables

We will add an Account table in the local database with the table name as Tbl_Account.

SELECT * INTO Tbl_Account FROM QRemote...Account

The Tbl_Account table is created for the local database with 105 records.

Now we will query the local table Tbl_Account.
Select * from Tbl_Account
Data is available in the local database table.

Now, we will create a stored procedure that will read data from the Linked Server table & write it into the SQL Server local table.

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand your database, and then expand Programmability.

Right-click Stored Procedures and then click the New Stored Procedure.

We will create the stored procedure named Import_QODBC_Tables, which will check in the local database if the table exists. It will drop the table & recreate the table & read all the linked server tables & create a new table in a local database.

After writing the stored procedure, click on Execute to create the stored procedure.

Stored Procedure Created.

Stored Procedure Code:

================================================================

USE [QODBC]
GO
/****** Object:  StoredProcedure [dbo].[Import_QODBC_Tables]    Script Date: 07/29/2016 00:28:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[Import_QODBC_Tables]


AS
begin

IF OBJECT_ID (N'Tbl_Account', N'U') IS NOT NULL
 
Drop table Tbl_Account
   
SELECT * INTO Tbl_Account    FROM QRemote...Account

IF OBJECT_ID (N'Tbl_AccountTaxLineInfo', N'U') IS NOT NULL
 
Drop table Tbl_AccountTaxLineInfo

SELECT * INTO    Tbl_AccountTaxLineInfo    FROM    QRemote...AccountTaxLineInfo

IF OBJECT_ID (N'Tbl_ARRefundCreditCard', N'U') IS NOT NULL
 
Drop table Tbl_ARRefundCreditCard

SELECT * INTO    Tbl_ARRefundCreditCard    FROM    QRemote...ARRefundCreditCard

IF OBJECT_ID (N'Tbl_ARRefundCreditCardRefundAppliedTo', N'U') IS NOT NULL
 
Drop table Tbl_ARRefundCreditCardRefundAppliedTo

SELECT * INTO    Tbl_ARRefundCreditCardRefundAppliedTo    FROM    QRemote...ARRefundCreditCardRefundAppliedTo

IF OBJECT_ID (N'Tbl_BarCode', N'U') IS NOT NULL
 
Drop table Tbl_BarCode

SELECT * INTO    Tbl_BarCode    FROM    QRemote...BarCode

IF OBJECT_ID (N'Tbl_Bill', N'U') IS NOT NULL
 
Drop table Tbl_Bill

SELECT * INTO    Tbl_Bill    FROM    QRemote...Bill

IF OBJECT_ID (N'Tbl_BillExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_BillExpenseLine

SELECT * INTO    Tbl_BillExpenseLine    FROM    QRemote...BillExpenseLine

IF OBJECT_ID (N'Tbl_BillItemLine', N'U') IS NOT NULL
 
Drop table Tbl_BillItemLine

SELECT * INTO    Tbl_BillItemLine    FROM    QRemote...BillItemLine

IF OBJECT_ID (N'Tbl_BillLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillLinkedTxn

SELECT * INTO    Tbl_BillLinkedTxn    FROM    QRemote...BillLinkedTxn

IF OBJECT_ID (N'Tbl_BillingRate', N'U') IS NOT NULL
 
Drop table Tbl_BillingRate

SELECT * INTO    Tbl_BillingRate    FROM    QRemote...BillingRate

IF OBJECT_ID (N'Tbl_BillingRateLine', N'U') IS NOT NULL
 
Drop table Tbl_BillingRateLine

SELECT * INTO    Tbl_BillingRateLine    FROM    QRemote...BillingRateLine

IF OBJECT_ID (N'Tbl_BillPaymentCheck', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheck

SELECT * INTO    Tbl_BillPaymentCheck    FROM    QRemote...BillPaymentCheck

IF OBJECT_ID (N'Tbl_BillPaymentCheckLine', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheckLine

SELECT * INTO    Tbl_BillPaymentCheckLine    FROM    QRemote...BillPaymentCheckLine

IF OBJECT_ID (N'Tbl_BillPaymentCheckLineLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCheckLineLinkedTxn

SELECT * INTO    Tbl_BillPaymentCheckLineLinkedTxn    FROM    QRemote...BillPaymentCheckLineLinkedTxn

IF OBJECT_ID (N'Tbl_BillPaymentCreditCard', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCard

SELECT * INTO    Tbl_BillPaymentCreditCard    FROM    QRemote...BillPaymentCreditCard

IF OBJECT_ID (N'Tbl_BillPaymentCreditCardLine', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCardLine

SELECT * INTO    Tbl_BillPaymentCreditCardLine    FROM    QRemote...BillPaymentCreditCardLine

IF OBJECT_ID (N'Tbl_BillPaymentCreditCardLineLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_BillPaymentCreditCardLineLinkedTxn

SELECT * INTO    Tbl_BillPaymentCreditCardLineLinkedTxn    FROM    QRemote...BillPaymentCreditCardLineLinkedTxn

IF OBJECT_ID (N'Tbl_BillToPay', N'U') IS NOT NULL
 
Drop table Tbl_BillToPay

SELECT * INTO    Tbl_BillToPay    FROM    QRemote...BillToPay

IF OBJECT_ID (N'Tbl_BuildAssembly', N'U') IS NOT NULL
 
Drop table Tbl_BuildAssembly

SELECT * INTO    Tbl_BuildAssembly    FROM    QRemote...BuildAssembly

IF OBJECT_ID (N'Tbl_BuildAssemblyComponentItemLine', N'U') IS NOT NULL
 
Drop table Tbl_BuildAssemblyComponentItemLine

SELECT * INTO    Tbl_BuildAssemblyComponentItemLine    FROM    QRemote...BuildAssemblyComponentItemLine

IF OBJECT_ID (N'Tbl_Charge', N'U') IS NOT NULL
 
Drop table Tbl_Charge

SELECT * INTO    Tbl_Charge    FROM    QRemote...Charge

IF OBJECT_ID (N'Tbl_ChargeLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_ChargeLinkedTxn

SELECT * INTO    Tbl_ChargeLinkedTxn    FROM    QRemote...ChargeLinkedTxn

IF OBJECT_ID (N'Tbl_Check', N'U') IS NOT NULL
 
Drop table Tbl_Check

SELECT * INTO    Tbl_Check    FROM    QRemote..."Check"

IF OBJECT_ID (N'Tbl_CheckApplyCheckToTxn', N'U') IS NOT NULL
 
Drop table Tbl_CheckApplyCheckToTxn

SELECT * INTO    Tbl_CheckApplyCheckToTxn    FROM    QRemote...CheckApplyCheckToTxn

IF OBJECT_ID (N'Tbl_CheckExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CheckExpenseLine

SELECT * INTO    Tbl_CheckExpenseLine    FROM    QRemote...CheckExpenseLine

IF OBJECT_ID (N'Tbl_CheckItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CheckItemLine

SELECT * INTO    Tbl_CheckItemLine    FROM    QRemote...CheckItemLine

IF OBJECT_ID (N'Tbl_Class', N'U') IS NOT NULL
 
Drop table Tbl_Class

SELECT * INTO    Tbl_Class    FROM    QRemote...Class

IF OBJECT_ID (N'Tbl_ClearedStatus', N'U') IS NOT NULL
 
Drop table Tbl_ClearedStatus

SELECT * INTO    Tbl_ClearedStatus    FROM    QRemote...ClearedStatus

IF OBJECT_ID (N'Tbl_Company', N'U') IS NOT NULL
 
Drop table Tbl_Company

SELECT * INTO    Tbl_Company    FROM    QRemote...Company

IF OBJECT_ID (N'Tbl_CompanyActivity', N'U') IS NOT NULL
 
Drop table Tbl_CompanyActivity

SELECT * INTO    Tbl_CompanyActivity    FROM    QRemote...CompanyActivity

IF OBJECT_ID (N'Tbl_CreditCardCharge', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCharge

SELECT * INTO    Tbl_CreditCardCharge    FROM    QRemote...CreditCardCharge

IF OBJECT_ID (N'Tbl_CreditCardChargeExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardChargeExpenseLine

SELECT * INTO    Tbl_CreditCardChargeExpenseLine    FROM    QRemote...CreditCardChargeExpenseLine

IF OBJECT_ID (N'Tbl_CreditCardChargeItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardChargeItemLine

SELECT * INTO    Tbl_CreditCardChargeItemLine    FROM    QRemote...CreditCardChargeItemLine

IF OBJECT_ID (N'Tbl_CreditCardCredit', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCredit

SELECT * INTO    Tbl_CreditCardCredit    FROM    QRemote...CreditCardCredit

IF OBJECT_ID (N'Tbl_CreditCardCreditExpenseLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCreditExpenseLine

SELECT * INTO    Tbl_CreditCardCreditExpenseLine    FROM    QRemote...CreditCardCreditExpenseLine

IF OBJECT_ID (N'Tbl_CreditCardCreditItemLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditCardCreditItemLine

SELECT * INTO    Tbl_CreditCardCreditItemLine    FROM    QRemote...CreditCardCreditItemLine

IF OBJECT_ID (N'Tbl_CreditMemo', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemo

SELECT * INTO    Tbl_CreditMemo    FROM    QRemote...CreditMemo

IF OBJECT_ID (N'Tbl_CreditMemoLine', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemoLine

SELECT * INTO    Tbl_CreditMemoLine    FROM    QRemote...CreditMemoLine

IF OBJECT_ID (N'Tbl_CreditMemoLinkedTxn', N'U') IS NOT NULL
 
Drop table Tbl_CreditMemoLinkedTxn

SELECT * INTO    Tbl_CreditMemoLinkedTxn    FROM    QRemote...CreditMemoLinkedTxn

IF OBJECT_ID (N'Tbl_Currency', N'U') IS NOT NULL
 
Drop table Tbl_Currency

SELECT * INTO    Tbl_Currency    FROM    QRemote...Currency

IF OBJECT_ID (N'Tbl_Customer', N'U') IS NOT NULL
 
Drop table Tbl_Customer

SELECT * INTO    Tbl_Customer    FROM    QRemote...Customer

IF OBJECT_ID (N'Tbl_CustomerAddtionalNote', N'U') IS NOT NULL
 
Drop table Tbl_CustomerAddtionalNote

SELECT * INTO    Tbl_CustomerAddtionalNote    FROM    QRemote...CustomerAddtionalNote

IF OBJECT_ID (N'Tbl_CustomerContacts', N'U') IS NOT NULL
 
Drop table Tbl_CustomerContacts

SELECT * INTO    Tbl_CustomerContacts    FROM    QRemote...CustomerContacts

IF OBJECT_ID (N'Tbl_CustomerShipToAddress', N'U') IS NOT NULL
 
Drop table Tbl_CustomerShipToAddress

SELECT * INTO    Tbl_CustomerShipToAddress    FROM    QRemote...CustomerShipToAddress

IF OBJECT_ID (N'Tbl_CustomerMsg', N'U') IS NOT NULL
 
Drop table Tbl_CustomerMsg

SELECT * INTO    Tbl_CustomerMsg    FROM    QRemote...CustomerMsg

IF OBJECT_ID (N'Tbl_CustomerType', N'U') IS NOT NULL
 
Drop table Tbl_CustomerType

SELECT * INTO    Tbl_CustomerType    FROM    QRemote...CustomerType

IF OBJECT_ID (N'Tbl_CustomField', N'U') IS NOT NULL
 
Drop table Tbl_CustomField

SELECT * INTO    Tbl_CustomField    FROM    QRemote...CustomField

IF OBJECT_ID (N'Tbl_DateDrivenTerms', N'U') IS NOT NULL
 
Drop table Tbl_DateDrivenTerms

SELECT * INTO    Tbl_DateDrivenTerms    FROM    QRemote...DateDrivenTerms

IF OBJECT_ID (N'Tbl_Deposit', N'U') IS NOT NULL
 
Drop table Tbl_Deposit

SELECT * INTO    Tbl_Deposit    FROM    QRemote...Deposit

IF OBJECT_ID (N'Tbl_DepositLine', N'U') IS NOT NULL
 
Drop table Tbl_DepositLine

SELECT * INTO    Tbl_DepositLine    FROM    QRemote...DepositLine

IF OBJECT_ID (N'Tbl_Employee', N'U') IS NOT NULL
 
Drop table Tbl_Employee

SELECT * INTO    Tbl_Employee    FROM    QRemote...Employee

IF OBJECT_ID (N'Tbl_EmployeeAddtionalNote', N'U') IS NOT NULL
 
Drop table Tbl_EmployeeAddtionalNote

SELECT * INTO    Tbl_EmployeeAddtionalNote    FROM    QRemote...EmployeeAddtionalNote

IF OBJECT_ID (N'Tbl_EmployeeEarning', N'U') IS NOT NULL
 
Drop table Tbl_EmployeeEarning

SELECT * INTO    Tbl_EmployeeEarning    FROM    QRemote...EmployeeEarning

IF OBJECT_ID (N'Tbl_Entity', N'U') IS NOT NULL
 
Drop table Tbl_Entity

SELECT * INTO    Tbl_Entity    FROM    QRemote...Entity

IF OBJECT_ID (N'Tbl_EntityAdditionalNote', N'U') IS NOT NULL
 
Drop table Tbl_EntityAdditionalNote

SELECT * INTO    Tbl_EntityAdditionalNote    FROM    QRemote...EntityAdditionalNote

IF OBJECT_ID (N'Tbl_EntityContacts', N'U') IS NOT NULL
 
Drop table Tbl_EntityContacts

SELECT * INTO    Tbl_EntityContacts    FROM    QRemote...EntityContacts

IF OBJECT_ID (N'Tbl_EntityShipToAddress', N'U') IS NOT NULL
 
Drop table Tbl_EntityShipToAddress

SELECT * INTO    Tbl_EntityShipToAddress    FROM    QRemote...EntityShipToAddress

IF OBJECT_ID (N'Tbl_Estimate', N'U') IS NOT NULL
 
Drop table Tbl_Estimate

SELECT * INTO    Tbl_Estimate    FROM    QRemote...Estimate

IF OBJECT_ID (N'Tbl_EstimateLine', N'U') IS NOT NULL
 
Drop table Tbl_EstimateLine

SELECT * INTO    Tbl_EstimateLine    FROM    QRemote...EstimateLine

IF OBJECT_ID (N'Tbl_EstimateLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_EstimateLinkedTxn
SELECT * INTO    Tbl_EstimateLinkedTxn    FROM    QRemote...EstimateLinkedTxn

IF OBJECT_ID (N'Tbl_Host', N'U') IS NOT NULL
Drop table Tbl_Host
SELECT * INTO    Tbl_Host    FROM    QRemote...Host

IF OBJECT_ID (N'Tbl_HostMetaData', N'U') IS NOT NULL
Drop table Tbl_HostMetaData
SELECT * INTO    Tbl_HostMetaData    FROM    QRemote...HostMetaData

IF OBJECT_ID (N'Tbl_HostSupportedVersions', N'U') IS NOT NULL
Drop table Tbl_HostSupportedVersions
SELECT * INTO    Tbl_HostSupportedVersions    FROM    QRemote...HostSupportedVersions

IF OBJECT_ID (N'Tbl_InventoryAdjustment', N'U') IS NOT NULL
Drop table Tbl_InventoryAdjustment
SELECT * INTO    Tbl_InventoryAdjustment    FROM    QRemote...InventoryAdjustment

IF OBJECT_ID (N'Tbl_InventoryAdjustmentLine', N'U') IS NOT NULL
Drop table Tbl_InventoryAdjustmentLine
SELECT * INTO    Tbl_InventoryAdjustmentLine    FROM    QRemote...InventoryAdjustmentLine

IF OBJECT_ID (N'Tbl_InventorySite', N'U') IS NOT NULL
Drop table Tbl_InventorySite
SELECT * INTO    Tbl_InventorySite    FROM    QRemote...InventorySite

IF OBJECT_ID (N'Tbl_Invoice', N'U') IS NOT NULL
Drop table Tbl_Invoice
SELECT * INTO    Tbl_Invoice    FROM    QRemote...Invoice

IF OBJECT_ID (N'Tbl_InvoiceLine', N'U') IS NOT NULL
Drop table Tbl_InvoiceLine
SELECT * INTO    Tbl_InvoiceLine    FROM    QRemote...InvoiceLine

IF OBJECT_ID (N'Tbl_InvoiceLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_InvoiceLinkedTxn
SELECT * INTO    Tbl_InvoiceLinkedTxn    FROM    QRemote...InvoiceLinkedTxn

IF OBJECT_ID (N'Tbl_Item', N'U') IS NOT NULL
Drop table Tbl_Item
SELECT * INTO    Tbl_Item    FROM    QRemote...Item

IF OBJECT_ID (N'Tbl_ItemAssembliesCanBuild', N'U') IS NOT NULL
Drop table Tbl_ItemAssembliesCanBuild
SELECT * INTO    Tbl_ItemAssembliesCanBuild    FROM    QRemote...ItemAssembliesCanBuild where ItemInventoryAssemblyRefFullName='assembly item' and TxnDate={d'2018-01-01'}

IF OBJECT_ID (N'Tbl_ItemDiscount', N'U') IS NOT NULL
Drop table Tbl_ItemDiscount
SELECT * INTO    Tbl_ItemDiscount    FROM    QRemote...ItemDiscount

IF OBJECT_ID (N'Tbl_ItemFixedAsset', N'U') IS NOT NULL
Drop table Tbl_ItemFixedAsset
SELECT * INTO    Tbl_ItemFixedAsset    FROM    QRemote...ItemFixedAsset

IF OBJECT_ID (N'Tbl_ItemGroup', N'U') IS NOT NULL
Drop table Tbl_ItemGroup
SELECT * INTO    Tbl_ItemGroup    FROM    QRemote...ItemGroup

IF OBJECT_ID (N'Tbl_ItemGroupLine', N'U') IS NOT NULL
Drop table Tbl_ItemGroupLine
SELECT * INTO    Tbl_ItemGroupLine    FROM    QRemote...ItemGroupLine

IF OBJECT_ID (N'Tbl_ItemInventory', N'U') IS NOT NULL
Drop table Tbl_ItemInventory
SELECT * INTO    Tbl_ItemInventory    FROM    QRemote...ItemInventory

IF OBJECT_ID (N'Tbl_ItemInventoryAssembly', N'U') IS NOT NULL
Drop table Tbl_ItemInventoryAssembly
SELECT * INTO    Tbl_ItemInventoryAssembly    FROM    QRemote...ItemInventoryAssembly

IF OBJECT_ID (N'Tbl_ItemInventoryAssemblyLine', N'U') IS NOT NULL
Drop table Tbl_ItemInventoryAssemblyLine
SELECT * INTO    Tbl_ItemInventoryAssemblyLine    FROM    QRemote...ItemInventoryAssemblyLine

IF OBJECT_ID (N'Tbl_ItemNonInventory', N'U') IS NOT NULL
Drop table Tbl_ItemNonInventory
SELECT * INTO    Tbl_ItemNonInventory    FROM    QRemote...ItemNonInventory

IF OBJECT_ID (N'Tbl_ItemOtherCharge', N'U') IS NOT NULL
Drop table Tbl_ItemOtherCharge
SELECT * INTO    Tbl_ItemOtherCharge    FROM    QRemote...ItemOtherCharge

IF OBJECT_ID (N'Tbl_ItemPayment', N'U') IS NOT NULL
Drop table Tbl_ItemPayment
SELECT * INTO    Tbl_ItemPayment    FROM    QRemote...ItemPayment

IF OBJECT_ID (N'Tbl_ItemReceipt', N'U') IS NOT NULL
Drop table Tbl_ItemReceipt
SELECT * INTO    Tbl_ItemReceipt    FROM    QRemote...ItemReceipt

IF OBJECT_ID (N'Tbl_ItemReceiptExpenseLine', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptExpenseLine
SELECT * INTO    Tbl_ItemReceiptExpenseLine    FROM    QRemote...ItemReceiptExpenseLine

IF OBJECT_ID (N'Tbl_ItemReceiptItemLine', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptItemLine
SELECT * INTO    Tbl_ItemReceiptItemLine    FROM    QRemote...ItemReceiptItemLine

IF OBJECT_ID (N'Tbl_ItemReceiptLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_ItemReceiptLinkedTxn
SELECT * INTO    Tbl_ItemReceiptLinkedTxn    FROM    QRemote...ItemReceiptLinkedTxn

IF OBJECT_ID (N'Tbl_ItemSalesTax', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTax
SELECT * INTO    Tbl_ItemSalesTax    FROM    QRemote...ItemSalesTax

IF OBJECT_ID (N'Tbl_ItemSalesTaxGroup', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTaxGroup
SELECT * INTO    Tbl_ItemSalesTaxGroup    FROM    QRemote...ItemSalesTaxGroup

IF OBJECT_ID (N'Tbl_ItemSalesTaxGroupLine', N'U') IS NOT NULL
Drop table Tbl_ItemSalesTaxGroupLine
SELECT * INTO    Tbl_ItemSalesTaxGroupLine    FROM    QRemote...ItemSalesTaxGroupLine

IF OBJECT_ID (N'Tbl_ItemSites', N'U') IS NOT NULL
Drop table Tbl_ItemSites
SELECT * INTO    Tbl_ItemSites    FROM    QRemote...ItemSites

IF OBJECT_ID (N'Tbl_ItemService', N'U') IS NOT NULL
Drop table Tbl_ItemService
SELECT * INTO    Tbl_ItemService    FROM    QRemote...ItemService

IF OBJECT_ID (N'Tbl_ItemSubtotal', N'U') IS NOT NULL
Drop table Tbl_ItemSubtotal
SELECT * INTO    Tbl_ItemSubtotal    FROM    QRemote...ItemSubtotal

IF OBJECT_ID (N'Tbl_JobType', N'U') IS NOT NULL
Drop table Tbl_JobType
SELECT * INTO    Tbl_JobType    FROM    QRemote...job type

IF OBJECT_ID (N'Tbl_JournalEntry', N'U') IS NOT NULL
Drop table Tbl_JournalEntry
SELECT * INTO    Tbl_JournalEntry    FROM    QRemote...JournalEntry

IF OBJECT_ID (N'Tbl_JournalEntryCreditLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryCreditLine
SELECT * INTO    Tbl_JournalEntryCreditLine    FROM    QRemote...JournalEntryCreditLine

IF OBJECT_ID (N'Tbl_JournalEntryDebitLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryDebitLine
SELECT * INTO    Tbl_JournalEntryDebitLine    FROM    QRemote...JournalEntryDebitLine

IF OBJECT_ID (N'Tbl_JournalEntryLine', N'U') IS NOT NULL
Drop table Tbl_JournalEntryLine
SELECT * INTO    Tbl_JournalEntryLine    FROM    QRemote...JournalEntryLine

IF OBJECT_ID (N'Tbl_ListDeleted', N'U') IS NOT NULL
Drop table Tbl_ListDeleted
SELECT * INTO    Tbl_ListDeleted    FROM    QRemote...ListDeleted

IF OBJECT_ID (N'Tbl_OtherName', N'U') IS NOT NULL
Drop table Tbl_OtherName
SELECT * INTO    Tbl_OtherName    FROM    QRemote... another name

IF OBJECT_ID (N'Tbl_PaymentMethod', N'U') IS NOT NULL
Drop table Tbl_PaymentMethod
SELECT * INTO    Tbl_PaymentMethod    FROM    QRemote...payment method

IF OBJECT_ID (N'Tbl_PayrollItemNonWage', N'U') IS NOT NULL
Drop table Tbl_PayrollItemNonWage
SELECT * INTO    Tbl_PayrollItemNonWage    FROM    QRemote...PayrollItemNonWage

IF OBJECT_ID (N'Tbl_PayrollItemWage', N'U') IS NOT NULL
Drop table Tbl_PayrollItemWage
SELECT * INTO    Tbl_PayrollItemWage    FROM    QRemote...PayrollItemWage

IF OBJECT_ID (N'Tbl_Preferences', N'U') IS NOT NULL
Drop table Tbl_Preferences
SELECT * INTO    Tbl_Preferences    FROM    QRemote...Preferences

IF OBJECT_ID (N'Tbl_PriceLevel', N'U') IS NOT NULL
Drop table Tbl_PriceLevel
SELECT * INTO    Tbl_PriceLevel    FROM    QRemote...PriceLevel

IF OBJECT_ID (N'Tbl_PriceLevelPerItem', N'U') IS NOT NULL
Drop table Tbl_PriceLevelPerItem
SELECT * INTO    Tbl_PriceLevelPerItem    FROM    QRemote...PriceLevelPerItem

IF OBJECT_ID (N'Tbl_PurchaseOrder', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrder
SELECT * INTO    Tbl_PurchaseOrder    FROM    QRemote...PurchaseOrder

IF OBJECT_ID (N'Tbl_PurchaseOrderLine', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrderLine
SELECT * INTO    Tbl_PurchaseOrderLine    FROM    QRemote...PurchaseOrderLine

IF OBJECT_ID (N'Tbl_PurchaseOrderLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_PurchaseOrderLinkedTxn
SELECT * INTO    Tbl_PurchaseOrderLinkedTxn    FROM    QRemote...PurchaseOrderLinkedTxn

IF OBJECT_ID (N'Tbl_ReceivePayment', N'U') IS NOT NULL
Drop table Tbl_ReceivePayment
SELECT * INTO    Tbl_ReceivePayment    FROM    QRemote...receive payment

IF OBJECT_ID (N'Tbl_ReceivePaymentLine', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentLine
SELECT * INTO    Tbl_ReceivePaymentLine    FROM    QRemote...ReceivePaymentLine

IF OBJECT_ID (N'Tbl_ReceivePaymentLineLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentLineLinkedTxn
SELECT * INTO    Tbl_ReceivePaymentLineLinkedTxn    FROM    QRemote...ReceivePaymentLineLinkedTxn

IF OBJECT_ID (N'Tbl_ReceivePaymentToDeposit', N'U') IS NOT NULL
Drop table Tbl_ReceivePaymentToDeposit
SELECT * INTO    Tbl_ReceivePaymentToDeposit    FROM    QRemote...ReceivePaymentToDeposit

IF OBJECT_ID (N'Tbl_Sales', N'U') IS NOT NULL
Drop table Tbl_Sales
SELECT * INTO    Tbl_Sales    FROM    QRemote...Sales

IF OBJECT_ID (N'Tbl_SalesLine', N'U') IS NOT NULL
Drop table Tbl_SalesLine
SELECT * INTO    Tbl_SalesLine    FROM    QRemote...SalesLine

IF OBJECT_ID (N'Tbl_SalesOrder', N'U') IS NOT NULL
Drop table Tbl_SalesOrder
SELECT * INTO    Tbl_SalesOrder    FROM    QRemote...SalesOrder

IF OBJECT_ID (N'Tbl_SalesOrderLine', N'U') IS NOT NULL
Drop table Tbl_SalesOrderLine
SELECT * INTO    Tbl_SalesOrderLine    FROM    QRemote...SalesOrderLine

IF OBJECT_ID (N'Tbl_SalesOrderLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_SalesOrderLinkedTxn
SELECT * INTO    Tbl_SalesOrderLinkedTxn    FROM    QRemote...SalesOrderLinkedTxn

IF OBJECT_ID (N'Tbl_SalesReceipt', N'U') IS NOT NULL
Drop table Tbl_SalesReceipt
SELECT * INTO    Tbl_SalesReceipt    FROM    QRemote...SalesReceipt

IF OBJECT_ID (N'Tbl_SalesReceiptLine', N'U') IS NOT NULL
Drop table Tbl_SalesReceiptLine
SELECT * INTO    Tbl_SalesReceiptLine    FROM    QRemote...SalesReceiptLine

IF OBJECT_ID (N'Tbl_SalesRep', N'U') IS NOT NULL
Drop table Tbl_SalesRep
SELECT * INTO    Tbl_SalesRep    FROM    QRemote...SalesRep

IF OBJECT_ID (N'Tbl_SalesTaxCode', N'U') IS NOT NULL
Drop table Tbl_SalesTaxCode
SELECT * INTO    Tbl_SalesTaxCode    FROM    QRemote...SalesTaxCode

IF OBJECT_ID (N'Tbl_SalesTaxPaymentCheck', N'U') IS NOT NULL
Drop table Tbl_SalesTaxPaymentCheck
SELECT * INTO    Tbl_SalesTaxPaymentCheck    FROM    QRemote...SalesTaxPaymentCheck

IF OBJECT_ID (N'Tbl_SalesTaxPaymentCheckLine', N'U') IS NOT NULL
Drop table Tbl_SalesTaxPaymentCheckLine
SELECT * INTO    Tbl_SalesTaxPaymentCheckLine    FROM    QRemote...SalesTaxPaymentCheckLine

IF OBJECT_ID (N'Tbl_ShipMethod', N'U') IS NOT NULL
Drop table Tbl_ShipMethod
SELECT * INTO    Tbl_ShipMethod    FROM    QRemote...ShipMethod

IF OBJECT_ID (N'Tbl_SpecialAccount', N'U') IS NOT NULL
Drop table Tbl_SpecialAccount
SELECT * INTO    Tbl_SpecialAccount    FROM    QRemote...SpecialAccount

IF OBJECT_ID (N'Tbl_SpecialItem', N'U') IS NOT NULL
Drop table Tbl_SpecialItem
SELECT * INTO    Tbl_SpecialItem    FROM    QRemote...SpecialItem

IF OBJECT_ID (N'Tbl_StandardTerms', N'U') IS NOT NULL
Drop table Tbl_StandardTerms
SELECT * INTO    Tbl_StandardTerms    FROM    QRemote...StandardTerms

IF OBJECT_ID (N'Tbl_TaxCode', N'U') IS NOT NULL
Drop table Tbl_TaxCode
SELECT * INTO    Tbl_TaxCode    FROM    QRemote...TaxCode

IF OBJECT_ID (N'Tbl_Template', N'U') IS NOT NULL
Drop table Tbl_Template
SELECT * INTO    Tbl_Template    FROM    QRemote...Template

IF OBJECT_ID (N'Tbl_Terms', N'U') IS NOT NULL
Drop table Tbl_Terms
SELECT * INTO    Tbl_Terms    FROM    QRemote...Terms

IF OBJECT_ID (N'Tbl_TimeTracking', N'U') IS NOT NULL
Drop table Tbl_TimeTracking
SELECT * INTO    Tbl_TimeTracking    FROM    QRemote...TimeTracking

IF OBJECT_ID (N'Tbl_ToDo', N'U') IS NOT NULL
Drop table Tbl_ToDo
SELECT * INTO    Tbl_ToDo    FROM    QRemote...ToDo

IF OBJECT_ID (N'Tbl_Transaction', N'U') IS NOT NULL
Drop table Tbl_Transaction
SELECT * INTO    Tbl_Transaction    FROM    QRemote..."Transaction"

IF OBJECT_ID (N'Tbl_Transfer', N'U') IS NOT NULL
Drop table Tbl_Transfer
SELECT * INTO    Tbl_Transfer    FROM    QRemote...Transfer

IF OBJECT_ID (N'Tbl_TransferInventory', N'U') IS NOT NULL
Drop table Tbl_TransferInventory
SELECT * INTO    Tbl_TransferInventory    FROM    QRemote...TransferInventory

IF OBJECT_ID (N'Tbl_TransferInventoryLine', N'U') IS NOT NULL
Drop table Tbl_TransferInventoryLine
SELECT * INTO    Tbl_TransferInventoryLine    FROM    QRemote...TransferInventoryLine

IF OBJECT_ID (N'Tbl_TxnDeleted', N'U') IS NOT NULL
Drop table Tbl_TxnDeleted
SELECT * INTO    Tbl_TxnDeleted    FROM    QRemote...TxnDeleted

IF OBJECT_ID (N'Tbl_UnitOfMeasureSet', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSet
SELECT * INTO    Tbl_UnitOfMeasureSet    FROM    QRemote...UnitOfMeasureSet

IF OBJECT_ID (N'Tbl_UnitOfMeasureSetRelatedUnit', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSetRelatedUnit
SELECT * INTO    Tbl_UnitOfMeasureSetRelatedUnit    FROM    QRemote...UnitOfMeasureSetRelatedUnit

IF OBJECT_ID (N'Tbl_UnitOfMeasureSetDefaultUnit', N'U') IS NOT NULL
Drop table Tbl_UnitOfMeasureSetDefaultUnit
SELECT * INTO    Tbl_UnitOfMeasureSetDefaultUnit    FROM    QRemote...UnitOfMeasureSetDefaultUnit

IF OBJECT_ID (N'Tbl_Vehicle', N'U') IS NOT NULL
Drop table Tbl_Vehicle
SELECT * INTO    Tbl_Vehicle    FROM    QRemote...Vehicle

IF OBJECT_ID (N'Tbl_VehicleMileage', N'U') IS NOT NULL
Drop table Tbl_VehicleMileage
SELECT * INTO    Tbl_VehicleMileage    FROM    QRemote...VehicleMileage

IF OBJECT_ID (N'Tbl_Vendor', N'U') IS NOT NULL
Drop table Tbl_Vendor
SELECT * INTO    Tbl_Vendor    FROM    QRemote...Vendor

IF OBJECT_ID (N'Tbl_VendorContacts', N'U') IS NOT NULL
Drop table Tbl_VendorContacts
SELECT * INTO    Tbl_VendorContacts    FROM    QRemote...VendorContacts

IF OBJECT_ID (N'Tbl_VendorCredit', N'U') IS NOT NULL
Drop table Tbl_VendorCredit
SELECT * INTO    Tbl_VendorCredit    FROM    QRemote...VendorCredit

IF OBJECT_ID (N'Tbl_VendorCreditExpenseLine', N'U') IS NOT NULL
Drop table Tbl_VendorCreditExpenseLine
SELECT * INTO    Tbl_VendorCreditExpenseLine    FROM    QRemote...VendorCreditExpenseLine

IF OBJECT_ID (N'Tbl_VendorCreditItemLine', N'U') IS NOT NULL
Drop table Tbl_VendorCreditItemLine
SELECT * INTO    Tbl_VendorCreditItemLine    FROM    QRemote...VendorCreditItemLine

IF OBJECT_ID (N'Tbl_VendorCreditLinkedTxn', N'U') IS NOT NULL
Drop table Tbl_VendorCreditLinkedTxn
SELECT * INTO    Tbl_VendorCreditLinkedTxn    FROM    QRemote...VendorCreditLinkedTxn

IF OBJECT_ID (N'Tbl_VendorType', N'U') IS NOT NULL
Drop table Tbl_VendorType
SELECT * INTO    Tbl_VendorType    FROM    QRemote...VendorType

IF OBJECT_ID (N'Tbl_WorkersCompCode', N'U') IS NOT NULL
Drop table Tbl_WorkersCompCode
SELECT * INTO    Tbl_WorkersCompCode    FROM    QRemote...WorkersCompCode

IF OBJECT_ID (N'Tbl_WorkersCompCodeRateHistory', N'U') IS NOT NULL
Drop table Tbl_WorkersCompCodeRateHistory
SELECT * INTO    Tbl_WorkersCompCodeRateHistory    FROM    QRemote...WorkersCompCodeRateHistory

end

================================================================

All tables are imported to the SQL Server Database by executing this stored procedure.

You can see that all tables are imported into the SQL Server Database.

You can add this stored procedure to SQL Agent & schedule it. So it will automatically add all tables to the SQL Server Database.

Now we will add this stored procedure to SQL Agent.

Right-click on SQL Server Agent & click on New >> Job to create a new Job.

A New Job pop-up will appear. Specify the name of the job.

Click next on the "Steps" in the left menu. An SQL job can contain one or more steps. A step might be simply an SQL statement or a stored procedure call. Add your effort here.

Enter the step name and type. Select the database, write the stored procedure execution command "exec Import_QODBC_Tables," & click ok.

Job step added.

The job is added to SQL Server Agent.

You can run a Job by right-clicking on the Job name & click on "Start Job at Step..."

The job is executing.

Problem Description:

I am using the code below. It is returning records & giving me the correct result. But if I change the query from "SELECT Name FROM customer" to "SELECT * FROM customer," it does not return any records & giving me the incorrect result. I am getting the following error:

[QODBC] Internal error
440: BOF or EOF is True, or the current record has been deleted. The requested operation requires a current record.



Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub 

Solution:

To resolve the issue, you need to add the line below to your connection string.

OptimizerAllowDirtyReads=N

For Example:

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;OptimizerAllowDirtyReads=N"

After changing this, you can get the correct result.

#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <conio.h>

using namespace std;

void ConvertTimeStampStructToString(TIMESTAMP_STRUCT tTimeStamp, CHAR *strTimeStamp, LONG lSizeTimeStamp)
{
strcpy(strTimeStamp, "");
sprintf(sr timestamp, "%d-%d-%d %d:%d:%d," tTimeStamp.year, tTimeStamp.month, tTimeStamp.day, tTimeStamp.hour, tTimeStamp.minute, tTimeStamp.second);
}

int main()
{
SQLHENV henv = SQL_NULL_HENV; // Environment
SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle

SQLCHAR sListId[256] = "";
SQLCHAR sEditSequence[256] = "";
SQLCHAR sName[256] = "";
BOOL bIsActive = 0;
TIMESTAMP_STRUCT tTimeModified = {0};
TIMESTAMP_STRUCT tTimeCreated = {0};
SQLINTEGER cbData;

SQLRETURN retcode;
int iKey;

CHAR *sTimeStamp = new CHAR[32]();

SQLCHAR statement[]="SELECT * FROM ShipMethod";

retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLAllocHandle: Environment handle initialized." << endl;

retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLCHAR *)(void*)SQL_OV_ODBC2, -1);
if(retcode != SQL_SUCCESS){
cout << "SQLSetEnvAttr failed. exit program" << endl;
return 0;
}
cout << "SQLSetEnvAttr : ODBC 2.0 initialized." << endl;

retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLSetEnvAttr: Database handle initialized." << endl;

retcode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);
if(retcode != SQL_SUCCESS){
cout << "SQLSetConnectAttr failed. exit program" << endl;
return 0;
}
cout << "SQLSetConnectAttr : Set Login Timeout." << endl;

retcode = SQLConnect(hdbc, (SQLCHAR*) "QuickBooks Data" , SQL_NTS, (SQLCHAR*) NULL, 0, NULL, 0);
if(retcode != SQL_SUCCESS){
cout << "SQLConnect failed. exit program" << endl;
return 0;
}
cout << "SQLConnect : Database connection successfully." << endl;

retcode = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt);
if(retcode != SQL_SUCCESS){
cout << "SQLAllocHandle failed. exit program" << endl;
return 0;
}
cout << "SQLAllocHandle : Handle initialized." << endl;

retcode = SQLExecDirect(hstmt, statement, SQL_NTS);
if(retcode != SQL_SUCCESS){
cout << "SQLExecDirect failed. exit program" << endl;
return 0;
}
cout << "SQLExecDirect : Query executed successfully." << endl;

cout << endl << endl << endl;
cout << "Query: SELECT * FROM ShipMethod " << endl;
cout << endl << endl << endl;
cout << "ListId" << ", " << "TimeModified" << ", " << "TimeCreated" << ", " << "EditSequence" << ", " << "Name" << ", " << "IsActive" << endl << endl;
cout << endl << endl;
while(SQLFetch(hstmt) == SQL_SUCCESS){

retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)sListId, sizeof(sListId), &cbData);

retcode = SQLGetData(hstmt, 2, SQL_C_TIMESTAMP, (SQLPOINTER)&tTimeModified, sizeof(tTimeModified), &cbData);

retcode = SQLGetData(hstmt, 3, SQL_C_TIMESTAMP, (SQLPOINTER)&tTimeCreated, sizeof(tTimeCreated), &cbData);

retcode = SQLGetData(hstmt, 4, SQL_C_CHAR, (SQLPOINTER)sEditSequence, sizeof(sEditSequence), &cbData);

retcode = SQLGetData(hstmt, 5, SQL_C_CHAR, (SQLPOINTER)sName, sizeof(sName), &cbData);

retcode = SQLGetData(hstmt, 6, SQL_C_BIT, (SQLPOINTER)bIsActive, sizeof(bIsActive), &cbData);

cout << sListId << ", ";
ConvertTimeStampStructToString(tTimeModified, sTimeStamp, sizeof(sTimeStamp));
cout << sTimeStamp << ", ";
ConvertTimeStampStructToString(tTimeCreated, sTimeStamp, sizeof(sTimeStamp));
cout << sTimeStamp << ", ";
cout << sEditSequence << ", ";
cout << sName << ", ";
cout << bIsActive << endl;
}
delete [] sTimeStamp;
cout << endl << endl;

retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Handle freed." << endl;

retcode = SQLDisconnect (hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLDisconnect failed. exit program" << endl;
return 0;
}
cout << "SQLDisconnect : Close database connection." << endl;

retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Database handle freed." << endl;


retcode = SQLFreeHandle(SQL_HANDLE_ENV, henv);
if(retcode != SQL_SUCCESS){
cout << "SQLFreeHandle failed. exit program" << endl;
return 0;
}
cout << "SQLFreeHandle : Environment handle freed." << endl;

printf("Press Any Key To Exit...");
getch();
return 0;
}

Please click here to download the C++ code.

Problem Description:

Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back-ordered invoices from the invoice table?

If a customer orders some items, we create an invoice. Then, if some things are back-ordered, when the packing slip is made, it shows they were not sent out, and that item is back-ordered. I was looking for a query or an SP that would lead me to only invoices that have not been completely shipped, or all the items in backorder. 

Solution:

All you need to do is use the ItemInventory table to pick up the quantity on hand and compare it with the ordered quantity ordered and invoiced to date (invoiced or not), and call the column "Qty to Pick."

Something along the lines of:

SELECT SalesOrderLine.SalesOrderLIneItemRefFullName as Type, SalesOrderLine.TxnDate as Date, SalesOrderLine.DueDate, SalesOrderLine.RefNumber as Num, SalesOrderLine.CustomerRefFullName as Name, SalesOrderLine.SalesOrderLineQuantity as "Qty Ordered," SalesOrderLine.SalesOrderLineInvoiced as "Qty Invoiced," (SalesOrderLine.SalesOrderLineQuantity - SalesOrderLine.SalesOrderLineInvoiced) as "Qty to Pick," ItemInventory.QuantityOnHand as "Qty Available," ItemInventory.QuantityOnOrder as "Qty on Back Order" FROM SalesOrderLine, ItemInventory where SalesOrderLine.SalesOrderLineItemRefListID = ItemInventory.ListID and SalesOrderLine.IsFullyInvoiced = FALSE and SalesOrderLine.SalesOrderLineInvoiced < SalesOrderLine.SalesOrderLineQuantity

Problem Description:

All our products are Inventory Assemblies. I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is kept outside of QuickBooks, to make sure QuickBooks has the latest inventory data.

I have successfully inserted ItemInventoryAssemblyLine entries, but now I'm trying to update the Quantity information on parts for existing assemblies.

To do the update, I need to join a local table I've created from the external inventory data, which I've loaded with the ListID, ItemInventoryAssemblyLnItemInventoryRefListID, and FQPrimaryKey values from ItemInventoryAssemblyLine, in order to facilitate joins.

Here's my SQL (from MS Access Query Designer):

UPDATE ItemInventoryAssemblyLine INNER JOIN Assemblies_with_PK ON ItemInventoryAssemblyLine.FQPrimaryKey = Assemblies_with_PK.FQPrimaryKey SET ItemInventoryAssemblyLine.ItemInventoryAssemblyLnQuantity = [Qty];

"Qty" is a column in the local table with the updated Quantity that I'd like to edit into the QuickBooks table.

When I run this query, I get the dreaded "Error parsing complete XML return string." error. I have tried alternatively joining on the combination of ListID and ItemInventoryAssemblyLnItemInventoryRefListID instead of FQPrimaryKey with the same result.

I have updated Custom Fields in the parent ItemInventoryAssembly table using the same approach with no problems.

Any help with this would be greatly appreciated! 

Solution:

Please update your QODBC to the latest QODBC version and test again.

Please click here to get the latest QODBC version.

If you are still getting the same error after updating the version, I kindly request you to please raise a support ticket to the QODBC Technical Support department by clicking here.

Problem Description:

How do I update Customer custom fields? 

Solution:

You can update Customer custom fields using the update query.

For Example:
Update Customer Set CustomFieldContract='QODBCJOB1' where ListID='800000F2-1513344011'

And there you have it! To check that everything worked, just do the following:
Select ListID, Name, FullName, CustomFieldContract from Customer where ListID='800000F2-1513344011'

You can check it on QuickBooks, also.

You can insert Custom fields during Customer creation also. Please refer to the sample query for the same.

INSERT INTO "customer" ("Name," "LastName," "FirstName," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalcode," "CustomFieldBDay," "CustomFieldContract," "CustomFieldSpousesName") values('Juniga, Daphne,' 'Juniga,' 'Daphne,' 'Daphne Juniga,' '561 W 4th St.', 'Carlsbad,' 'CA,' '92009', '26/01/1987', 'QODBCJOB1', 'Jack')

And there you have it! To check that everything worked, just do the following:
Select Name, CustomFieldBDay, CustomFieldContract, CustomFieldSpousesName from Customer where Name='Juniga, Daphne'

You can check it on QuickBooks, also.

Problem Description 1:

I recently installed QODBC to get data out of a QuickBooks company file, but I get this error: "Index was outside the bounds of the array."

Problem Description 2:

We are attempting to import tables into SQL Server, which we use as a staging environment. We are not doing any transformations, simply trying to copy the data from QuickBooks into SQL Server using the Import Wizard. Below is the screenshot of the error we receive when attempting to do so. Details of the configuration are provided below.

Source data (QuickBooks): Microsoft .NET provider for ODBC - QODBC 32-bit configured DSN with default settings. Destination Data ( SQL Server): Microsoft OLEDB provider for SQL Server

We are attempting to import four tables for now.

- Account, Class, Entity, Transactions

The reported error below occurs for each table.

Error: Can not load the column attributes from the schema for the current provider descriptor.
- COLUMN_NAME
- TYPE_NAME
- COLUMN_SIZE
- DECIMAL_DIGITS
- COLUMN_SIZE

Solution:

Please follow the steps below to resolve this error:

1. Go to Import Export Wizard by right-clicking on your SQL Server database, clicking on Task, & Click on Import Data.

2. Choose ODBC Data Source & Enter DSN & Connection String, and click "Next."

3. Choose Destination and click on "Next."

4. Choose the second option (Write a query to specify the data to transfer) and click "Next."

5. Write the query and click on "Next." You can change the table name & question as per your requirement.
For Example:
Select * from the Account

6. Change the destination table name to your requirement and click "Edit Mapping."

7. You must verify the date type of all columns from the "Type" column.

You need to make sure that you are not getting 23 in Type. You will get 23 date-type columns.

You need to replace 23 with the current date for all columns whose Type is 23 and click on "OK."

8. Click on "next" until the finish.

Save and Run Package.

Complete the Wizard.

9. You can see the table data imported to SQL Server's local database table.

Keywords: How to use DTS to import QuickBooks Data into MS SQL Server

Problem Description:

I'm trying to link a general ledger report in Excel. I've created the sp_report SQL query in Microsoft Query just fine, but the data it returns contains subtotals and blank lines. I know I can write a script/filter in Excel to eliminate these lines, but I can't figure out what parameters to use to do it using QODBC.

Solution:

In QODBC, you can remove subtotals and blank lines in sp_report by adding the following condition.

where RowType='DataRow'

For example:

sp_report ProfitAndLossDetail show TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisYearToDate' where RowType='DataRow'

Problem Description:

I have a custom program written in Access 2003 communicating with QuickBooks 2013 using QODBC 13, which works fine. Will upgrading to the latest QuickBooks and QODBC require any code changes to the Access 2003 program?

Problem Description:

We are upgrading from QuickBooks Pro 2011 to QuickBooks Enterprise (the latest) and need a developer/consultant to assist in checking/upgrading queries and code to work with the new versions of QODBC/QuickBooks. Our company uses the QODBC Driver for QuickBooks to synchronize data between our Microsoft Access database and QuickBooks. We push customers, jobs, cheques, and items from Access to QuickBooks and pull data from QuickBooks to Access for reports.  

Solution:

It is unlikely that you will have a problem as QODBC is upward compatible with QuickBooks, so the same queries you have running now should continue to work.

Suggest you purchase the latest version of QODBC, which supports the newest version of QuickBooks, then run some tests. If you have any trouble with connections, please refer to the following:

Connecting QODBC Driver to QuickBooks for the First Time (Video)
How to Use QODBC with Microsoft Access 2010 to Build Linked Table(32-Bit)
How to Configure QODBC to Work With 64-bit MS Access

Or Raise a Support Ticket

There shall be no code change required in your MS-Access Application/Queries, and it should be able to work with the latest QuickBooks & QODBC.

Additional Information:
With every release of QuickBooks/QODBC, new fields or features might be added. If you want to leverage the new areas, you will have to re-link the tables in Access. Refer: How to Re-Link QuickBooks Data in MS Access using QODBC.

Please refer to the link below for details about the updated QODBC table & report schema and the relation between tables. You will also get information on each report column parameter that is supported. You can also view all table/column details.

Please refer to QODBC Data Layout for more details.

Problem Description:

I need to use Between, in a range of dates, to return new customers in one period. I try this:

SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'} AND SalesRepRefFullName='PD'

Is the use of QODBC correct? 

Solution:

The use of BETWEEN here is fine. It's just TimeCreated is a TIMESTAMP, not a date. It should be:

SELECT FullName FROM Customer Where TimeCreated Between {ts'2007-12-18 00:00:00.000'} AND {ts'2007-12-19 00:00:00.000'} AND SalesRepRefFullName='PD'

Problem Description:

I'm trying to make Inventory Items inactive after a sale programmatically or Active after voiding a sale.

I thought the following SQL statement would have worked fine, but I got error 214217887(80040e21), saying the ODBC driver doesn't support this statement.

sSQL = "UPDATE ItemInventory SET ItemInventory.IsActive = True " & _
"WHERE (((ItemInventory.ListID)='ED0000-1173904787'))" 

Solution:

QODBC doesn't support the Table name used in the SET columns. So you need to remove the table name before the column name in the SET condition. For example:

sSQL = "UPDATE ItemInventory SET IsActive = True " & _
"WHERE (((ListID)='ED0000-1173904787'))"

Problem Description:

What is the fastest way to get the number of records from a table? 

Solution:

QODBC would need to scan the whole table to get the record or row count. So you shouldn't do it if you don't need to. If you must do it, run the query against the optimized table using nosync like this:

SELECT count(listid) as "Table Row Count" from Customer nosync

Problem Description:

I am using the following SQL statement:

sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' , EntityFilterFullNameWithChildren = '{?customer}'

I then create a customer parameter in the parameter list. The customer name is pretty long, so is there a way to search by using a wildcard, i.e., *50-126*, rather than having to enter:

Customer name. location.50-126.projectdescription??

Solution:

Sorry, you can't use wildcards, and you need to use the full name path for a specific job:

sp_report JobProfitabilityDetail parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Abercrombie, Kristy:Remodel Bathroom', SummarizeColumnsBy = 'TotalOnly'

If you wanted, for example, to compare all Remodel Bathroom (or 50-126) jobs for all customers, you would need to run the Job Profitability detail report for each customer to a data file (like an append query in MS Access) and report from the data file instead.

Problem Description:

I created a report using a custom set with the customer as the parent and InvoiceLine as the child, giving me items ordered in the past.

My problem is that I only want the last time an item was ordered and want to eliminate some things, such as shipping.

When I try to filter out items like shipping, it also takes out any invoice line items.

I'm at a complete loss when it comes to getting only the last time an item was ordered.  

Solution:

You can get items ordered by a customer, in part name order, with the last date ordered first,t by using the below query:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian: Kitchen' and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc



Or for the last time an item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc

Problem Description:

We are involved in a new project with our bank involving a 'Positive Payment' solution that requires I send them a file with the check summary data each time we do a check printing run. The example layout I received from this looks like this: (best viewed with a fixed font)

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8

RECONCILIATIONHEADER0075207990012345600000001000000005
20799001234560000071909000005100020040512 Abelman, Frayne & Schwab
20799001234560000071910000000200020040512 American Express
20799001234560000071911000000200020040512 Arizona Dept. of Revenue
20799001234560000071912000000200020040512 Arrow Electronics Inc.
20799001234560000071913000000200020040512 ASCOM Hasler Mailing Sys

I can manage the formatting if I can get the data into Access, but I need to grab the check number, check amount, issue date, void indicator, if that's available, and the payee name.

Can I do this? 

Solution:

If you are paying bills each day and printing them in a daily batch, then you can try looking at the following:

SELECT RefNumber, Amount, TxnDate, Memo, PayeeEntityRefFullName FROM BillPaymentCheck where BankAccountRefFullname='Checking' and IsToBePrinted is NULL and TxnDate={fn CURDATE()}

Problem Description:

I want to update the salesrep field in my existing invoices. I want to use the salesrep value stored in the Customer file. I would normally do this in SQL using a correlated subquery. When I run the following query: update Invoice set SalesReprefFullName = (select SalesRepRefFullName from Customer where Invoice.CustomerrefFullName = Company.FullName), I get the following error: [QODBC] Expected lexical element not found:

Solution:

Sorry, even your nested SELECT statement is invalid. It would be best if you always referenced the TxnID for all update operations using QODBC. For example, for Invoices, the update operation would look like this:

Update Invoice set SalesRepRefListID='10000-1197757777' where Invoice.TxnID='2B55-1071523067'

Where:

SalesRepRefListID is the List ID for an existing Sales Representative from the SalesRep table.
TxnID is the Transaction ID for the existing Invoice from the Invoice table.

Problem Description:

We need to bill all payroll costs to various customers. Payroll costs are defined as all of the following:

1. Employee's gross salary
2. Company share of social security
3. Company share of Medicare
4. Federal unemployment taxes paid
5. Texas unemployment taxes paid
When a payroll run is processed, all the above costs are coded to the appropriate customer, and QuickBooks flags them as "billable" at this point.

To bill the payroll costs, we go to the Customers menu and select "Invoice for Time and Expenses." All employee costs outlined above are then brought up in a list. We check the items we want to bill (all of them) and then select "create an invoice."

The above five items are listed separately when the invoice is created, but valuable descriptions are not displayed. We need the invoice to record the employee's name and the nature of the expense associated with each cost being billed out. E.g.

John Doe Gross Salary $500
John Doe Company Social Security $25
Etcetera
We have tried using various invoice forms, and these fields are never available to select for inclusion.

We can see the fields needed in the payroll module by viewing the details of the employee check. However, we have not been successful in finding these fields in the QuickBooks tables so that we can pull them into the invoice using your software. If I can see the data in the tables, I am confident I can extract it using QODBC and SQL to format it into a suitable report.

Thanks in advance for any help.

Solution:

You can get these details from the PayrollTransactionDetail report:

For this example, PayCheck:



You can find all the details using the following:

sp_report PayrollTransactionDetail show PayrollItem, WageBase, Credit, Debit, Amount parameters DateFrom = {d'2007-12-01'}, DateTo={d'2007-12-01'}, IncludeAccounts = 'All,' ReportDetailLevelFilter = 'AllExceptSummary,' EntityFilterFullNameWithChildren='Dan T. Miller' Where PayrollItem is not NULL

Change the bold entries to the employee and paycheck date you're after

Note: QODBC does not support direct import, but you can write VBA code that reads a CSV / Excel file & generates insert statements.

There are two ways to import data to QuickBooks through CSV using QODBC.

1) Using Wizard

Create a new MS Access Database file & link the Customer table.

Please refer to Using QuickBooks Data with Access 2016 / 365 / 2013 32-bit for linking the Customer table.

Go to the "External Data" tab on Microsoft Access, as shown in the screenshot below, and click on the "Text File."

The "Get External Data" screenshot will be shown below. Select the CSV file by clicking the "Browse" button. (*Note: the file name must be less than 64 characters. Otherwise, Microsoft Access will not be able to import it. Choose the second option. Append the copy of the records to the table.

Next, select the option "Delimited - Characters such as comma or tab separate each field," as shown in the screenshot below, and click "Next."

Next, make sure "Comma" is selected in the "Choose the delimiter that separates your fields" and the double quotes symbol "s selected in the "Text Qualifier" as shown in the screenshot below, and click "Next" to continue.

Now, click on the Advanced button to configure import fields.

Now write the field name you want to insert from the CSV file & click OK.

Click Finish to complete the import process.

Import Done. Customer data from CSV is inserted into the QuickBooks customer table.

Or

2) Using VBA Code

Please refer to Using QuickBooks Data with VBA

In this tutorial, we are showing a sample VBA script inserting Customer & Invoice.

Importing Customer to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have a Customer CSV file with Customer Name, Company Name, Phone & Email fields.

We will import these fields to QuickBooks using VBA. You can see the records below from the Customer CSV file.

Please refer to the sample VBA code for Importing Customers to QuickBooks through a CSV file using QODBC.

By clicking on the "Add Customer" button, the Customer list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer to the code below code which is used in this example:

Import Customer:

Option Compare Database

Public Sub exampleCsvImportCustomer()

Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim sMsg
Dim rs
Dim I As Integer
i = 0
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM customer"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs. Open sSQL, Connection, adopt dynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Customer.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Customer.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("Name") = MyArray(0)
rs("CompanyName") = MyArray(1)
rs("Phone") = MyArray(2)
rs("Email") = MyArray(3)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Customer Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Importing Invoice to QuickBooks through CSV file using QODBC & Microsoft Access VBA

We have an Invoice CSV file with CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefListID & FQSaveToCache fields.

In this example, we are creating two Invoices, each having 3 invoice lines. We will import these fields into QuickBooks using VBA. You can see the records below from the Invoice CSV file.

Please refer to the sample VBA code for Importing Invoices to QuickBooks through a CSV file using QODBC.

By clicking the "Add Invoice" button, the Invoice list in a CSV file will be imported into QuickBooks using QODBC.

Please Note: You need to change the CSV file location & VBA script according to your CSV file location & file data.

Please refer to the code below code which is used in this example:

Import Invoice:

Option Compare Database

Public Sub exampleCsvImportInvoice()
Dim oConnection As New ADODB.Connection
Dim sConnectString
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim rs
Dim sMsg
Dim I As Integer
i = 0

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

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"

sSQL = "SELECT * FROM InvoiceLine"

Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("C:\Input\Invoice.csv") Then
Set objStream = fso.OpenTextFile("C:\Input\Invoice.csv", 1, False, 0)
End If

Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
ReDim MyArray(0)
MyArray = Split(strLine, ",")
rs.AddNew
rs("CustomerRefListID") = MyArray(0)
rs("RefNumber") = MyArray(1)
rs("InvoiceLineItemRefListID") = MyArray(2)
rs("InvoiceLineDesc") = MyArray(3)
rs("InvoiceLineRate") = MyArray(4)
rs("InvoiceLineQuantity") = MyArray(5)
rs("InvoiceLineSalesTaxCodeRefListID") = MyArray(6)
rs("FQSaveToCache") = MyArray(7)
rs.Update
i = i + 1

Loop

sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

End Sub

Please Note: If you have a 64-bit application, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote" (i.e., sConnectString = "DSN=QuickBooks Data 64-bit QRemote; OLE DB Services=-2;")

Download Sample

Also, Refer:

QODBC Reports Architecture Mismatch Problem When connecting

Problem Description:

How to stop any query on VBDEMO when it takes time.

How to convert the date to a QuickBooks date.

I want to insert TxnDate in the ReceivePayment table, but it pops up the operator and operand error.

I am just inserting values in the ReceivePayment Table. My Query is:

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,txndate, RefNumber, PaymentMethodRefFullName, Memo, DepositToAccountRefListID, TotalAmount, IsAutoApply) VALUES ('B480000-1171302186', '3C0000-1164634404', '2007-02-12', '10667', 'SECPAY,' '72465', '80000-1164634403',54.04, TRUE)

I am looking for your early reply with thanks. 

Solution:

You can stop query execution by clicking "Stop Query" from the QODBC Icon on the system tray. Right-click on the QODBC Icon in the system tray and click on "Stop Query" for 5 or 10 minutes.



If the above does not work, and you need to close VB Demo, use Alt-Ctrl-Del and the Windows Task Manager to end the VB Demo application task:





QODBC uses the standard SQL date format: {d'YYYY-MM-DD'}. You will get an Invalid operand for operator error when you provide the wrong data type values in your query. Please refer to Troubleshooting - [QODBC] Invalid operand for operator

INSERT INTO ReceivePayment (CustomerRefListID, ARAccountRefListID,txndate, RefNumber, PaymentMethodRefFullName, Memo, DepositToAccountRefListID, TotalAmount, IsAutoApply) VALUES ('B480000-1171302186', '3C0000-1164634404', {d'2007-02-12'}, '10667', 'SECPAY,' '72465', '80000-1164634403',54.04, TRUE)

Problem Description:

I don't see any indicator in the Invoice table. How do I void an Invoice? And how do I query for invoices that have been voided? 

Solution:

You can use SP_VOID just like you would use DELETE. It will void the invoice instead of deleting it.

For Example:

SP_VOID FROM Invoice WHERE TxnID = '1B03F-1513346399'



You can use the query below to get a list of Voided Invoices.

Select RefNumber, CustomerRefFullName, and Memo from Invoice, like 'VOID:%.'

If no InvoiceLine is returned, you can resync your optimized InvoiceLine table by running:

Problem Description:

This is a no-technical question, but one I need to answer myself before jumping into QODBC. I'm considering working with QODBC to get the information we can't get easily from QuickBooks reports. I want to ask people working with QODBC, QuickBooks, and QuickBooks POS if QODBC can access and work with information related to the time of purchases. For example, view items that sell the most at 11 AM or between 4 and 5 PM, and also see what those items are. I don't know if this information is located in the QuickBooks or the QuickBooks POS file. If someone could tell me if I could access this type of information, I could start working with QODBC. I would appreciate any help. 

Solution:

You can use the query below to get time frame information using QODBC.

For Example:

SELECT {fn HOUR(TimeCreated)} as Hour, SalesLineQuantity as Qty, Type, RefNumber as Num, SalesLineItemRefFullName as Item, SalesLineDesc as Memo, SalesLineRate as SalesPrice, SalesLineAmount as Amount, TimeModified, CustomFieldContract as Contract# FROM SalesLine NOSYNC where (TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) and {fn CONVERT( {fn HOUR(TimeCreated)}, SQL_VARCHAR)} = '22' and SalesLineType='Item' and SalesLineItemRefFullName is not null and SalesLineQuantity > 0 order by SalesLineQuantity DESC, SalesLineItemRefFullName



Where '22' is the hour using a 24-hour format.
(TxnDate >= {d'2007-12-01'} and TxnDate <={d'2007-12-31'}) is the start and end date for the report

Problem Description:

The InvoiceLine table contains orphan records (the parent invoice record no longer exists). I tried:

DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx'

The TxnID is supplied from a list before running the DELETE command.

However, no records are deleted. What am I doing wrong?  

Solution:

The first step is actually to check that there are orphaned InvoiceLines. I would hazard a guess that you have orphaned optimized InvoiceLine only and no real orphaned InvoiceLines in QuickBooks. Run the following query with the unoptimized tag using QODBC Test Tool:-

SELECT TxnID, InvoiceLineTxnLineID, InvoiceLineDesc, InvoiceLineAmount, RefNumber FROM InvoiceLine unoptimized where RefNumber = 'YourRefNumber.'

Where 'YourRefNumber' is the Reference to the Invoice.

If no InvoiceLine is returned, you can resync your optimized InvoiceLine table by running:

sp_optimizefullsync InvoiceLine

How can I get daily payment bill information? And how can I get daily Bill Payment information using QODBC, and how can I get these details?  

Solution:

You can use the query below to get details about daily Bill Payments.

For Example:

SELECT DueDate, VendorRefFullName as Vendor, RefNumber as Ref, AmountDue as "Amt. Due" FROM Bill where IsPaid = 0 and DueDate <= {d'2007-12-25'} order by DueDate

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Or

You can go straight to the BillToPay table like this.

SELECT BillToPay.BillToPayDueDate as "Date Due," Bill.VendorRefFullName as Vendor, BillToPay.BillToPayRefNumber as Ref, BillToPay.BillToPayAmountDue as "Amt. Due", BillToPay.CreditToApplyCreditRemaining as "Credit Remaining" FROM BillToPay, Bill where BillToPay.BillToPayTxnID = Bill.TxnID and BillToPay.BillToPayDueDate <= {d'2007-12-25'} order by BillToPay.BillToPayDueDate

Problem Description:

I need to compute a Year-to-Date total paid using the Invoice table. I'm working in MS-Access and need to run the following statement:

SELECT SUM(Subtotal) FROM Invoice WHERE CustomerRefListID = 'some customer ID' AND IsPaid AND TimeCreated BETWEEN date1 AND date2  

Solution:

You can use the query below to get details about records for a specified Year.

For Example:

Select * from Invoice where {fn YEAR(TxnDate)} = 2017

For more details, refer to: How are dates formatted in SQL queries when using the QuickBooks generated timestamps?

Problem Description:

How can I change the price of each item in a group? Is it possible? If yes, please give me an example.

Solution:

The ItemInventory, ItemService, and ItemOtherCharge item itself determines the price of an item within an ItemGroup. The cost of an ItemService item can be calculated using standard UPDATE statements, for example:

For Example:

Update ItemService Set SalesOrPurchasePrice=40 where Name='Floor Plans'

You cannot change the price or quantity of individual item group lines within an Invoice. To do that, you should read the item group lines and write each item separately on separate invoice lines. You can, however, state the quantity of the item group itself.

Problem Description:

The field ExpenseLineBillableStatus contains the status of the BillExpenseLine and uses the following stored procedure "sp_columns" command in QODBC Test Tool:

sp_columns BillExpenseLine

shows the: ExpenseLineBillableStatus valid values as: |Billable|NotBillable|HasBeenBilled|

However, the following insert failed:-

INSERT INTO BillExpenseLine (VendorReflistid, APAccountRefFullName,txndate, RefNumber, ExpenseLineAccountRefFullname, ExpenseLineAmount, ExpenseLinememo, ExpenseLineBillableStatus, ExpenseLineTaxCodeReffullname, FQSaveToCache ) VALUES ('290000-1045709633', 'Accounts Payable', {d'2006-02-01'}, '111111', 'Boosters COGS', 244, '2 of 222233433 aaaaaabbbbbca', 'Billable', 'NCG',0)

With an "Insert into a linked table, "BillExpenseLine failed with an error. 

Solution:

For the Billable, NotBillable, and HasBeenBilled flags to be used on a Bill Expense Line within QuickBooks, a Customer Job "ExpenseLineCustomerRefListID" must also be assigned.

To create a BillExpenseLine with a "Billable" status using two SQL insert statements:

Statement 1

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID," "ExpenseLineAmount," "ExpenseLineMemo," "ExpenseLineCustomerRefListID," "ExpenseLineBillableStatus," "ExpenseLineTaxCodeRefListID," "FQSaveToCache") VALUES ('270000-1045536344', 500.00, 'QODBC Expense Line Memo Test 1', '100000-1045537156', 'Billable,' 'C0000-1045536338', 1)

Statement 2

INSERT INTO "Bill" ("VendorRefListID," "APAccountRefListID," "TxnDate," "RefNumber," "TermsRefListID," "DueDate," "Memo") VALUES ('200000-1045537156', '3D0000-1045609540', {d'2006-02-02'}, '20061', '20000-1045536343', {d'2006-03-02'}, 'QODBC Bill Expense Memo Test')

The key to this process is in the field named "FQSaveToCache." This field is not part of the table but is used as a flag to the QODBC driver. In the sequence above, you should note that the value of "FQSaveToCache" is set to 1 (trustworthy) in the first statement.

A proper (1) setting of "FQSaveToCache" instructs QODBC to take the values from the INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 (false) or not specified, as shown in the second statement, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection and saved as a batch into QuickBooks.

Note: The BillExpenseLine.ExpenseLineAccountRefListID must be an asset type. You cannot use a COGS (cost of goods sold) type account.

Problem Description:

I want to list the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer within a specified date range.

What QODBC SQL Statement would I use?

This is what I have so far:

SELECT SUM(Subtotal) AS gt FROM Invoice WHERE CustomerRefListId='30000-1166980720' AND (TxnDate>={d'2006-12-24'} AND TxnDate<={d'2007-01-23'})

But that will only get me the subtotal of invoices for the primary customer, but it doesn't get me the combined subtotal of all the invoices for each job under that customer.

Is there a way to combine it all into one SQL Statement? Or do I have to use multiple statements and then manually combine all the totals?

Solution:

You can use the query below to get the Subtotal of Invoices Within the Date Range using QODBC.

You can run this query:

SELECT Invoice.CustomerRefFullName, Sum(Invoice.SubTotal) AS SumOfSubTotal FROM Invoice WHERE (((Invoice.TxnDate)>={d'2007-01-01'})) and (((Invoice.TxnDate)<{d'2007-12-01'})) GROUP BY Invoice.CustomerRefFullName



You can change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use.


If you want to get the SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined, I suggest you go straight to the QuickBooks reporting engine instead, which will add up and total things for you (including any credits). QuickBooks reports can be accessed using the QODBC stored report procedures:

sp_report SalesByCustomerSummary show Text as Customer, Label as Job, Amount_1 as Total parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2007-12-01'}, SummarizeColumnsBy = 'TotalOnly'

Problem Description:

The InvoiceLineRate value passed in the insert query to create the InvoiceLine record is not being loaded into QBO.

Below are the two queries I am using to create the invoice.

Insert into "InvoiceLine" ("CustomerRefListID," "CustomerRefFullName," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineQuantity," "InvoiceLineRate," "InvoiceLineAmount," "FQSaveToCache") VALUES ('11', 'Alder Electric,' '19', 'Todd Gardner', 37.5, 42.50, 1593.75, 1)

Insert into "Invoice" ("CustomerRefListID," "CustomerRefFullName," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressAddr3", "BillAddressAddr4", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "TermsRefListID," "TermsRefFullName," "DueDate")VALUES ('11', 'Alder Electric,' {d'2015-12-06'}, '1388', 'Alder Electric,' 'Dannon Alder,' '605 N 1250 W', '', 'Centerville,' 'UT,' '84014', '5', 'Due upon Receipt,' {d'2015-12-21'}) 

Solution:

QODBC Online has the logic that if the user has provided Quantity, Rate & Amount, QODBC will send Quantity & Amount to QuickBooks.

So you need to send Quantity & Rate and remove Amount. The Amount will be auto-calculated.

For Example:

Insert into "InvoiceLine" ("CustomerRefListID," "CustomerRefFullName," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineQuantity," "InvoiceLineRate," "FQSaveToCache") VALUES ('11', 'Alder Electric,' '19', 'Todd Gardner', 37.5, 42.50, 1)

Insert into "Invoice" ("CustomerRefListID," "CustomerRefFullName," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressAddr3", "BillAddressAddr4", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "TermsRefListID," "TermsRefFullName," "DueDate")VALUES ('11', 'Alder Electric,' {d'2015-12-06'}, '1388', 'Alder Electric,' 'Dannon Alder,' '605 N 1250 W', '', 'Centerville,' 'UT,' '84014', '5', 'Due upon Receipt,' {d'2015-12-21'})

Tags: QuickBooks Online, QBO, QODBC Online, invoice line rate, Quantity, Rate, invoice line amount, invoice line

Problem Description:

I am getting the error "Invalid operand for the operator: <assignment>" while trying to insert an invoice line item:

INSERT INTO InvoiceLine ( CustomerRefListID, TemplateRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineRate, InvoiceLineAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache) VALUES ( '800019E1-1428688811', '8000001B-1360624407', '17003', '80000129-1362767268', 65.0000, '130.00', '80000002-1358615111', 0)

Solution:

You will get an Invalid operand for operator error when you provide the wrong data type values in your query.

For Example:

The column data type is decimal/integer/bit & you are inserting the value as a string/character.

In your query, the InvoiceLineAmount column data type is decimal & you provided the value as a string/character.

Please refer to the sample query & test again:

INSERT INTO InvoiceLine ( CustomerRefListID, TemplateRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineRate, InvoiceLineAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache) VALUES ( '800019E1-1428688811', '8000001B-1360624407', '17003', '80000129-1362767268', 65.0000, 130.00, '80000002-1358615111', 0)

Please refer to QODBC Data Layout for more details.

Problem Description:

We just upgraded to v16 on both QuickBooks and QODBC, and now refreshes from Excel aren't working. We're getting the message "[QODBC] Column not found ReorderBuildPoint."

Solution:

Column "ReorderBuildPoint" has been changed in QODBC's latest version. Now, ReorderPoint & BuildPoint are coming separately through QuickBooks SDK. So we have changed this in QODBC.

So it would be best if you edited the column name in your query in the Excel sheet command text.

Please refer to QODBC Data Layout for more details:

Problem Description:

I am trying to query the Transaction table based on the TxnDate field.

I tried the code below.

OdbcCommand SourceCmd = new OdbcCommand("Select * from Transaction where TxnDate >= {d '2010-01-01'} and TxnDate <= {d '" + DateTime.Now.ToString("yyyy-MM-dd") + "'} ", cnQODBC);

I am getting this error:

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

And when I try this one:

OdbcCommand SourceCmd = new OdbcCommand("Select * from Transaction where TxnDate >= #2010-01-01# and TxnDate <= #" + DateTime.Now.ToString("yyyy-MM-dd") + "# ", cnQODBC);

I get this error:

ERROR [42000] [QODBC] Unexpected extra token: #

What is the correct format to pass the date values ??

I am using C#.

Solution:

Please refer to the article below for the date format in QODBC:
How are dates formatted in SQL queries when using the QuickBooks-generated timestamps

Problem Description:

I am looking for the definition and usage of "JUMPIN TYPE."

Solution:

In QODBC, JUMPIN provides relationship details with other tables and information about the value coming from another table.

Please refer to QODBC Data Layout for more details:

Jumpin is the terminology used in QODBC to show a relationship with another table. You can relate to Jumpin as a relationship.

Problem Description:

I am trying to run an update on the Name field in ItemInventory. I've checked to see if it's updateable, and it is.

I get "Cannot update table ItemInventory Error 10004," and sometimes a window pops up after clicking OK, saying there is not enough memory to complete this task or similar. I was unable to get a screenshot of the error. I run the query with nothing else open overnight on a computer with 16GB of RAM. Memory should not be an issue. I need to be able to update part numbers in batches because there are over 30,000 parts. You can't do it one at a time.

Please help.

Solution:

Please put a limit on your update query & try again instead of updating all records at a time. Please try to update records in a small group/part.

QODBC is an ODBC driver. The QODBC driver works with all front-end applications which can access ODBC, some of which are faster than others. The QuickBooks interface SDK, which uses XML, is not the speediest file system available, but it does the job.

Please remember that QODBC is not a database but rather a translation tool. QuickBooks is a non-normalized flat file system with no indexes and will not perform like SQL Server or dBase files. Every transaction you request must be translated and communicated via large, complicated XML transactions to QuickBooks.

The QuickBooks application layer needs to process those XML requests. The larger the number of Records, the lengthier the XML request; therefore, QuickBooks may take more time and memory to process longer requests.

QODBC sends the request to QuickBooks (via QBSDK), and QuickBooks will process the request(the request is in XML format); if there are more records, then QuickBooks may throw an "Out Of Memory" error.

How to insert employee time information from an MS Excel time spreadsheet into QuickBooks using QODBC

Solution:

You cannot perform insert/update operations directly in MS Excel because Excel is just exporting tables in the spreadsheet. Excel does not create link tables like MS Access. If you want to insert/update using MS Excel, then you need to use MS Query, which will execute the statement you enter in the editor, or you can write VBA code in Excel.

Please refer to the article for how to write SQL statements using MS Query: Microsoft Office with MS Query and QODBC.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

You can use ADO in VB to connect to QuickBooks via VBA in MS Excel.

Please refer to the sample code for inserting records through VBA:

Sub test()
Const adopt static = 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 TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) VALUES ( '370000-933272659', 480, {d'2007-12-18'}, 'Pretell Real Estate:75 Sunset Rd.', 'Removal,' 'Salary')."
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Please note: All query values are coming from sample company files. Please change it according to your company file value before using it.

Please click here to download a sample MS Excel file.

Problem:

Using SQL Server Integration Service (SSIS) and QODBC, All character fields are being returned as NULL.

Solution:

Please use the function CONVERT to convert the data into SQL_VARCHAR type to fix this problem.

You can use the CONVERT function in SSIS like this:

{fn CONVERT(ColumnName, SQL_VARCHAR)}

Here is an example of how to use this function in a linked server:

Syntax: SELECT {fn CONVERT(ColumnName, SQL_VARCHAR)} FROM LinkedServerName...TableName

Example: SELECT {fn CONVERT(ListID, SQL_VARCHAR)} FROM QODBC...Customer

Please note “QODBC” is the linked server name on our machine. Please replace it using your linked server when testing it on your end.

Conversion ratio information is stored in a table called UnitOfMeasureSetRelatedUnit.

Problem:

How to get Conversion Ratio information from UnitOfMeasureSetRelatedUnit Table in QODBC.

Solution:

We can get conversion ratio information by using INNER JOIN on SalesOrderLine Table.

We can get conversion ratio information by comparing UnitOfMeasureSetRelatedUnit.ListID and SalesOrderLine.ListID

Please refer below-mentioned query for conversion ratio information:

Select S.RefNumber, S.TxnDate, S.SalesOrderLineItemRefFullName, S.SalesOrderLineQuantity,
S.SalesOrderLineRate, U.UnitOfMeasureType, U.Name, U.BaseUnitName, U.RelatedUnitName,
U.RelatedUnitConversionRatio From UnitOfMeasureSetRelatedUnit U
INNER JOIN SalesOrderLine S ON S.SalesOrderLineOVerrideUOMSetRefListID = U.ListID

Problem Description:

We seem to have an issue with an insert into the InvoiceLines table, dropping a few transactions.

We need to create about 1000 invoices per month, with an average of about 6 - 7 lines per invoice. As QuickBooks cannot handle a set-based insert, we have used a cursor to insert the invoice lines and generate an invoice customer by customer (using the FQSaveToCache flag as the trigger. The code is as follows:

SET COUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC
DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
SET @i = 0

OPEN curOutput
FETCH NEXT
FROM curOutput

INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag; --Subroutine for QB insert
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO QREMOTE...INVOICELINE (CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)
VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag )
SET @i = @i + 1
IF @QBFlag = 0 and @i > 500
BEGIN
WAITFOR DELAY '00:05:00.'
set @i = 0
END

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'
FETCH NEXT
FROM curOutput
INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END
--End subroutine for QB insert

CLOSE curOutput
DEALLOCATE curOutput

Our original code was identical to the above, except that it did not include the WAITFOR DELAY code triggered every time the FQSavetoCache flag is set to zero. The code works beautifully and has successfully created invoices in QuickBooks for hundreds of customers.

Adding the 10-second delay when the FQSavetoCache flag was set to zero would resolve the issue, but it has not. If the 10-second delay doesn't seem to work when the flag is zero, I could set the delay to wait for a second or two after each row of data. Unfortunately, we are only at about a 97 - 98% success rate in the accuracy of the invoices being generated, and the issue seems to be the inability of QuickBooks (or the QODBC driver) to keep up with the data being fed from our SQL Server table (which we have verified is 100% accurate).A

OLE DB provider "MSDASQL" for linked server "qremote" returned message "[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:qodbc.com/links/2953".
Msg 7343, Level 16, State 2, Line 74
The OLE DB provider "MSDASQL" for linked server "qremote" could not INSERT INTO table "[MSDASQL]".

Do you have any suggestions? Please advise.

Solutions:

We did troubleshooting and found SQL Server sends the Disconnect command every 5 minutes. The disconnect command is not expected from SQL Server when there is activity on the linked server. This is not a QODBC or QRemote issue.

[forums.sqlteam.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes

[social.technet.microsoft.com]SQL Server Linked Server to ODBC Connection Drops connection every 4-5 minutes

[forums.sqlteam.com]Is there a way to Disconnect the Linked Server connection - Not delete and re-add

[social.technet.microsoft.com]Is there a way to Disconnect the Linked Server connection - Not delete and re-add

There seems to be no option in SQL Server, which does not cause why SQL Server is sending the Disconnect command.

We can't use MS Access as middleware because MS Access as a middleware has the same issue.

Workaround 1:

As a workaround, You can add some timing logic to the looping, like if the header insert is completed, if it is 4 minutes, it will sleep for 1 minute & 10 seconds. The sleep of 1 minute & 10 seconds will automatically send a disconnect command and resume after 1 minute. By doing so, you noticed no loss of invoice line But yes, inserting time increased due to sleep.

Please use the below script:

SET NOCOUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC
DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
DECLARE @firstTime datetime
DECLARE @currentTime datetime
SET @i = 0
set @firstTime=GETDATE();
set @currentTime=GETDATE();

OPEN curOutput
FETCH NEXT
FROM curOutput
INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag;
--Subroutine for QB insert
WHILE @@FETCH_STATUS = 0

BEGIN
INSERT INTO QRemote...INVOICELINE (CustomerRefListID, TemplateRefListID,TxnDate, TermsRefListID,CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache)
VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag )

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.'
set @currentTime=GETDATE();

IF @QBFlag = 0 and ((SELECT DATEDIFF(ss,@firstTime,@currentTime))> 240) --240=4 minutes. 270=4:30minutes

BEGIN
PRINT 'It is Sleep time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. '
WAITFOR DELAY '00:01:10.'
set @firstTime=GETDATE();
set @currentTime=GETDATE();
PRINT 'Wake up time ' + CAST(@QBFlag AS VARCHAR(12)) + ' for @firstTime : ' + CONVERT(nvarchar(90), @firstTime, 21) + ' @currentTime :' + CONVERT(nvarchar(90), @currentTime, 21) + ' ------. '
END

FETCH NEXT
FROM curOutput
INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ;
END

--End subroutine for QB insert
CLOSE curOutput
DEALLOCATE curOutput

Workaround 2:

You can use another workaround as below:

1) Insert an Invoice Line along with Invoice header fields with FQSaveToCache as 0. This will create an invoice in QuickBooks.

2) Get the Last Insert ID of the Invoice.

3) Using a loop for the rest of the invoice line, Insert each invoice line to the existing Invoice by Passing TxnID & with FQSaveToCache as 0 in the insert statement. This will update the current invoice and add a new line item.

As the above solution does not use FQSaveToCache, the SQL Server Disconnect issue will not affect it.

Please use the below script:

SET NOCOUNT ON

DECLARE curOutput CURSOR FOR SELECT CustomerRefListID, TemplateRefListID, TxnDate, TermsRefListID, CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSavetoCache FROM dbo.QBQODBC_INVOICEPOSTINGDATA WHERE CustomerRefListID IS NOT NULL ORDER BY CustomerRefListID, FQSavetoCache DESC DECLARE @CustomerID VARCHAR(100)
DECLARE @TemplateID VARCHAR(100)
DECLARE @TransactionDate DATETIME
DECLARE @CustomerTermsID VARCHAR(100)
DECLARE @CustomerMsgID VARCHAR(100)
DECLARE @ItemID VARCHAR(100)
DECLARE @Quantity DECIMAL(18,2)
DECLARE @Rate DECIMAL(18,2)
DECLARE @Amount DECIMAL(18,2)
DECLARE @QBFlag INT
DECLARE @i INT
DECLARE @txnID VARCHAR(100)
SET @i = 0
OPEN curOutput
set @txnID =''
FETCH NEXT
FROM curOutput
INTO @CustomerID,@TemplateID,@TransactionDate,@CustomerTermsID,@CustomerMsgID,@ItemID,@Quantity,@Rate,@Amount,@QBFlag;
--Subroutine for QB insert
WHILE @@FETCH_STATUS = 0
BEGIN

if @txnID =''
begin
INSERT INTO QRemote...INVOICELINE (CustomerRefListID, TemplateRefListID,TxnDate, TermsRefListID,CustomerMsgRefListID, InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache) VALUES( @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 )
--Get the Last inserted it

Set @txnid= (select LastInsertId from openquery(QRemote,'sp_lastinsertid InvoiceLine'))

end

else

begin
INSERT INTO QRemote...INVOICELINE (txnID, CustomerRefListID,TemplateRefListID, TxnDate,TermsRefListID, CustomerMsgRefListID,InvoiceLineItemRefListID, InvoiceLineQuantity, InvoiceLineRate,InvoiceLineAmount, FQSaveToCache)
VALUES( @txnID,@CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, 0 )
end

if @QBFlag=0
begin
set @txnID =''
end

PRINT 'Transaction quantity of ' + CAST(@Quantity AS VARCHAR(12)) + ' for item ID ' + @ItemID + ' for customer ID ' + @CustomerID + ' is now posted.' FETCH NEXT FROM curOutput INTO @CustomerID, @TemplateID, @TransactionDate, @CustomerTermsID, @CustomerMsgID, @ItemID, @Quantity, @Rate, @Amount, @QBFlag ; END
--End subroutine for QB insert

CLOSE curOutput
DEALLOCATE curOutput

Note: The JDBC-ODBC bridge driver was removed in Java 8, preventing ODBC drivers from being usable in newer versions of Java or other Java-based applications.

Refer: Oracle JDBC-ODBC Bridge

How to Use QODBC with NetBeans

Problem Description:

QuickBooks Enterprise comes with a tool called QODBC that enables connecting to a QuickBooks database to read and write data. I have installed it and can successfully see QuickBooks data by connecting with Excel.

I am attempting to be able to read data in Talend Open Studio 5.4.1 by using the Generic ODBC connector. I can get a successful connection message when setting up the database connection, but retrieving the schema to see the tables shows no tables. There are no errors or any other indication - just no table listing.

Solutions:

Please query the table directly instead of loading tables. Use a tDBInput component, use a SELECT query & Click on "Guess Schema."

For Example:

Select * from the customer

Problem Description:

We use a Visual Basic app with an SQL Server database to write data to QuickBooks through QODBC. We are executing two SQL statements as described in your help. We are getting an error saying either:

1) The transactions are not in balance - meaning it seems that both transactions are not being executed at the same time or

2) If we try to combine the two text strings into one SQL server statement, that "missing semicolon at the end of SQL statement."

Solutions:

You are getting a transaction not in balance error because only one statement is executed. You should execute both statements one by one.

You cannot run both statements at the same time. You have to run them one after the other.

Also, the connection should not be closed between the executions of two queries. If the connection is closed, then you will face the error.

Please refer to the below-mentioned sample VBA code for executing two statements one after the other:

Public Sub exampleInsert()

Const adOpenStatic = 3

Const adLockOptimistic = 3

Dim oConnection

Dim oRecordset

Dim sMsg

DoCmd.RunSQL " INSERT INTO JournalEntryCreditLine(refnumber,journalcreditlineaccountreffullname,JournalCreditLineAmount,JournalCreditLineMemo,FQSaveToCache) Values ('1234','operating1',555.00,'test memo transfer cred',1)”

DoCmd.RunSQL " INSERT INTO JournalEntryDebitLine(ref number,JournalDebitLineAccountReffullname, JournalDebitLineAmount, JournalDebitLineMemo, FQSaveToCache) VALUES ('1234', 'operating2',555.00, 'Test Memo transfer deb,' 0)."

sMsg = sMsg & "Record Added!!!"

MsgBox sMsg

Set oRecordset = Nothing

Set oConnection = Nothing

End Sub

Instructions

      The most difficult part of using QRemote in programming is the connection string. The simplest form includes a reference to a DSN. A system DSN called "QuickBooks Data QRemote" for 32-bit applications & "QuickBooks Data 64-bit QRemote" for 64-bit applications is automatically created when QODBC is installed. "QuickBooks Data QRemote" will be used in all examples but can be substituted for any different DSN name you make.

      Normally ADO pools connections. QRemote does not support connection pooling. It is recommended to tell ADO not to do connection pooling on any QRemote connection. This is done with OLE DB Services=-2.

Parameters available on the connection string

IP Address = IP address of QRemote Server.

Port = IP address of QRemote Server.

EncryptionKey = Encryption Key settled in QRemote Server. (If you have set Encryption Key in QRemote Server.) By default, it is blank.

RemoteDSN = Remote DSN which you want to use. By default, it is "QuickBooks Data."

 OLE DB Services=-2

Example of simple DSN
32-bit:
sConnectString = "DSN=QuickBooks Data QRemote;OLE DB Services=-2;"

64-bit:
sConnectString = "DSN=QuickBooks Data 64-bit QRemote;OLE DB Services=-2;"

Example of a DSNless connection string
Without Encryption Key:
sConnectString = "Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;RemoteDSN=QuickBooks Data;"

With Encryption Key:(Replace 123456 with your Encryption Key value)
sConnectString = "Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;EncryptionKey=123456;RemoteDSN=QuickBooks Data;"

Standard syntax
"Driver={QRemote for QuickBooks};OLE DB Services=-2;IPAddress=127.0.0.1;Port=4500;"

Problem Description:

I have a question about How to locate data found in QuickBooks by table and field name inside the QODBC system.

Solutions:

The fastest way to find data in QuickBooks via the QODBC Driver is to use the bait and sync method. Inside QuickBooks, place a unique "bait" value in the field in question, such as find me for text fields or 123321 for numeric fields, and save the data. Then start QODBC Test Tool and connect to the QuickBooks company file. Refer to Connecting QODBC Driver to QuickBooks for the First Time (Video), then do a global search for the "bait" value. The file and field name will be shown if the data is available via QODBC.

Please follow the steps below to locate QuickBooks data by table and field name inside the QODBC system.

1) Add a sample find me to Invoice or bill records (i.e., RefNumber).

For Example, We are creating an Invoice with RefNumber (i.e., Invoice#) findme.


2) Add a sample 123321 in the CreditMemo rate.

For Example, We are creating CreditMemo with Rate 123321.


3) Use the SP_GLOBALSEARCH command to search data from the QuickBooks company file. (Requires v16.0.0.317 or above)

sp_globalsearch SearchValue

For Example:

sp_globalsearch 'find me.'


sp_globalsearch '123321'

Problem Description:

How can I apply a refund check to an existing credit memo using QODBC?

In QuickBooks, I can refund by opening the Create Credit Memos/Refunds dialogue box using the QuickBooks UI and finding the credit memo for that customer. In the dialogue box icon bar, click on "Use the credit to give a refund."

Solutions:

You can use the CheckApplyCheckToTxn table and the Insert Only table to create a check to pay off a credit memo. You can give refunds to an existing credit memo by using the below queries:

INSERT INTO CheckExpenseLine (AccountRefListID, ExpenseLineAccountRefListID, ExpenseLineAmount, ExpenseLineMemo, ExpenseLineCustomerRefListID, ExpenseLineBillableStatus, FQSaveToCache) Values ('20000-933270541', '40000-933270541',80, 'Test Memo,' '800000E2-1513333899', null,1)



Insert into CheckApplyCheckToTxn ( AccountRefListID, PayeeEntityRefListID, TxnDate, ApplyCheckToTxnTxnID, ApplyCheckToTxnAmount) values ('20000-933270541', '800000E2-1513333899',{d'2017-12-18'},'1B3C0-1513338090',80)



After executing the above queries, you can see CreditMemo as Refunded in QuickBooks.



You can get ApplyCheckToTxnTxnID from the TxnID field of the CreditMemo table for that particular Transaction by using the below query.

SELECT TxnID, RefNumber FROM CreditMemo where RefNumber='1268'

Problem Description:

I am trying to insert an Invoice, But I am getting the below error:

[QODBC] Not supported (#10003)

I am using below SQL statements:

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, '', 1)

INSERT INTO "Invoice" ("CustomerRefListID," "ARAccountRefListID," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "BillAddressCountry," "spending," "TermsRefListID," "DueDate," "ShipDate," "ItemSalesTaxRefListID," "Memo," "IsToBePrinted," "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403', '40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', {d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test,' 0, '10000-999022286')

I am getting the below error:

Please let me know what I am doing wrong.

Solutions:

You need to either provide a value for SalesTaxCodeRefListID, remove it, or pass null as a value instead of an empty string from the insert statement.

Your insert statement should be as below:

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('250000-933272656', 'Building permit 3', 3.00000, 3.00, null, 1)

INSERT INTO "Invoice" ("CustomerRefListID," "ARAccountRefListID," "TxnDate," "RefNumber," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "BillAddressCountry," "IsPending," "TermsRefListID," "DueDate," "ShipDate," "ItemSalesTaxRefListID," "Memo," "IsToBePrinted," "CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403', '40000-933270541', {d'2002-10-01'}, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', {d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test,' 0, '10000-999022286')

Problem Description 1:

I am following the below steps and getting the error.

1. First, we inserted the records in Bill & BillExpenseLine table, and the papers got inserted successfully.

2. Second, we are inserting the records in BillPaymentCheckLine, and we are getting the below error

"Error Parsing Complete XML return string."

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555',1, '81D4-1435326671', 88.3)

Please let me know what I am doing wrong.

Solutions 1:

Either remove RefNumber or IsToBePrinted from the insert statement. QuickBooks SDK will allow only one of the fields during insert/update & due to this issue occurring.

Your query should be as below.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555', '81D4-1435326671', 88.3) 

Problem Description 2:

I am trying to link BillItemLine with PurchaseOrder. I am using the below query to connect transactions & I am getting an XML error.

"Error Parsing Complete XML return string."

INSERT INTO BillItemLine ( ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity ) values ('271B-1071512692', '271D-1071512692',300 )

Please let me know what I am doing wrong.

Solutions 2:

If you are trying to link transactions with Bill, you should include vendor details in your query. Please refer to the sample query & test again:

Your question should be as below.

INSERT INTO BillItemLine (VendorRefListID, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity) VALUES ('10000-933272655', '271B-1071512692', '271D-1071512692',30)

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-2000',200.00000,200.00,'20000-999022286',1)

Note: QRemote is a linked server name. It would be best if you changed it with your linked server name.

Suppose you don't set up a linked server in SQL Server. Please refer below-mentioned link for creating the linked server.

For 32-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote

For 64-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote

Second Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-3000',200.00000,200.00,'20000-999022286',1)

Third Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-4000',200.00000,200.00,'20000-999022286',1)

Header Information

INSERT INTO QRemote...InvoiceLine(CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES('160000-933272658', '40000-933270541', {d'2014-10-01'}, '1', 'Brad Lamb,' '1921 AppleseedLane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '20000-933272658', {d'2014-10-31'}, {d'2014-10-01'}, '2E0000-933272656', 'Memo Test,' 0,'10000-999022286')

Results in QuickBooks

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '240000-933272656' and found like this:

     Select ListId, and FullName from QRemote...An item where FullName is like 'F%.'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '160000-933272658' and found like this:

     Select ListId, and FullName from QRemote...Customers where FullName like 'Aber%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, and FullName from QRemote...An account where a Name like 'Account%.'

ItemSalesTaxRefListID

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from QRemote...ItemSalesTax where Name like 'San%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'T%'

Example of adding InvoiceLine items to an existing Invoice

You can add lines to existing Invoices using SQL Linked Server without any issue. Please refer to the sample query for the same:

Insert into QRemote.InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705', '320000-1071525597', '11440DECA', 5,5)

OR

Insert openquery(QRemote, 'select TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnID=''0''') values ('1B308-1513325705','320000-1071525597', '88880DECA', 5,5)

Note: In the INSERT statement, a where should be TxnID=''0''. The TxnID=''0'' predicate is used to avoid retrieving data from the remote server, which can result in slower performance.

Problem Description:

How to get Bank Reconciliation details using QODBC

I've not successfully identified any field/flag identifying the bank reconciliation status. Is this information available thru QODBC? Or has it not been made available?

I'm building a supplemental (to QuickBooks) bank account management/forecasting application. I want to be able to pull bank reconciliation data from QuickBooks (using QODBC) and be able to identify cleared/not cleared bank account transactions. This would be very useful for a client of mine. It's easy to remove all bank account transacbanks (debits and credits) from the Transaction table, which is very much like a bank statement if you order by date. But I've not been able to determine the CLEARED status of each transaction. Is there any way for me to determine this thru QODBC?

Solutions:

The Bank Reconciliation details are unavailable through the Intuit SDK, so they are unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

You can refer ClearedStatus table, which is used to clear checks using QODBC.

The "ClearedStatus" table is Insert Only table. It is used for Insert purposes only.

You can clear check using QODBC by inserting TxnId & check the status in the "ClearedStatus" table. If you want to clear check using QODBC, Please refer to the below steps:

Please open the Check register in QuickBooks & locate the transaction in which you want to update the check status.

For Example, I want to update the check status of the Bill Payment:

To locate the TxnID of the "BILLPMT" check, I ran the following:

SELECT TxnID, AppliedToTxnSeqNo, TxnDate, PayeeEntityRefFullName, Amount FROM BillPaymentCheckLine where RefNumber='294'

Using this TxnID, I can mark the check as cleared by running the following using the INSERT only ClearedStatus table:

INSERT INTO ClearedStatus ("TxnID," "ClearedStatus") VALUES ('39D7-1071529831', 'Cleared')

BTW: The valid values are: |Cleared|NotCleared|Pending|

And the check is now marked as "Cleared" in QuickBooks.

Table Differences:

Number of Tables available in

List of Tables available in QODBC Desktop

List of Tables available in QODBC Online

List of Tables available via API from QuickBooks Online

Report Differences:

Number of Reports available in

List of Reports available in QODBC Desktop

List of Reports available in QODBC Online

List of Reports available via API from QuickBooks Online 

Stored Procedure Difference:

Stored Procedure which is not working in QODBC for QuickBooks Online:

QODBC Desktop Stored Procedures Command-List

Also, Refer

Detailed Difference between QODBC for QuickBooks Desktop and Online.

Tags: QuickBooks Online, QBO, QODBC Online, difference

Problem Description:

QRemote Does not consider FQSaveToCache with working with OdbcCommand & Parameters.

I have an application that creates Sales Orders. This is what I execute for each line item:

Running this code against QODBC DSN works well if everything is local - using a 32-bit local DSN and QuickBooks local to the application. The Sales Order appears correct in QuickBooks.

Taking the same code and connecting it to a 64-bit QRemote DSN connected to a 32-bit qODBC DSN on another machine (via QRemote Server) does not work.

The basic flow is like this:

cnQODBC = New OdbcConnection(ConfigurationManager.AppSettings.Item("QuickBooksConnectionString"))

cnQODBC.Open()

[Repeat for every sales order line item]

Dim cmdQODBC As OdbcCommand = New OdbcCommand("insert into SalesOrderLine (CustomerRefListID, TxnDate, SalesOrderLineClassRefListID, TemplateRefListID, RefNumber, " & "SalesOrderLineItemRefListID, SalesOrderLineDesc, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineAmount, " & "CustomFieldSalesOrderLineOther1, FQSaveToCache) values (??, ??, ????????)", cnQODBC)

cmdQODBC.Parameters.AddWithValue(", "strCustomerListID)

cmdQODBC.Parameters.AddWithValue("", "{d'" & dteInvoiceDate.ToString("yyyy-MM-dd") & "'}")

cmdQODBC.Parameters.AddWithValue(", "strLineClassListID)

cmdQODBC.Parameters.AddWithValue(", "strTemplateListID)

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber)

cmdQODBC.Parameters.AddWithValue("", strLineItemListID)

cmdQODBC.Parameters.AddWithValue(", "strain description)

cmdQODBC.Parameters.AddWithValue(", "intQuantity)

cmdQODBC.Parameters.AddWithValue(", "dblLineRate)

cmdQODBC.Parameters.AddWithValue(", "dblLineAmount)

cmdQODBC.ExecuteNonQuery()

[End repeat]

cnQODBC.Close()

cnQODBC = Nothing

cmdQODBC.ExecuteNonQuery()

Solutions:

Integer, double, and long datatype parameter passing in QRemote pass-through string format because of flow QRemote Client --> QRemote Server --> QODBC Datatype conversion create the problem in QODBC and due to this issue occurred.

The workaround for this problem is to pass value in string format instead of basic format as in the below example:

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber.ToString()) //cmd.Parameters.AddWithValue("", "3");

cmdQODBC.Parameters.AddWithValue("", intQuantity.ToString()) //cmd.Parameters.AddWithValue("", "44.2");

cmdQODBC.Parameters.AddWithValue("", dblLineRate.ToString()) // cmd.Parameters.AddWithValue("", "113.4");

cmdQODBC.Parameters.AddWithValue("", dblLineAmount.ToString()) //cmd.Parameters.AddWithValue("", "0");

Instead of

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber) //cmd.Parameters.AddWithValue("", 3);

cmdQODBC.Parameters.AddWithValue("", intQuantity) //cmd.Parameters.AddWithValue("", 44.2);

cmdQODBC.Parameters.AddWithValue("", dblLineRate) //cmd.Parameters.AddWithValue("", 113.4);

cmdQODBC.Parameters.AddWithValue("", dblLineAmount) //cmd.Parameters.AddWithValue("", 0);

Problem Description:

Trying to insert into SalesOrderLine and getting a 'Multiple-step' error.

INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, ClassRefListID, FQSaveToCache) VALUES ('8000343B-1393961058', '80000008-1377787801 ', 'R1', 1.0000, 500.00, '80000001-1377787801 ', '80000003-1393947522', 0)

OLE DB provider "MSDASQL" for linked server "QBREMOTE" returned the message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "QBREMOTE" could not INSERT INTO table "[QBREMOTE]...[SalesOrderLine]".

Solutions:

You need to change the "Maximum Field Name Length" value from QODBC Setup Screen>> Advanced tab to resolve "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." at the time of insertion.

Please follow the below instruction to change the "Maximum Field Name Length" value.

Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" (If you are using System DSN) or "User DSN" (If you are using User DSN) Tab >> select your DSN>> click "configure">> Advanced >> "Maximum Field Name Length" change to 100.  

Also, you can use openquery, which can insert records in the SalesOrderLine table:

Insert openquery(QBREMOTE,' select CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, FQSaveToCache from SalesOrderLine where 1=0') values ('160000-933272658', '120000-1071512690', 'Wood Door: Exterior', 1.0000, 555.00, '10000-999022286', 0)

Please refer:

How to do INSERTs, UPDATEs, and DELETEs using OPENQUERY with Linked MS SQL Servers

How to use parameters in OPENQUERY

Solutions:

OPENQUERY does not accept variables for its arguments. You need to Pass Basic Values as below example:

Select Query:

DECLARE @TSQL varchar(8000), @ID varchar(25)

SELECT @ID = '19650'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(QRemote ,''SELECT * FROM ReceivePayment WHERE ReceivePayment.RefNumber = ''''' + @ID + ''''''')'

EXEC (@TSQL)

Update Query:

DECLARE @TSQL varchar(8000), @ID varchar(25), @CName varchar(25)

SELECT @ID = '80000146-1513345553'

SELECT @CName = 'New Company'

SELECT @TSQL = 'Update OPENQUERY(QRemote ,''SELECT * FROM Customer WHERE Customer.ListID = ''''' + @ID + ''''''')' + 'SET CompanyName = ''' + @CName + ''''

EXEC (@TSQL)

Please refer below link:
How to pass a variable to a linked server query
Including parameters in OPENQUERY

Problem Description:

I am trying to run a report that returns ItemInventory where LastReceived is NULL. I have tried in vain to accomplish this. What syntax do you use to select a blank/null date, or is there a special function for this?

Solution:

You can select a blank/null date using "IS NULL" in your query.

For Example below query will return a row whose InventoryDate is blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NULL

In the same way, you can use "IS NOT NULL" in your query.

For Example below query will return a row whose InventoryDate is not blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NOT NULL 

Problem Description:

I want to write some select statements on InvoiceLine and SalesReceiptLine that return all records dated WITHIN the past 30 days relative to whatever TODAY is. I'm very familiar with Microsoft SQL syntax and would normally say... WHERE TxnDate >= getdate()-30

How can I reference "30 days ago" using the QODBC driver?

Solutions:

In QODBC, the function CURATE() – Returns the current computer system date as a date value.

For example, for Today, April 18, 2006, when the following query:-

SELECT {fn CURDATE()} as Today, ({fn CURDATE()}-30) as "30 Days Ago", TxnDate, RefNumber, InvoiceLineDesc FROM invoiceline WHERE TxnDate >= ({fn CURDATE()}-30) is run in QODBC Test Tool the results were:

Problem Description:

I need to select only the transactions that occurred in the last 90 days. I used the Access functions Date() And DateAdd() in the Criteria to filter for those transactions, but I am getting the error message "Invalid Procedure Call." Here are the Criteria that I am trying to use:

Between Date() And DateAdd("dd",-91,Date())

What am I doing wrong? Does QODBC have different functions for this?

Solutions:

To write Pass-thru queries for reading and to write QuickBooks® data using QODBC and Microsoft® Access, you must use the proper date format.

You may use Date Macros, but you may also use specifically from and to dates for more flexibility.

This function formats dates with the QODBC format: {d 'YYYY-MM-DD'}. There is no need to remember the form, just the function's name: fncqbDate.



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


Example:

You might use fncqbDate to help create an SQL string with VBA from user input dates.

mySQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly',dateFROM = " & fncqbDate(BegDate) & ", dateTO = " & fncqbDate(EndDate) & " where account like '%checking%'"



Put Some Checks into a Table:

Try this out and put some checks on a table:
1. Copy and paste fncqbDate (first function above) into a module.
2. Copy and paste fncGetChecks (function below) into a module.
3. If you need QuickBooks® to open to using QODBC, open it and ensure you authorized QuickBooks® to communicate with QODBC. 4. Make sure the following references are checked in your Microsoft® Access database:
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library


To use fncGetChecks, call it from a form or type: fncGetChecks into the immediate window of the Visual Basic Editor.

Change the default connection string if necessary by entering your connection string when prompted.

The function will ask for: a name for the new query (make sure this doesn't already exist in your database)

a beginning date

an ending date

Your connection string which may or may not be the default offered



Function fncGetChecks()
On Error GoTo fncGetChecks_err
Dim q As String, Date1 As Date, Date2 As Date
q = InputBox("Give your temporary query a name:", "Temporary Pass-Thru Query", "")
Date1 = InputBox("Enter start date:", "Start Date", FormatDateTime(Now, vbShortDate))
Date2 = InputBox("Enter end date:", "End Date", FormatDateTime(Now, vbShortDate))
Dim db As DAO.Database, qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.ReturnsRecords = True
qd.Connect = InputBox("Enter connection string:", "", "ODBC;DSN=QuickBooks Data;SERVER=QODBC")
qd.SQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account " & _
"parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly'," & _
"dateFROM = " & fncqbDate(Date1) & ", dateTO = " & fncqbDate(Date2) & _
" where account like '%checking%'"
DoCmd.RunSQL "select * into tbl" & q & " from " & q
Set qd = Nothing
Set db = Nothing
DoCmd.DeleteObject acQuery, q
DoCmd.OpenTable "tbl" & q
Exit Function
fncGetChecks_err:

MsgBox Erl & " " & Err.Number & ": " & Err.Description
End Function


Also, refer to the following:

How to Use Prompted Date Ranges in MS Access 2007 using Vista 

Problem Description:

I have the invoice number (RefNumber) from my table outside QuickBooks. But if a QuickBooks user creates invoices directly in QuickBooks, then QuickBooks keeps new incrementing invoices automatically, and the next invoice I create using my outside table will be incorrect.

I need to replicate what QuickBooks does in determining the following QuickBooks invoice number. What do you recommend?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically manage and assign a RefNumber to the newly inserted invoice.

If you would like to specify the RefNumber, and RefNumber is numeric, it should be easy to sync up. Just get the max RefNumber from the Invoice table( of the current year), add 1 to it, and specify it in the insert statement.

Problem Description:

How do I Auto increment the Ref Number for the Invoice?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically increment and assign a RefNumber to the newly inserted invoice.

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Get max and insert RefNumber for the Invoice?

Solutions:

You can get the max RefNumber for Invoice by using the below query & add 1 to the result and can create a new invoice:

SELECT Max(RefNumber) FROM InvoiceLine  

We got 2581 as the max RefNumber. Now we are creating the new invoice with RefNumber 2582.

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('620000-1071526513', '2582', '240000-933272656', 'POWER TRAK-2000', 200.00000, 200.00, '20000-999022286',0)

Problem Description:

What happens If I use the same RefNumber for Invoice creation?

Solutions:

QuickBooks is allowing duplicate RefNumber. So when you use the same RefNumber for Invoice creation. QuickBooks will create an invoice with a duplicate RefNumber/

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Auto Increment Alphanumeric RefNumber for Invoice?

Solutions:

First, create any Invoice from QuickBooks UI & assign the alphanumeric RefNumber "R0001T". Now create a new Invoice from QODBC & do not pass RefNumber. QuickBooks will automatically increment and assign a RefNumber (i.e., "R0002T") to the newly inserted invoice.

Problem Description:

How do I create a note (or a blank line) on the invoice? If I do it the same way that I do it using QODBC for QuickBooks, I get an error :

Error sending txn to QB: 2020: QuickBooks message: Required param missing. We need to supply the required value for the API. QuickBooks message: Required parameter Line.SalesItemLineDetail is missing in the request.

Solutions:

You can create a blank invoice from QODBC Online by using below sample query:

INSERT INTO "InvoiceLine" (CustomerRefFullName,"InvoiceLineItemRefFullName", RefNumber) VALUES ('Aadi Vanhorn','Test','Blank001' )

You must create an item without providing a price/rate in the Item module. Then it would be best if you delivered ItemName in your query. In this example, I have created the Item "Test" & use it in the question. You can use the RefNumber column to insert InvoiceNumber. If you don't want to provide InvoiceNumber, then QuickBooks Online automatically adds the Invoice number to the invoice. Please see the below query without including RefNumber:

INSERT INTO "InvoiceLine" (CustomerRefFullName,"InvoiceLineItemRefFullName") VALUES ('Aadi Vanhorn','Test')

Tags: QuickBooks Online, QBO, Create Blank Invoice, QODBC Online

Problem Description:

I am trying to modify the TxnDeleted table from QuickBooks in QODBC but getting the "[QODBC] Not Supported" error. 

Solutions:

The TxnDeleted table is the read-only table. You can't modify/delete its content.

The insert/Update/Delete feature in the TxnDeleted table is unavailable through the Intuit SDK, so they are unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.   

Problem Description:

I am getting the below error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise.

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

Please see the following SQL statement:

SELECT Item.Name AS SKU, Item.CustomFieldColor AS Item, Item.Description, Item.SalesPrice, Item.PurchaseCost, Item.QuantityOnHand FROM Item Item WHERE (Item.Name<>'IFR' And Item.Name<>'OTW') AND (Item.Description<>'') AND (Item.Type='ItemInventory') ORDER BY Item.Name

The above statement is working fine in QODBC Test Tool, MS Access. But I am facing the issue in MS Excel.  

Solutions:

MS Excel has some issue when you alias in the query. When you try to use Microsoft Query to return data from some third-party databases into Microsoft Excel, apostrophes (') around alias names can cause the query to fail.

Please refer below mentioned link to resolve this issue:

Using a field alias in Query does not work with some third-party databases

You can either apply hotfix or change registry values.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

The result after changing registry values & execute the query again:

Problem Description:

I've linked all the QODBC tables into an Access 2010 DB. I have created table subset queries on the original tables and would like to do selects based on date ranges - other things, too, like JOINs - but I'm stuck with this problem. The minute I give Access a date constraint like receive payment.TxnDate >=#11/1/2014# as stated in your "How Dates Are Formatted.." documentation for Access Date formats, Access immediately crashes. No msgs in the brief QODBC log files about the crashes, as best I can tell:

Solutions:

Please try executing the following command using QODBC Test Tool to see if it can solve the issue: sp_optimizefullsync ALL.

This command will bring some data to a local cache to increase query retrieval performance. I want to suggest you, please let me run the above command entirely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of documents in the QuickBooks company file. Please let this command run entirely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.

After successfully executing the above command, please create a new MS Access Database file & link all tables & execute the query again. It should work without any issues.

Please refer: How to Use QODBC with Microsoft Access 2010

Problem Description:

How do I access the alternate email address 1 and 2 and the cc email fields? I don't see them at the customer table.

Solutions:

You can get CC Email details from the "Cc" field in the Customer & Entity table.

The Alt Email 1 and 2 field is not available through the Intuit SDK, so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.

Problem Description:

I have QuickBooks 2014 version & I am using QODBC's latest version. I have a problem with querying the customer table using QODBC. 

QODBC driver consistently gets stuck on "Find Next Record" at record #9000. I have tried the following: 

- Reset the optimizer file

- Rebuild the QuickBooks company file

- sp_optimizefullsync Customer: this crashes QuickBooks or does not complete correctly. There are still records missing from the Customer table that appear in QuickBooks.

- Query customer table using UNOPTIMIZED keyword. Unoptimized hangs at about record 9000 and does not return the Customer

On QuickBooks SDK logs, I noticed the below error:

20140923.175608 E 3888 QBSDKProcessRequest *** INTERNAL ERROR WHEN PROCESSING THE QBXML REQUEST ***.

Solutions:

There might be some internal error that occurred during the processing request. To resolve this error, you need to get in touch with Intuit.

Please restart QuickBooks & try again, as you are saying that the query is stuck on #9000. There might be some issue with the company file. Please test the same on another company file or a sample company file to see if the problem is related to the company file.

Also, try changing the Iterator value to 100 on QODBC Setup Screen--Advanced Tab.

If you are still facing the same error, there might be an issue with your company file that would require repairing the company file & you need to get in touch with Intuit.

Problem Description:

I am getting the error "Error 3250 - This feature is not enabled or not available in this version of QuickBooks." while I am trying to insert an Invoice in QuickBooks using QODBC. Below is my insert statement:

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineGroupItemGroupRefListID, InvoiceLineGroupQuantity, InvoiceLineSalesTaxCodeRefListID, InvoiceLineLotNumber, FQSaveToCache) VALUES ('670000-1071517519', '91047', '320000-1071525597', 'POWER TRAK-2000', 200.00000, 200.00,'300000-933272656',11, '20000-999022286', 'L123',0)

Solutions:

QuickBooks does not allow to use LotNumber when Advanced Inventory is Turned Off. QuickBooks SDK will throw the error "This feature is not enabled or not available in this version of QuickBooks."

The Error message will depend on the operation you are performing.

In this case, you need to enable the Advanced Inventory module & try again.

There might be other possibilities for this error. Please refer below possibilities:

If the Units of Measure feature is not enabled & you are trying to use it in your query.

If the Class feature is not enabled & you are trying to use it in your query. The QuickBooks company file you are using is not set to allow assigning classes to names. This preference must be turned on to include the ClassRef section of your query.

To resolve this error, You need to enable the feature you are using from QuickBooks & try again.   

Problem Description 1:

We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [42S00] [QODBC] Insert value must be a simple value. We have other customers that can import credit memos, and I have compared the imported data and cannot find any difference in the data format. Can you give me an idea of what to look for?

Solutions 1:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VB code generates this error. It is not a QODBC error.

Please verify your insert statement & try again. Please refer below-mentioned link to a sample VB code:

Examples of How to Use QODBC via Visual Basic

Please refer to the below-mentioned article for creating a credit memo:

How to create Credit Memos

Problem Description 2:

When I go to insert a Customer, I receive an error saying [QODBC] Insert value must be a simple value. I need to know what I am doing wrong to insert a new customer.

Please refer below code which I am using:

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim address postal code As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

"INSERT INTO customer(name, firstname, lastname, companyName, contact, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email)VALUES(name, firstname, lastname, companyName, contact, billAddressAddr1, billAddressAddr2, billAddressAddr3, billAddressCity, billAddressState, billAddressPostalCode, phone, fax, email)")

LogEntry("New Customer Added to QuickBooks")

Catch e As an Exception

MsgBox(e.ToString)

End Try

' Close the database.

rs.Close()

rs = Nothing

conn.Close()

conn = Nothing

'*********************************************************************************************************************

End Sub

Solutions 2:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VBA code generates this error. It is not a QODBC error.

There is a problem with your Insert statement. You cannot pass the value directly. Your insert statement should be like as below.

INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')

Please refer to the updated code & try to insert the record with this code.

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim addressPostalCode As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

sSQL = "INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open sConnectString

' Create a new record.

rs = conn.Execute(sSQL)

sMsg = sMsg & "Record Added!!!"

MsgBox msg

' Close the database.

Set rs = Nothing

Set conn = Nothing

'*********************************************************************************************************************

End Sub

Problem Description:

I'm trying to create a new table with VB Demo or QODBC Test Tool.

Can you send me a sample query to do this?

Solutions:

QODBC is an ODBC driver. Please remember that QODBC is not a database but rather a translation tool. QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools;

You cannot create a new table from VB Demo or QODBC Test Tool because QODBC is just reading and writing to the QuickBooks Company file via the QuickBooks SDK. QuickBooks SDK provides data in XML format, and QODBC converts it to a table format.

What you are trying to do is a new table in the QuickBooks Company file, which is not supported. Thus, you cannot create/drop/alter table(s) from the VB Demo or QODBC Test Tool.

Keywords: adding in a column, adding in a table, how to add a new table, how to add a new column

When inserting an invoice via a looping SQL procedure, I have invoice lines (InvoiceLine table) items that can potentially be assigned to varying classes. Using the ClassRefListID field, I class the lines accordingly and do not address the ClassRefListID in the main invoice (header/parent) table. When I examine the invoice in QuickBooks, it seems that ALL invoice line items have been forced to the SAME class (it looks like the class I last defined in the last child InvoiceLine item). I need to insert invoices with lines pointing to DIFFERENT courses, as I can manually do so in QuickBooks. Is this possible using QODBC????

Solution:

You are correct. When you create or add InvoiceLine, QODBC will always use the last class referenced in the invoice (if applicable). For example, this three-line invoice:

First Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '60001-1019495266', 'QODBCClass1', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 1)

Note:  QODBC Support Wizard is used only to test QODBC SQL queries and is not a development tool.

Second Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '80001-1026828072', 'QODBCClass1', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881', 1)

Third Line

INSERT INTO "InvoiceLine" ("CustomerRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', 'QODBCClass1', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881', 0)

Incorrectly appears in QuickBooks with all three invoice lines showing the "Manual Pool Systems" Class: 

So, you will need to use Plan B:

To get around this, you can first create the invoice and then add invoice lines and specify the class to use or not to use, like this:

INSERT INTO "InvoiceLine" ("CustomerRefListID," "ClassRefListID," "RefNumber," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('4C0000-1040154668', '60001-1019495266', 'QODBCClass2', '670004-1044572237', 'Building permit Additional Room,' 100.00000, 100.00, '10000-1011136881', 0)

Once the invoice is created, we can locate the TxnID using the QODBC stored procedure (as long as we haven't broken our QODBC connection):

SP_LASTINSERTID InvoiceLine

The TxnID for the new invoice is 8D0E-1481798988, so we can now add additional lines to the invoice by doing the following:

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '80001-1026828072', '670004-1044572237', 'Bin Permit Renovations,' 200.00000, 200.00, '10000-1011136881')

INSERT INTO "InvoiceLine" ("TxnID," "ClassRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineSalesTaxCodeRefListID") VALUES ('8D0E-1481798988', '', '670004-1044572237', 'Less Council Rebate,' -50.00000, -50.00, '10000-1011136881')

The invoice now appears in QuickBooks with different Class values for each line:

Problem Description 1:

I am running ProfitAndLossBudgetPerformance through MS Query & It is returning no rows. But using QuickBooks UI, I can get records from the ProfitAndLossBudgetPerformance report. I am executing the below query:

Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

There is no error popup occurring. From the QODBC logs, I noticed the below error:

2014-06-18 19:39:03 QODBC Ver: 14.00.00.302 *********************************************************************************************************************
IsAService: False
SQL Statement: Sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d'2013-01-01'}, DateTo={d'2013-02-20'}, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'
Error Getting XML in BuildAndLoadXMLForNextRecord
Input XML:

<?xml version="1.0" encoding="ISO-8859-1"?>

<?qbxml version="11.0"?>

<QBXML>

<QBXMLMsgsRq onError=" continue on error">

<BudgetSummaryReportQueryRq requestID="1">

<BudgetSummaryReportType>ProfitAndLossBudgetPerformance</BudgetSummaryReportType>

<BudgetCriterion>AccountsAndClasses</BudgetCriterion>

<ReportPeriod>

<FromReportDate>2013-01-01</FromReportDate>

<ToReportDate>2013-02-20</ToReportDate>

</ReportPeriod>

<SummarizeBudgetColumnsBy>Date</SummarizeBudgetColumnsBy>

<SummarizeBudgetRowsBy>Class</SummarizeBudgetRowsBy>

</BudgetSummaryReportQueryRq>

</QBXMLMsgsRq>

</QBXML>

Problem Description 2:

Is there any way to have a date prompt in this stored procedure instead of a fixed fiscal year? I am running the query into Excel using Microsoft Query. It appears to give me information regarding the current month (i.e., February info since this is the month of February) for the fiscal year identified in the parameters. Is there any way I can replace FiscalYear with a FromDate and ToDate parameter?

Solutions:

As per QuickBooks SDK, the parameter "fiscal year" is required in the ProfitAndLossBudgetPerformance report. So there is the parameter "fiscal year" is missing in the SQL statement. After including the required parameter, you can get all records from the ProfitAndLossBudgetPerformance report. Please refer below query:

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom={d '2013-01-01'}, DateTo={d '2013-02-20'},FiscalYear=2013, BudgetCriterion='AccountsAndClasses', SummarizeBudgetColumnsBy='Date', SummarizebudgetRowsBy='Class'

Please refer to the article mentioned. You can get detailed information about how to execute sp_report in QODBC:

How to use the QuickBooks Reporting Engine with QODBC

Problem Description:

Is there a stored procedure that provides all deleted transactions? I.e., Voided/Deleted Transactions Detail?

For Example:

If an Invoice gets deleted from QuickBooks, does it get physically deleted? Otherwise, which field(s) would tell me that fact?

Solutions:

You can find Voided/Deleted Transactions details from QODBC "TxnDeleted" and "ListDeleted" tables. Please refer to tables "TxnDeleted" and "ListDeleted" to see the data you are looking for.

For Example, I have an Invoice in QuickBooks whose Invoice# is 1114  

Now we are verifying the same Invoice from the QODBC Invoice table. By using the below query:
SELECT * FROM Invoice where RefNumber ='1114'

Now we are deleting the Invoice from QuickBooks, whose Invoice# is 1114: 

Now we are verifying the same Invoice from the QODBC Invoice table to see if it exists in the QODBC Invoice table. By using the below query:
SELECT * FROM Invoice where RefNumber ='1114'

Now we are checking the same Invoice from the QODBC TxnDeleted table to see if it exists in the QODBC TxnDeleted table. By using the below query:
SELECT * FROM TxnDeleted where TxnDelType='Invoice' 

We can find Deleted Transactions (i.e., Invoice, SalesOrder, TimeTracking, etc..) details from QODBC "TxnDeleted" tables.

We can find Deleted List (i.e., Customer, Item, etc..) details from QODBC "ListDeleted" tables.

An Example of Creating One ItemInventoryAssembly with Two Lines

First, please create a new MS Access database & link ItemInventoryAssembly, and ItemInventoryAssemblyLine tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.
Note the FQSaveToCache field, set to True, except on the last one.

First Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '160000-933272656', 2.0, 1)

Second Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '130000-933272656', 1.0, 0)

Result in QuickBooks  

Questions and Answers

Question

     This seems to work for adding multiple items (ItemInventoryAssemblyLine records) to an assembly when the assembly is first created.

     However, I am having trouble adding new ItemInventoryAssemblyLine records to an existing assembly. When I try to repeat an INSERT with FQSaveToCache set to 0, I get an error.

     QODBC Error 3100 - The name "NewAssembly1" of the list element is already in use (#10053)

Answer

   I don't think we can add items to an existing assembly. The workaround would be to create a new assembly with the additional items. 

Problem Description:

I want to add new assemblies to the item list. I've tried the two queries below. Both result in this error:

invalid operand for the operator: <assignment>
code 11024
Can you suggest a query that will work? 

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName) VALUES ('12082-74262', 'Active,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset'); 

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass) VALUES ('12082-74262', 'Active,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 'TW,' 'am');

Solution:

Due to this issue, the IsActive field contains a boolean datatype & it accepts 1/0 or true/false as input. I have used 'Active' as a value in the IsActive field. Please use the below query & try again.

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 1)

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass, FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 'TW,' 'am,' 0)


Please refer below-mentioned link for getting details about the QODBC Table schema:

QODBC Data Layouts

Problem Description:

I have exported the customer list from QB, and Now I want to insert these using the insert query. But some of them fail because of the apostrophe (').
How to deal with special characters like this.

Solutions:

I want to inform you that you can get an additional quote for your query.
For Example, If you want to add a customer whose name contains extra quotes, then you can insert the name using the below query:
Insert into customer (name) values ('Larry's Custom Invoice ')
The above question will create a customer entry named Larry's Custom Invoice.        

I'm working in PHP and using the QODBC Test Tool. I am trying to Insert value into the Customer table.

But I am getting the below error:

Expected lexical element not found:

My insert statement is:

Insert into Customer ('CompanyName,' 'Phone,' 'Email') values('adsf,' '235632', 'afdsf');

Won't work for me. Any suggestions as to why?

Solution 1:

QODBC will issue this error when there is a syntax error in your SQL statements.

Your insert statement should be like this:

Insert into Customer ("CompanyName," "Phone," "Email") values('Test,' '235632', 'abc@def.com');

Problem Description 2:

I have constructed an insert statement that is getting this error. To narrow it down, I modified the values portion of my query to be a select inserted and tested the different data types I was trying to select.

After doing so, I was able to determine that the problem is with both the ts and d functions that we have here, and I had to assume that it was the input's fault and not yours since the process is referenced everywhere. I seemed to remember reading somewhere that you guys use the computer's regional time settings to figure out how to parse out times, but after my test, I found this not to be the case.

For example
select {ts'8/20/2006 9:26:15 AM'} from customer
and
select {d'9/15/2006'} from customer
both give the lexical element error.

My "Regional and Language settings" in "Control Panel" say my date-time format looks like so:

Time: 10:04:29 AM
Short Date: 8/10/2006
Long Date: Thursday, August 10, 2006

I was hoping that you guys were paying attention to it. After all, that's what .NET does, and it seems to work great.

select {d'2006-06-26'} from customer

This results in "6/26/2006 12:00:00 AM," which is expected, but having to format the time for the select/insert/update/delete statement so that it can be formatted back seems like a lot of overhead...

For those of us using .Net (or any language that supports date/time formatting via a format string), you should be able to use the following:

String date= System.DateTime.Now.ToString("yyyy-MM-dd");
string datetime= System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss tt");

Which will give you the following:
2006-08-10
and
2006-08-10 10:10:39 AM

Respectively, which can be used in your SQL Statement afterward.

Solutions 2:

QODBC will issue this error when there is a syntax error on your SQL statements.

So please check your statements & try again.

QODBC uses the standard SQL Date {d'YYYY-MM-DD'} and Timestamp {ts 'YYYY-MM-DD HH:mm: SS.zzz'} formats and is the same worldwide regardless of the region: See also: How are dates formatted in SQL queries when using the QuickBooks generated time stamps

How can I add blank InvoiceLine in QuickBooks using QODBC?

Solution:

There are empty Invoice line records in QuickBooks, and QODBC can view them too, but you may not be able to create an empty InvoiceLine in QODBC.

You cannot create an empty Invoice either in QODBC or in QuickBooks. But in QuickBooks, you could leave lines blank and ensure at least one line of data in your invoice to create empty line records.

But in QODBC, Invoice lines are inserted by single queries. When you insert a line record, QODBC will automatically generate an invoice to contain this line item.

When the line record is null, there will be no record of the new invoice, which is not allowed by QuickBooks. So QODBC could not create an empty InvoiceLine record.

The empty InvoiceLine record must be ItemGroup/InvoiceGroupLine. Please refer: To how to Invoice ItemGroups using QODBC.

I am attempting to apply payments to invoices. I have also determined the TxnID of the Invoice I want to apply a payment to and used it in the SQL code specified on How to apply a payment to an invoice.

I am getting the error 'Error: 3120 - Object "43AC05E5-1352851019" specified in the request cannot be found.'

I have checked that I have the correct TxnID. I can do a "SELECT * from Invoice WHERE TxnID='43AC05E5-1352851019, '" and it works. It's just when I try the Insert Into operation that things fail.

I have confirmed that I can successfully write to QuickBooks by creating an unapplied payment. It is just when trying to apply the payment to a specific invoice.

I created a new unapplied payment and then tried applying that payment to an open invoice, which didn't work either.

My SQL Statement:
INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, Memo) Values ('80000CDC-1275674980', '4D0000-1095982517', 1414.02, '43AC05E5-1352851019', 1414.02, 'Payment for Invoice test')

Also, I verified the invoice 43A96469-1351099713 belongs the customer 80000DC6-1286995152

Solution:

1) The Invoice or the Bill is already paid

OR

2) The Invoice or the Bill you are trying to adjust does not belong to the same customer/vendor as the Payment or Check is

OR

3) The ID you have specified does not exist.

OR

4) Missing ARAccount

Your query is missing ARAccount related column.

You need to insert ARAccountRefListID in your insert query.

Please add the ARAccountRefListID of the invoice you want to apply for payment in your query.

The ARAccountRefListID of the invoice you want to apply the payment to is "400000-1095829128", So please add this value to your insert query.

Please try to run the below query:

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, ARAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, Memo) VALUES ('80000DC6-1286995152', '4D0000-1095982517', '400000-1095829128',281.98, '43A96469-1351099713', 281.98, 'Apply payment test')

Problem Description:

When executing a simple query like:

SELECT * FROM Invoice

Application and QuickBooks will both get hang, and it Does Not Respond.

Then After, the application is forced to close, QuickBooks becomes normal again.

Solution:

This may be the case of your Optimizer file being corrupted or the QuickBooks Company file needing Rebuild/Repair.

Close All applications using QODBC & un-select "Use Optimizer" (QODBC Setup Screen->Optimizer Window->Uncheck "Use Optimizer" option).

Then use our QODBC Test Tool to test again to see if the problem is still there.

If this does not resolve the issue, you may have to rebuild your company file. Please refer to how to rebuild a QuickBooks Company File.

And if QODBC works with the optimizer turned off, then there might be a problem/corruption in your optimizer file.

You can click "Reset Optimizer File" and then execute "the sp_optimizefullsync ALL" command in the QODBC Test Tool:

Refer:  How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables

Otherwise, follow the steps.

You need to reset your opt file store path. You can find the path setting at QODBC Setup Screen->Optimizer Window.

The default path is "%UserProfile%\QODBC Driver for QuickBooks\Optimizer". Please set it to "%appdata%\QODBC Driver for QuickBooks\Optimizer" or use another folder that you have full authority to access it.

After the path setting is completed,

Navigate to that Path,  Start -> Run ->, and then type the Optimizer file path, Delete the.OPT ( corresponding to your company file )

Please run this command in QODBC Test Tool:

sp_optimizefullsync all

This command will re-optimize all QuickBooks data and create a new OPT file in the folder you assigned.

Refer:  How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables

If you are using a connection string or linked table in MS Access to connect to QuickBooks, please remove the former connection string or linked table and re-build a new one to make sure that it points to the new opt file store path.

Instruction:

Under QuickBooks, when saving a Journal Entry record, the Debit total should match the Credit total.

Even thou you are not changing the amount and just updating other information on the Credit/Debit entry, You may encounter "Transaction not in balance":

UPDATE JournalEntryDebit Line SET journaldebitlineaccountreflistid='8000008F-1197954502', journaldebitlineaccountreffullname='Note Payable - Bank of Anycity', journaldebitlineamount=35000 where txnid='5E40-1197715323'

This query will return an error like this:

Solution:

To make the Journal Entry update query work, you should add the FQSaveToCache field into your update query and Updated BOTH
JournalEntryDebitLine &  JournalEntryCreditLine

When FQSaveToCache is set to value 1, all changes will be stored in a cache instead of directly sending to QuickBooks, and when it is set to 0, all updates in the cache will be sent to QuickBooks in one time.

Please modify the query like this:

UPDATE JournalEntryDebitLine SET journaldebitlineaccountreflistid='8000008F-1197954502', journaldebitlineaccountreffullname='Note Payable - Bank of Anycity', journaldebitlineamount=35000, FQSaveToCache =1 where txnid='5E40-1197715323'

UPDATE JournalEntryCreditLine SET journalcreditlineaccountreflistid='8000008F-1197954502', journalcreditlineaccountreffullname='Note Payable - Bank of Anycity', journalcreditlineamount=35000, FQSaveToCache=0 where txnid='5E40-1197715323'

Please notice that I added the FQSaveToCache field in my update query and set the first value to 1 and the last value to 0 so that the update can be done at the same time to avoid an imbalance problem.

Note: Generally, the field FQSaveToCache is used with the INSERT Statement, but it can work with the UPDATE statement only for these tables. 

Here is the data in QuickBooks before I run the update:

Here is the data in QuickBooks after I run the update:

Problem Description 

When trying to execute a query statement, I get the error message " Expected lexical element not found."

Solutions

It seems to be the issue in the SQL Statement. Please check all the field names and table names. Usually, this could be a typo error in the field name in your SQL Statement. To know more about the fields and data layout, please Click Here 

Another Possible Problem & Solution:

Problem Description:

When I issue SQL statement:

SELECT Desc FROM Charge 

I get "Expected lexical element not found: = <identifier>."

However, if I issue

Select * from Charge

Then, I get a full output with one of the columns named "Desc."

Why can't I query for the column by name?

I have tried this through the QODBC Support Wizard and a C# program. 

Problem Description:

I am trying to clear a date column for the JobStartDate and I am getting an error. Any help would be great, I could not find a help article on this.

I am using .net if that matters.


Dim jobStartDate As Date? = Nothing

Dim cmd As New OdbcCommand($"
UPDATE customer
SET jobStartDate = {jobStartDate}
WHERE ListID = '{listID}' ",
con)

cmd.ExecuteNonQuery()


ERROR [42000] [QODBC] [sql syntax error] Expected lexical element not found: = CASE


Dim jobStartDate As Date = Nothing

Dim cmd As New OdbcCommand($"
UPDATE customer
SET jobStartDate = {jobStartDate}
WHERE ListID = '{listID}' ",
con)

cmd.ExecuteNonQuery()

Message = "ERROR [42000] [QODBC] Unexpected extra token: :00:00"

SELECT Desc FROM Charge 

I get "Expected lexical element not found: = <identifier>."

However, if I issue

Select * from Charge

Then, I get a full output with one of the columns named "Desc."

Why can't I query for the column by name?

I have tried this through the QODBC Support Wizard and a C# program.