Problem Description

Upon pulling QuickBooks data into Microsoft Access, I didn’t see any values (all blank) in the QuantityonOrder field of either the Item or ItemInventory tables from QuickBooks.

Sp_report InventoryStockStatusByItem show QuantityOnOrder
Sp_report PurchaseByItemDetail

And I’ve attached a screenshot of my results for both – each showing 0 results.

I am using QuickBooks 2023.

QuickBooks SDK Logs

20230411.111122 I 30056 SpecVersion Current version of qbXML in use: 16.0
20230411.111122 I 30056 QBSDKMsgSetHandler QUERY: General Summary Report
20230411.111140 E 30056 ReportHandler::BuildTheRetObject Unable to convert column desc for column 2 since it has value 210
20230411.111140 E 30056 ReportHandler::BuildTheRetObject Unable to set the column description, HRESULT=0x80004005
20230411.111140 I 30056 QBSDKMsgSetHandler Request 1 completed successfully.

Response from QuickBooks SDK

<QBXML>
<QBXMLMsgsRs>
<GeneralSummaryReportQueryRs requestID="1" statusCode="510" statusSeverity="Warn" statusMessage="Unable to return object." />
</QBXMLMsgsRs>
</QBXML>

I have tried QuickBooks Verify & Rebuild the company file, but that did not resolve the issue.

Solutions

We have reported the issue to Intuit. Case 00129682: Unable to convert column desc for column 2 since

The issue is a bug in QuickBooks SDK - QuickBooks 2023.

Affected reports -  InventoryStockStatusByItem, InventoryStockStatusByVendor & InventoryValuationDetail

On May 26, 2023, the Intuit Team resolved the reported issue and released the fix for the issue.

Please update your QuickBooks to the latest release to fix the issue.

Updates:

Apr 18, 2023 & Apr 10, 2023 - Intuit Developer Support - "We didn't create a bug for this issue, this is a known issue. We are working on it."

"To unblock the customer as a workaround, we can disable the categories from the preferences and continue.

To locate the categories feature, go to edit->preferences->items&inventory-> Company Preferences-> under categorize items group box, uncheck "Activate categories". Impact of disabling this feature: Customer will go back to their items not being categorised as before

Turning off the Categories feature from the preferences will not have any impact on the ability to connect to the database to read inventory or any items. They will lose the ability to use the feature benefits till the fix is live.

Note: Customers have an option to turn on/off Categories as per their requirements, it’s not mandatory."

Intuit Case ID: 00129682

May 26, 2023  - Intuit Developer Support - "The fix is live, you can use the stock status report with categories."

Issue:

We recently upgraded our Windows OS to Windows 11 and SQL Server to 2019.

We can connect from SQL Server to QODBC/QRemote.

We can execute select statements.

We are experiencing an issue when updating a record. This is working correctly in Windows 10 and SQL Server 2017. The following is the SQL statement we are using.

Update QRemote...Bill set Memo='Sent' where TxnID='12-1234-56789'

Reason:

There are some changes in the standard ODBC API in Windows OS 11 or Windows Server 2019 when using SQL Server.

The issue is caused because QODBC received the SQL statement from SQL Server.
Update QODBC.Bill set Memo='Sent' where TxnID='12-1234-56789'
instead of
update QODBC.Bill set Memo='Sent' where TxnID='12-1234-56789'

Solution:

The issue has been identified and resolved in QODBC build 346.

Please install the latest version of QODBC.

QODBC Desktop - https://qodbc.com/download/desktop

QODBC Online - https://qodbc.com/download/online

QODBC POS - https://qodbc.com/download/pos

Problem Description

I am using Microsoft Excel.

I am using the following SQL statement.

SELECT TransferInventoryLine.TxnDate,
TransferInventoryLine.RefNumber,
TransferInventoryLine.FromInventorySiteFullName,
TransferInventoryLine.ToInventorySiteFullName,
InventorySite.SiteAddressAddr1,
InventorySite.SiteAddressAddr2,
InventorySite.SiteAddressAddr3,
InventorySite.SiteAddressAddr4,
InventorySite.SiteAddressCity,
InventorySite.SiteAddressState,
InventorySite.SiteAddressPostalCode,
InventorySite.SiteAddressCountry,
TransferInventoryLine.Memo,
TransferInventoryLine.TransferInventoryLineQuantityTransferred,
TransferInventoryLine.TransferInventoryLineItemRefFullName,
Item.Description
FROM (TransferInventoryLine unoptimized
INNER JOIN InventorySite unoptimized
ON TransferInventoryLine.ToInventorySiteListID = InventorySite.listen)
INNER JOIN Item
ON TransferInventoryLine.TransferInventoryLineItemRefListID = Item.ListID
WHERE (TransferInventoryLine.TxnDate={d'2020-09-15'})

Solutions

We can reproduce the issue. The issue can be produced in QODBC build 336 and above.

We are working on fixing the issue.

As a workaround, please change the SQL statement.

SELECT TransferInventoryLine.TxnDate,
TransferInventoryLine.RefNumber,
TransferInventoryLine.FromInventorySiteFullName,
TransferInventoryLine.ToInventorySiteFullName,
InventorySite.SiteAddressAddr1,
InventorySite.SiteAddressAddr2,
InventorySite.SiteAddressAddr3,
InventorySite.SiteAddressAddr4,
InventorySite.SiteAddressCity,
InventorySite.SiteAddressState,
InventorySite.SiteAddressPostalCode,
InventorySite.SiteAddressCountry,
TransferInventoryLine.Memo,
TransferInventoryLine.TransferInventoryLineQuantityTransferred,
TransferInventoryLine.TransferInventoryLineItemRefFullName,
Item.Description
FROM TransferInventoryLine, InventorySite, Item
where TransferInventoryLine.ToInventorySiteListID = InventorySite.ListID
and TransferInventoryLine.TransferInventoryLineItemRefListID = Item.ListID
and TransferInventoryLine.TxnDate={d'2020-09-14'}

Click here to raise a support ticket and request if you still face the issue.

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

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.

Download Sample

Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

It would be best if you opened QuickBooks & load the company file in QuickBooks.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

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

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.

'QuickBooks Data' as an example:

The first time you used MS Excel to access QuickBooks. You will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel spreadsheet.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

Now we will add a button & we will write code for the button click event. On button click, it will open the connection, refresh the report data & close the connection.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open the connection, refresh the report data & close the connection.

Private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from QuickBooks Data"). Refresh
End Sub

Note: Query from QuickBooks Data is the name of the connection. You can get it from the Data--> Connections.

Report Data will be refreshed by clicking the Refresh Data button.

Download Sample

In this example, we will show you how to insert an Invoice in QuickBooks using the data available in an Excel spreadsheet.

We have an Excel spreadsheet that contains invoice-related information. The Excel spreadsheet has a CustomerRefFullName, RefNumber, TxnDate, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefFullName, and FQSaveToCache information.

Using this information, we will create an Invoice in QuickBooks, which has 4 line items.

Now we will add a button & we will write code for the button click event. On button click, it inserts the Invoice in QuickBooks.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to ADD Invoice—double-click on the command button to write code.

Write the code below for inserting an invoice in QuickBooks from an Excel spreadsheet.



Private Sub CommandButton1_Click()

Dim sh As Worksheet
Dim row As Range
Dim RowCount As Integer
Dim CustomerRefFullName As String
Dim RefNumber As String
Dim TxnDate As Date
Dim InvoiceLineItemRefFullName As String
Dim InvoiceLineDesc As String
Dim InvoiceLineRate As Single
Dim InvoiceLineQuantity As Single
Dim InvoiceLineSalesTaxCodeRefFullName As String
Dim FQSaveToCache As Boolean
Dim connection As New ADODB.Connection
Dim sConnectString
Dim sSQL As String
Dim rs
Dim sMsg

RowCount = 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 sh = ActiveSheet
For Each rw In sh.Rows

If (RowCount > 0), Then
CustomerRefFullName = sh.Cells(rw.Row, 1).Value
If (CustomerRefFullName = "") Then
Exit For

End If

RefNumber = sh.Cells(rw.Row, 2).Value
TxnDate = sh.Cells(rw.Row, 3).Value
InvoiceLineItemRefFullName = sh.Cells(rw.Row, 4).Value
InvoiceLineDesc = sh.Cells(rw.Row, 5).Value
InvoiceLineRate = sh.Cells(rw.Row, 6).Value
InvoiceLineQuantity = sh.Cells(rw.Row, 7).Value
InvoiceLineSalesTaxCodeRefFullName = sh.Cells(rw.Row, 8).Value
FQSaveToCache = sh.Cells(rw.Row, 9).Value

rs.AddNew
rs("CustomerRefFullName") = CustomerRefFullName
rs("RefNumber") = RefNumber
rs("TxnDate") = TxnDate
rs("InvoiceLineItemRefFullName") = InvoiceLineItemRefFullName
rs("InvoiceLineDesc") = InvoiceLineDesc
rs("InvoiceLineRate") = InvoiceLineRate
rs("InvoiceLineQuantity") = InvoiceLineQuantity
rs("InvoiceLineSalesTaxCodeRefFullName") = InvoiceLineSalesTaxCodeRefFullName
rs("FQSaveToCache") = FQSaveToCache
rs.Update

End If

RowCount = RowCount + 1
Next rw
sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

rs. Close
oConnection.Close

End Sub

On clicking on the ADD Invoice button, the Invoice will be added to QuickBooks.

Invoice Added.

You can see the same in the QuickBooks Invoice that is added.

Download Sample

There is a limitation with the QuickBooks Desktop SDK: it can connect only one company file to a user session at a time.

It means you cannot access multiple company files at the same time.

You need to close the connection with one company file and switch to another. ( i.e., connection closed for company A and Connection open for company B should have a gap of 15-30 seconds for switching operation.)

This applies to QuickBooks Desktop. When using QuickBooks Online, you can connect to multiple company files simultaneously.

In this article, we will connect to two different QuickBooks Desktop company files using two different QODBC DSNs with auto login configured. In this example, we will use two auto logins, DSN CompanyA & CompanyB.

Please Refer:
How to set up QODBC Desktop for Auto-login into QuickBooks Desktop
How to set up the QODBC Driver to auto-connect to QuickBooks Desktop. (Video)

Here is my DSN configuration for the First QuickBooks Desktop Company file.

It would be best if you opened QuickBooks Desktop & loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

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. 

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyA' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet.

Repeating the above steps will export another report in sheet 2 using DSN CompanyB.

We need to close the first company file, which is open in QuickBooks Desktop.

Here is my DSN configuration for the Second Company file.

It would be best if you opened QuickBooks Desktop and loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Navigate to sheet2
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyB' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Profit & Loss report query.
sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet 2.

After the export is complete, close the company file opened in QuickBooks Desktop.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

We need to disable auto-refresh data for both company file connections (i.e., Query from CompanyA & Query from CompanyB ).

Now we will add a button & we will write code for the button click event. On button click, it will open the connection for CompanyA, refresh the report data & close the connection for CompanyA, sleep for 15 seconds, open the connection for CompanyB, refresh the report data & close the connection for CompanyB.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open a connection for Company A, refresh the report data & close the connection for Company A, sleep for 15 seconds, open the connection for CompanyB, restore the report data & close the connection for CompanyB.

Fifteen seconds of sleep are required to close the company file and switch to another private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from CompanyA"). Refresh
Application.Wait (Now + TimeValue("0:00:15"))
ActiveWorkbook.Connections("Query from CompanyB").Refresh
End Sub

Report Data will be refreshed by clicking the Refresh Data button.

Keywords: QuickBooks Desktop, win log with multiple companies, log in with various companies

Download Sample

ADODB with QODBC

Open VB6, you can find it via Windows Start->All Programs->Microsoft Visual Studio 6.0 :

Create a New Standard EXE project.

Then, add a "Command Button" by double-clicking on the command button Icon. A command button will be added. Rename it to Get Customer via ADODB:

Double click on the Command Button & add the code below in the Command Button click event:

Private Sub Command1_Click()

Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim sMsg As String
Dim sConnectString As String
Dim sSQL As String

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "SELECT top 10 Name FROM Customer"
Set oConnection = New ADODB.Connection
Set oRecordset = New ADODB.Recordset
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(10)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(10)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************" & Chr(10)
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

End Sub

Now execute the code from Run >> Start With Full Compile

Double click on Get Customer via ADODB, which will get the top 10 customers' names & display them in msg box:

ADODC with QODBC

Now we will add an ADODC control in the same project we created in the above ADODB example.

Now we will configure ADODC control by right-clicking on the ADODC control & clicking on ADODC Properties:

From the General tab, select ODBC Data Source, QuickBooks Data.

Navigate to the RecordSource tab, select Command type to 2 - adCmdTable & table name Customer, and click OK.

Now we will add three text boxes showing the Customer table data through the ADODC control.

Now we will configure text boxes to display data from the ADODC control. Choose DataSource Adodc1 (i.e., ADODC Control name) & DataField FullName (i.e., Field name which you want to show):

Similarly, we will configure the other two textboxes with the Phone & Email fields.

Now execute the code from Run >> Start With Full Compile.

The following customer data will be displayed by clicking on the "Next" button.

Either QODBC or QRemote is required on the machine where the SSIS package will be developed and deployed, or SSIS services are installed.

Development - On the machine where you are developing, the SSIS package should have QODBC installed.

Deployment - On the machine you wish to deploy or run the SSIS Package - You should have QODBC installed. 

The first step is to install and test QODBC.

Please refer to Connecting QODBC Driver to QuickBooks for the First Time (Video)

If the QuickBooks application is installed on another machine, you can connect to QuickBooks from a remote device using QRemote Server. You need to install QODBC on both machines.

Please refer to Accessing QuickBooks Data Remotely using QODBC & QRemote. (Video)

When deploying the SSIS package to SQL Server, you need to configure the QRemote client to connect with QuickBooks.

Please follow the steps below to use QODBC with SSIS.

1. Go to the Start menu & open SQL Server Business Intelligence Development Studio from SQL Server.

2. Create a new Integration Service Project by clicking on Integration Service Project & Enter Project Name, setting the project location, and clicking OK.

The Project is created.

3. In this sample project, we will transfer QuickBooks data using the ADO.NET source to the Flat File Destination. Wes, we need to add a Data Flow Task in the Control Flow to the perform tab. You can add it by double-clicking on the Data Flow Task.

4. We need to add an ADO.NET Source in the Data Flow tab. You can add it by double-clicking on ADO.NET Source.

5. Now, we need to configure the ADO.NET source. By right-clicking on ADO.NET Source & click on Edit.

Add a new ADO.NET connection by clicking on New.

Add a new Data connection by clicking on New.

Select Provider to .Net Providers\Odbc Data Provider, select DSN to QuickBooks Data QRemote, and click on Test Connection.

Test Connection Succeeded.

You can select Data Access Mode as Table or view the whole table data. You can also select Data Access Mode as an SQL command for accessing table data with a condition & accessing report data using sp_report. In this example, I have selected Table or view as Data Access Mode & selected the Customer table. Click on OK & you will notice the connection has been added.

You can preview output data by clicking the Preview button.

Close the Preview by clicking the close button & click on OK to save configurations.

6. We need to add a Flat File Destination in the Data Flow tab. You can add it by double-clicking on Flat File Destination.

7. Now, we need to link the ADO.NET Source with the Flat File Destination by dragging the link from the ADO.NET Source to the Flat File Destination.

8. Now, we need to configure the Flat File Destination. By right-clicking on Flat File Destination & click on Edit.

Add a new Flat File connection by clicking on New.

Select Flat File Format & click OK.

Select the Destination file you want to store output using the Browse button.

Enable Unicode & Column names in the first data row option, & Click on OK to save configurations.

You will notice the OK button is disabled. To enable it, you need to click on Mapping, then click OK to complete the configuration.

9. Run the SSIS package to perform the transfer operation by clicking the play icon.

SSIS Package execution completed & 163 rows are transferred from ADO.NET Source to Flat File Destination.

Here is a Flat text file that contains the ADO.NET source result.

Please click here to download the Sample SSIS Package.

Problem Description:

I am evaluating your driver and have had success running SELECT queries against QuickBooks in SSMS, where I could use the QRemote DSN. When setting up the ODBC connection in SSIS, I get an error when enumerating the columns.

Unknown Data type found in column

Solution:

To resolve this error, you need to select “ADO.NET Source” instead of ODBC Source in the SSIS package.

#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 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

Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Open the QODBC setup screen and change the QODBC Compatibility mode from "Default" to "3.8."

To change, please follow the steps below:

Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go To "System DSN" Tab>> click the DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

Note: This should fix the "Driver does not support this parameter" error message.

Similarly, change the DSN configuration for QRemote 32-Bit and QRemote 64-Bit.

QRemote 32-Bit
Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data
QRemote" DSN > click "Configure">> Switch to the Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

QRemote 64-Bit
Start >> All Programs >> QODBC Driver for use with QuickBooks (64-Bit)>> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data 64-bit QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

Start QuickBooks and log in to the QuickBooks company file as the QuickBooks user Admin.

Before using the MS Excel Power Query application, please make sure you have installed the MS Excel Add-in named "Power Query."

Please select Power Query Window.

Please select the data input method via From Other Sources & click on From ODBC.

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.

In the connection string box, write "DSN = QuickBooks Data" & in the SQL Statement box, write your SQL statement & click on the "OK" button. If you have 64-bit MS Excel, then you need to write DSN = QuickBooks Data 64-bit QRemote in the connection string box.

Now select the "Windows" & "Use my current credentials" option and click on the "OK" button.:

Now you will get data in Power Query, apply the filter & other settings as per your requirement & click on the "Close & Load" button.

If you are facing the error "ODBC: ERROR [HYC00] [Microsoft][ODBC Driver Manager] Driver does not support this parameter", please switch QODBC, QRemote 32-Bit DSN, and QRemote 64-Bit DSN to ODBC Compatibility 3.8

You can also use "From Microsoft Query" for fetching data from QuickBooks to Excel.

For Excel 64-Bit:

http://qodbc.com/links/2771

For Access 64-Bit:
http://qodbc.com/links/2772

Excel 32-Bit:
http://qodbc.com/links/2466

Access 32-Bit:
http://qodbc.com/links/2470

Video tutorial:
http://qodbc.com/links/2509

Also, Refer:
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2010 (Import Tables) 32-bit
Using QuickBooks Data with Access 2010(Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access
Microsoft Access 2003 and QODBC

Tags: The driver does not support this parameter.

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:

So the QODBC works excellently running on the machine. We intend to integrate into our website / Intranet to create customers or make changes from the web. I noticed QuickBooks needs to be logged in and open to function. Is there a server version that will not require a user to log in or pass the info along automatically, so the website continues to function correctly?

Is there a way to try the server version by syncing to the SQL server? I need to be able to dump the customer table once daily into SQL. Maybe other tables in the future, like orders or quotes. Does the server version need a remote desktop session open to work? If the server reboots because of an update, I need to log in to make it work again. That doesn’t seem very feasible for us. 

Solution:

QODBC is not a driver you would install on a server; multiple workstations would have access to it. The server version of QODBC is for synchronizing SQL Server with QuickBooks data or for a web or intranet application.

>>Is there a server version that will not need a user logged in or pass the info along automatically, so the website continues to function correctly?
>>Does the server version need to have a remote desktop session open to work?

Please refer to how to Setup QODBC to Access QuickBooks when no one Logged in.

OR

You may configure a session-0 to start and start QuickBooks and QRemoteServer automatically.
When you configure session-0, you are setting up the auto login of a user, and that user will have a desktop session running.

>>Is there a way to try the server version by syncing to the SQL server?

You can use La inked Server on SQL Server and connect to QuickBooks Data using QRemote. When you query the Linked Server/tables, you will always get live QuickBooks Data.

For a dump of all the tables or selected tables from QODBC to Tables in SQL Server Database, you may write a script to create the table(s) in SQL Server and get data from the linked server.

e.g.
SELECT * into LocalSQLTableName FROM OPENQUERY(YourLinkedServerName, 'SELECT * FROM LinkedTableName')

SELECT * into tblVendor FROM OPENQUERY(QRemote, 'SELECT * FROM Vendor')

You can add the script to the agent to run periodically.

Please refer to the below-mentioned article for How to create a Link Server with MS SQL 2005/2008/2012 Using QODBC/QRemote.

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

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


Also, Refer:
How to set up QODBC Driver to auto-connect to QuickBooks. (Video)
How to setup QODBC for Auto-login into QuickBooks

Problem Description:

I recently upgraded from 1.0.0.7 to 1.0.0.312. Everything was pretty smooth, except for our Linked Servers timeout now. I test the QODBC connection, and it is okay. However, when I try to add the Linked Server for that successful ODBC connection, it just times out after a long period of time, approximately 15 minutes.

Solution:

Please follow the steps below to resolve the issue.

1. Verify the linked server configuration.

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

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

2. Verify QRemote Version.

If your SQL Server and QuickBooks are on different machines & you are connecting remotely, then please make sure the Version on QRemote on the Client machine is the same as QRemote on the Server Machine.

For Example:

If the client machine QRemote version is 1.0.0.312, then the server machine QRemote version should also be 1.0.0.312.
If the QRemote version on both machines is different, please make both versions the same by installing the same QODBC version.

You can get QODBC's latest version at the link below:

QODBC Driver for QuickBooks Download

3. Verify QRemote server configuration.

Please verify QRemote Server & QRemote Client DSNs are configured correctly by referring to thelink belowk:
QRemote Configuration

4. Restart SQL Server Services.

If you have followed steps #1,2,3 & still getting a timeout, then please restart SQL Server Services & test again. Open SQL Server Management Studio, Right-click on the Server name & click on Restart.

Please click on yes to run the service.

Please click on yes to restart the service.

SQL Server Service is restarting.

After restarting the SQL Server Service, please test again.

Troubleshooting - Microsoft  Access 2013 (Office 365 / 2016) crashes / Required Activation

Problem Description 1:

I have been having significant difficulties with QODBC since upgrading to QuickBooks Enterprise 16.0. The Test Connection in the QODBC Setup Screen says it is connected, and the "QODBC Test Tool" returns results. Still, Excel crashes as soon as you select the QuickBooks Data* Database option in the connection wizard.

Problem Description 2:

I downloaded the QODBC drivers today to start work on a development project, but I am facing an issue. Suppose I click the Test Connection button on the General tab of the Setup dialog. The test is successful. But when I try to use MS Access 2013 or MS Excel 2013 and connect to a QuickBooks data set using the ODBC driver, MS Access and MS Excel both crash.

Problem Description 3:

We have two licenses for QODBC Online. One is used at the consultant's office, and the client Admin uses one—both obtained authorization for access to QuickBooks using the same QuickBooks Online Login.

The problem appeared this morning after the consultant (me) loaded a new copy of the Access 2016 code the client uses. Because the DNS entries on the two different systems have different names, I attempted to use the Access Linked Data Manager to relink the databases to access. When I did this, I got an Authorization error, which I interpreted to mean that I needed to reconnect the databases in the DNS object to QuickBooks Online. I reverted to using the "QODBC Online Test Tool," and it also got the authorization error. I then used the DNS Configure function for the 32-bit DNS entry for QODBC and disconnected from QuickBooks Online. I then connected to QuickBooks Online, and everything functioned as expected. Now, when I attempt to use "QODBC Online Test Tool" to access the QuickBooks Online Data, I get the message that prompts "QODBC Driver QuickBooks Online not licensed for this QuickBooks Edition."

Problem Description 4:

Access 2016 crashes with no error message when I try to open a QuickBooks table.

The problem occurs with just a simple table or a query: no error messages, no clues. Access shuts down. I cleared messages and then retired.

2018-10-17 17:50:54 QODBC Ver: 16.00.00.313 *********************************************************************************************************************
service: Unknown
Startup Failure Calling Update: 46 - REGISTRY SET ERROR

Solution:

Due to some security added to Office 2013 & later versions, this issue is happening. This issue only occurs when using MS Office 2013 / 2016 / 365 and not with MS Office 2010 or earlier.
MS Office is not allowing writing to the registry; thus, QODBC has this error.

Please run the patch or follow the manual steps to fix this issue.

Patch:

Please close QuickBooks, Access, Excel, and any other application using QODBC.

Download the fix patch by clicking here

Extract the file, right-click on the zip file & click on Extract ALL.

Select the destination & extract the file by clicking on Extract.

/p>

You will find two exe from the extracted folder.

First, Right-click on "x64-Office2013Fix-20151221.exe" & click "Run as administrator."

Please click on yes to run the exe.

After the process is completed, press any key to close the application.

Now, First Right-click on "x86-Office2013Fix-20151221.exe" & click "Run as administrator."

Please click on yes to run the exe.

After the process is completed, press any key to close the application.

Run your application and see if that has fixed the issue.

If the above does not resolve the issue, then please follow the alternate solution(Manual Steps):

Manual Steps:

QODBC shows RegFile1 License error when using MS Excel 2013 or Access 2013, Excel Crash, Access Crash (Video)
MS Excel 2013 or Access 2013 (Office 365) crashes as soon as trying to connect to QuickBooks Data

If you still face issues, try using "QuickBooks Data QRemote."

For system requirement details of QODBC Online, click here.

How to install QODBC Online & Use Microsoft Excel/Access with QODBC

You can also see: How to use QODBC Online with QuickBooks Online Edition

For system requirement details of QODBC Online, click here.

Tags:

Installing QODBC Online

How to connect to QODBC Online

Tags: QuickBooks Online, QBO, MS Access, Excel, QODBC Online, connect online

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 instructions below 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

Step 1 - Download QXL

If you haven't already downloaded QXL, go to the download page now. Don't forget to read the "Readme" file during installation for the latest information on QXL setup, new features, and other driver information. Save the self-extracting executable "QXLSetup.exe" in a temporary directory or your "My Documents" folder to extract the installation files.

Step 2 - Running the Installer:

Click on the Install program from within Windows to install the driver. Select the destination directory to install the QXL support files. Click "OK" to continue.

The QXL - QuickBooks Export Made Easy® has been successfully installed on your computer. You can begin to use it because it will immediately run as a 30-day evaluation version.

Step 3 - Use QXL:

After installation, you may notice the following icons on the desktop.

QXL – Silent Mode - It uses the last saved settings of QXL and automatically starts executing the data export with no user interface.QXL – Direct Mode - It uses the previously saved settings of QXL and automatically starts executing the data export with the user interface, i.e., displaying progress data export.QXL – Manual execution of Data Export/Update Settings/Activation-Deactivation. You can select tables and destinations to which the files should be exported. Start QuickBooks, log in to your company file, switch back to QXL, and click Export Data.

After the export is completed. The exported data folder will open.

Exported data is placed at the below location by default: C:\Users\User Name\Documents\QXL\

QXL Features:

1. Export QuickBooks Data to XML format via QODBC.

2. Export QuickBooks Data to CSV format via QODBC.

3. Ability to choose export file(s) location.

4. Ability to export to Separate files or a single file.

5. Ability to export all selected, tabled, or excluded tables.

6. Ability to save your default settings from Options.

Also, Refer:
QXL options and settings

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 specific 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 have 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 constructed an Access database file (32bit Access 2003) with tables linked to QuickBooks. I can pull information from the related tables to other tables in Access using update queries with no problems. When I try to run an update query to change a value in the QuickBooks linked table, I receive the following error. I need help figuring out the error and how to fix it.

My query is:

UPDATE PriceLevelPerItem INNER JOIN ItemPricing ON PriceLevelPerItem.FQPrimaryKey <> ItemPricing.FQPrimaryKey SET PriceLevelPerItem.PriceLevelPerItemCustomPrice = [ItemPricing].[PriceLevelPerItemCustomPrice];

Solutions:

You need to include PriceLevelPerItemItemRefListID in your update statement.

You need to include the below-the-line in your update statement to resolve this issue.

SET PriceLevelPerItem.PriceLevelPerItemItemRefListID = PriceLevelPerItem.PriceLevelPerItemItemRefListID For Example:

UPDATE PriceLevelPerItem INNER JOIN ItemPricing ON PriceLevelPerItem.FQPrimaryKey <> ItemPricing.FQPrimaryKey SET PriceLevelPerItem.PriceLevelPerItemItemRefListID = PriceLevelPerItem.PriceLevelPerItemItemRefListID, PriceLevelPerItem.PriceLevelPerItemCustomPrice = [ItemPricing].[PriceLevelPerItemCustomPrice];

Problem Description:

I am getting the following 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 Too and MS Access. But I am facing an issue in MS Excel.  

Solutions:

MS Excel has an 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 to the link below to resolve this issue:

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

You can either apply a 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 am trying to run a query using an SQL Server database link to QuickBooks using QRemote. I can set up the linked server fine in SQL Server, and the connection has been tested to work. However, I try to run the query:

SELECT * FROM QRemote...InvoiceLine

The response is:

The OLE DB provider "MSDASQL" for linked server "QRemote" contains multiple tables that match the name "InvoiceLine."

I tried selecting through the QODBC Test Tool, which does not use the linked database. Please help as to where the issue might be.

Solutions:

You have not configured the MSDASQL property for the linked server.

The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. 

In Object Explorer, right-click the provider name and select Properties for MSDASQL. The first six properties should be enabled. Please enable the first six properties. 

For Multiple tables existing error, the "Level zero only" property should be set.   

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 that 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 to: How to Use QODBC with Microsoft Access 2010

Problem Description:

I am trying to use MS Access as a middleware with MS SQL Server. I have created an MS Access database & linked all QODBC tables through QRemote DSN. After that, I created MS Access DSN, which points to the newly created database. After that, I created the linked server pointing to MS Access DSN. The linked server is created, but when I run the query on the linked server, I get the below error: 

Please help me to resolve this error.

Solutions:

This error is not related to QODBC. You are facing this issue because MS SQL Server is logged on as a "Network Service."

You need to change this setting to "Local System."

You can change it by below steps:

Start >> Run >> type "services. msc" and click on "OK."

Find SQL Server (MSSQLSERVER), Open SQL Server (MSSQLSERVER) by double clicking on it & navigate log On tab & choose log-on as "Local System Account," and apply the changes by clicking on the ok button & test again.

We modified an invoice in QuickBooks and are trying to get the data out in Excel, but QODBC gives us the old data.

Why?

Solutions:

QODBC failure to return full records is usually caused by corruption of the QODBC optimizer file. I want to inform you that the Optimizer file may be corrupted if you are killing the process or query while the optimizer file is building. It is not advisable to kill a query while it is in execution.

Please reset the optimizer to solve the problem.

1) Close all other applications that use QODBC.

2) Turn off the QODBC optimizer (QODBC Setup Screen->" Optimizer" tab->Uncheck "Use optimizer" option)

3) Find the file with the .opt extension at the path you had assigned to the QODBC optimizer and delete the optimizer file.

(The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer)

You can find the optimizer folder path from QODBC Setup Screen->" Optimizer" tab. Please copy this path & navigate to this path & delete the .opt file.

Or

You can directly delete the .opt file from:

Start>>All Programs>> QODBC Driver for use with QuickBooks>>QODBC Setup Screen>>" Optimizer" tab>>Click "Reset Optimizer File(s)."

4) Turn on the QODBC optimizer (QODBC Setup Screen->" Optimizer" tab->Check "Use optimizer" option)

5) After turning on the optimizer, please run the below command in QODBC Test Tool:

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 run the above command completely. The above command may take some time, depending on your QuickBooks records. It may take 2-24 hours or more, depending on the number of records 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 it's building the Optimizer file.

6) Please test again to see if the problem is still there.

7) After completing the above steps, please test data from QODBC Test Tool. If you can get updated data, then please refresh the Excel sheet so you can get updated data.

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 following query:
Insert into customer (name) values ('Larry's Custom Invoice ')
The above question will create a customer entry named Larry's Custom Invoice.        

Problem Description:

How can I use the same Excel files in 64 & 32-bit Systems?

Is it possible to have a connection set up in an Excel file and be able to refresh on either a64-bit4 or 32-bit system?

I know it's possible on the same (64 or 32) as long as the DSNs have the same names, but I wasn't sure about "crossing" between systems.

Solutions:

Yes, you can create a QRemote DSN with the same name on all the machines on which you would like to use Excel.

Your Excel file needs to use a common name DSN, for example, "QODBC-Excel."

Please refer to the example below:

1) Machine A of a 64-bit OS has 64-bit Excel - create a DSN "QODBC-Excel" under 64-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

2) Machine B of a 64-bit OS has 32-bit Excel - create a DSN "QODBC-Excel" under 32-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

3) Machine C of 32-bit OS has 32-bit Excel - create a DSN "QODBC-Excel" under 32-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

Now you can use the same Excel file on the above machine, whether machine 32 or 64-bit machine. Also, you can use the same Excel file on different architectures (i.e., you can use a 32-bit Excel file on 64-bit Excel and vice-versa)

To apply the newly created DSN to old Excel files.

Open the Excel file. Please go to the DataTab & click on "Connections."

You can see existing connection details. Please click on "Properties."

Please select the connection name & remove it.

Now manually write the newly created DSN name for this example, "QODBC-Excel," & click "OK."

Now you can see updated connection details. Please click on "close."

By clicking on the "Refresh All" button, you will get updated data from the newly created DSN.

Problem:

I have created the link to the Customer table in MS Access using QODBC. I can link the table without any error, but when I try to edit any field from the linked table, I get the following error:

Microsoft Visual C++ Runtime Library
---------------------------
Runtime Error!

Program: C:\Program Files\Q...

R6025
- pure virtual function call

Sometimes I get a Runtime error & sometimes the MS Access application crashes. Then MS Access closes and tries to restart.

Solution:

This issue only happens when using MS Access Linked Table & tries to edit the first row.

Please try to update other rows than the first row. It will not cause any error/crash. After updating other rows, it does not produce any error if you attempt to update the first row.

Also, please follow the steps below:

1) Close all the applications.
2) Delete the Optimizer file located at %AppData%\QODBC Driver for QuickBooks\Optimizer
3) Start your MS Access application, remove all the linked tables, and re-create it.
4) Test again.

Keywords: Runtime Error, MS Access Crash

I am getting the error ODBC call failed- Error 10004 when trying to connect to QuickBooks through MS Access using QODBC.

Solution:

Please keep QuickBooks running, log in as Admin to your company file, Switch to single-user mode, and try to connect again with your application.

Once you are connected, close your application. On QuickBooks, switch to multi-user mode and try to connect again with your application.

I am trying to connect to QuickBooks through SSIS using QODBC, but I cannot connect to QuickBooks.

I have a read-only desktop edition of QODBC, which is included with QuickBooks Enterprise Edition.

I am getting the error: "Error = 80040408, Could not start QuickBooks."

Solution:

QODBC included with QuickBooks Enterprise Edition is a read-only edition, which can only work with QuickBooks Enterprise when using the Desktop Base application.

But to work with SSIS, you need to either use QRemote or enable "DCOM Server" in QODBC.

When you execute/set the SSIS package to run the scheduled job, it runs as a Service (SQL Server is a service-based application).

QODBC Server Edition License is required to work with a service-based application.

Please note:  QODBC Read-Only Edition for QuickBooks Enterprise does not include a QRemote License.

"Could not start QuickBooks": Keep QuickBooks running  ( No need to log in to the company file; keep the QuickBooks window open ) and try connecting.

An excellent example of this is the InvoiceLine table. If you run sp_columns InvoiceLine using QODBC Test Tool, you will see that the Memo and InvoiceLineGroupDesc columns can be up to 4095 characters long, so they can't be imported into Excel.

This means that a:  "select * from InvoiceLine."

It will not import. You must instead select all the columns of interest that you wish to use by doing something like the following:-

SELECT InvoiceLine.TxnID, InvoiceLine.TimeCreated, InvoiceLine.TimeModified, InvoiceLine.EditSequence, InvoiceLine.TxnNumber, InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName, InvoiceLine.ClassRefListID, InvoiceLine.ClassRefFullName, InvoiceLine.ARAccountRefListID, InvoiceLine.ARAccountRefFullName, InvoiceLine.TemplateRefListID, InvoiceLine.TemplateRefFullName, InvoiceLine.TxnDate, InvoiceLine.TxnDateMacro, InvoiceLine.RefNumber, InvoiceLine.BillAddressAddr1, InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3, InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressCity, InvoiceLine.BillAddressState, InvoiceLine.BillAddressPostalCode, InvoiceLine.BillAddressCountry, InvoiceLine.ShipAddressAddr1, InvoiceLine.ShipAddressAddr2, InvoiceLine.ShipAddressAddr3, InvoiceLine.ShipAddressAddr4, InvoiceLine.ShipAddressCity, InvoiceLine.ShipAddressState, InvoiceLine.ShipAddressPostalCode, InvoiceLine.ShipAddressCountry, InvoiceLine.IsPending, InvoiceLine.IsFinanceCharge, InvoiceLine.PONumber, InvoiceLine.TermsRefListID, InvoiceLine.TermsRefFullName, InvoiceLine.DueDate, InvoiceLine.SalesRepRefListID, InvoiceLine.SalesRepRefFullName, InvoiceLine.FOB, InvoiceLine.ShipDate, InvoiceLine.ShipMethodRefListID, InvoiceLine.ShipMethodRefFullName, InvoiceLine.Subtotal, InvoiceLine.ItemSalesTaxRefListID, InvoiceLine.ItemSalesTaxRefFullName, InvoiceLine.SalesTaxPercentage, InvoiceLine.SalesTaxTotal, InvoiceLine.AppliedAmount, InvoiceLine.BalanceRemaining, InvoiceLine.IsPaid, InvoiceLine.CustomerMsgRefListID, InvoiceLine.CustomerMsgRefFullName, InvoiceLine.IsToBePrinted, InvoiceLine.CustomerSalesTaxCodeRefListID, InvoiceLine.CustomerSalesTaxCodeRefFullName, InvoiceLine.SuggestedDiscountAmount, InvoiceLine.SuggestedDiscountDate, InvoiceLine.InvoiceLineType, InvoiceLine.InvoiceLineSeqNo, InvoiceLine.InvoiceLineGroupTxnLineID, InvoiceLine.InvoiceLineGroupItemGroupRefListID, InvoiceLine.InvoiceLineGroupItemGroupRefFullName, InvoiceLine.InvoiceLineGroupQuantity, InvoiceLine.InvoiceLineGroupIsPrintItemsInGroup, InvoiceLine.InvoiceLineGroupTotalAmount, InvoiceLine.InvoiceLineGroupSeqNo, InvoiceLine.InvoiceLineTxnLineID, InvoiceLine.InvoiceLineItemRefListID, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineRatePercent, InvoiceLine.InvoiceLinePriceLevelRefListID, InvoiceLine.InvoiceLinePriceLevelRefFullName, InvoiceLine.InvoiceLineClassRefListID, InvoiceLine.InvoiceLineClassRefFullName, InvoiceLine.InvoiceLineAmount, InvoiceLine.InvoiceLineServiceDate, InvoiceLine.InvoiceLineSalesTaxCodeRefListID, InvoiceLine.InvoiceLineSalesTaxCodeRefFullName, InvoiceLine.InvoiceLineOverrideItemAccountRefListID, InvoiceLine.InvoiceLineOverrideItemAccountRefFullName, InvoiceLine.FQSaveToCache, InvoiceLine.FQPrimaryKey, InvoiceLine.CustomFieldInvoiceLineOther1, InvoiceLine.CustomFieldInvoiceLineOther2, InvoiceLine.CustomFieldInvoiceLineColor, InvoiceLine.CustomFieldInvoiceLineMaterial, InvoiceLine.CustomFieldInvoiceLineGroupOther1, InvoiceLine.CustomFieldInvoiceLineGroupOther2, InvoiceLine.CustomFieldInvoiceLineGroupColor, InvoiceLine.CustomFieldInvoiceLineGroupMaterial, InvoiceLine.CustomFieldInvoiceLineGroupLineOther1, InvoiceLine.CustomFieldInvoiceLineGroupLineOther2, InvoiceLine.CustomFieldInvoiceLineGroupLineColor, InvoiceLine.CustomFieldInvoiceLineGroupLineMaterial, InvoiceLine.CustomFieldOther, InvoiceLine.CustomFieldBDay, InvoiceLine.CustomFieldContract, InvoiceLine.CustomFieldSpousesName
FROM InvoiceLine InvoiceLine
WHERE (InvoiceLine.TxnDate>{d '2024-01-01'})

Note: CustomField names will vary depending on the names you give them in QuickBooks.


You can also control the length of the column returned by QODBC using the LEFT function, even in stored procedure reports.

For example:

An examination of the report InventoryValuationSummary shows that the RowData and RowDataType columns can return values as long as 4096 characters:

Using the LEFT function, I can extract the first 30 characters of the RowData and RowDataType columns only:

sp_report InventoryValuationSummary show ItemDesc, QuantityOnHand, RowType,
{fn LEFT("RowData", 30)} AS "Row Data", {fn LEFT("RowDataType", 30)} AS "Row Data Type"
parameters DateFrom = {d '2024-11-19'}, DateTo = {d '2024-11-19'},
ItemFilterType = 'InventoryAndAssembly'

Keywords: assignment error

In QuickBooks

      New Custom Fields are created within QuickBooks.

In MS Access

      Newly created Custom Fields are not visible.

       The solution to this issue is to re-create the linked table.

      Open MS Access Linked Table:

     Right-click on the On Your Linked Table(In This Example, We are Using the Customer Table):

     Now, click on the Delete.

Now link the Same Table (In this example, it's the Customer Table). After linking the table, you will get the updated Custom Field.

     Please refer to the link below for How to Create a Linked Table in MS Access:

How to Use QODBC with Microsoft Access 2007

How to Use QODBC with Microsoft Access 2010

Problem Description 1:

I am trying to use QODBC to read a vendor custom field named "Territory." From the QODBC Test Tool, running "sp_columns vendor" produces a list of column data, but my custom fields don't appear.

I have already reset the server and the connection between QODBC & QuickBooks. But still, I am unable to get the Custom Field. 

Problem Description 2:

We are looking for the data in QuickBooks Enterprise Edition. v16 via QODBC (Linked Server) SQL Server with no luck:
We can not find data in the CustomField table, although we have many created. 

Solution:

You can find CustomField in the table that you have assigned it. to

For example, if you have assigned a CustomField in the Customer, Vendor, or Employee table, you can find it from Customer, Vendor, or Employee. If you have assigned a CustomField in transaction entities like (Invoice, Bill), then you can find it from Invoice, InvoiceLine, Bill, BillItemLine, and BillExpenseLine tables.

Please Note: You need to disconnect the connection between QODBC & QuickBooks and Reconnect to get the Custom Field. Custom Field will display in QODBC after resetting the connection.

Usually, you can get Custom Fields using QODBC by resetting the connection between QODBC & QuickBooks. The Custom Field will display in QODBC after disconnecting all the links and connecting again.

If you are still not getting the Custom Field even after all the connections between QODBC & QuickBooks are disconnected, restart the Machine. So you need to reset Custom Fields from the QuickBooks UI.

You need to remove each custom field's name, uncheck where used, and then reapply the information and test again.