Also refer to: QODBC Licensing Information

The types of licenses needed vary based on your requirements and your environment setup.

Please refer to the detailed information on QODBC Licensing below.

1) Power BI Gateway application accessing QuickBooks remotely:

Machine A - QuickBooks, QODBC, QRemote Server and QuickBooks company file
Machine B - QRemote Client, Power Bi Gateway

License
Machine A – QODBC Server Edition License
Machine B – QRemote License

2) Power BI Gateway application accessing QuickBooks remotely – QuickBooks company file on a different machine. Power Bi Gateway application accessing QuickBooks remotely – QuickBooks company file on different machine.:

Note: Accessing a QuickBooks company file from a different computer or shared drive can impact the performance of both QuickBooks and QODBC. Thus, we do not recommend such an environment setup. To achieve the best performance, it is advisable to run QODBC, the QuickBooks application, and the QuickBooks company file on the same computer.

Machine A - QuickBooks, QODBC, QRemote Server
Machine B - QRemote Client, Power BI Gateway
Machine C – QuickBooks company file

License
Machine A – QODBC Server Edition License
Machine B – QRemote License
Machine C – No QODBC / QRemote License

3) Multiple Power BI Gateway applications accessing QuickBooks remotely:

Machine A - QuickBooks, QODBC, QRemote Server, and QuickBooks company file
Machine B - QRemote Client, Power BI Gateway
Machine C - QRemote Client, Power BI Gateway

License
Machine A – QODBC Server Edition License
Machine B – QRemote License
Machine C – QRemote License

4) Multiple Power BI Gateway applications accessing QuickBooks remotely – QuickBooks company file on a different machine.:

Note: Accessing a QuickBooks company file from a different computer or shared drive can impact the performance of both QuickBooks and QODBC. Thus, we do not recommend such an environment setup. To achieve the best performance, it is advisable to run QODBC, the QuickBooks application, and the QuickBooks company file on the same computer.

Machine A - QuickBooks, QODBC, QRemote Server
Machine B - QRemote Client, Power Bi Gateway
Machine C – QuickBooks company file
Machine D - QRemote Client, Power Bi Gateway

License
Machine A – QODBC Server Edition License
Machine B – QRemote License
Machine C – No QODBC / QRemote License
Machine D – QRemote License

5) Multiple Power BI Gateway applications accessing QuickBooks remotely.:

Machine A - QuickBooks, QODBC, QRemote Server, Power BI Gateway, and QuickBooks company file
Machine B - QRemote Client, Power BI Gateway
Machine C - QuickBooks, QODBC, QRemote Server, Power BI Gateway

License
Machine A – QODBC Server Edition License
Machine B – QRemote License
Machine C – QODBC Server Edition License

6) Multiple Power BI Gateway applications accessing QuickBooks remotely – QuickBooks company file on a different machine.

Note: Accessing a QuickBooks company file from a different computer or shared drive can impact the performance of both QuickBooks and QODBC. Thus, we do not recommend such an environment setup. To achieve the best performance, it is advisable to run QODBC, the QuickBooks application, and the QuickBooks company file on the same computer.

Machine A - QuickBooks, QODBC, QRemote Server, Power BI Gateway
Machine B - QRemote Client, Power BI Gateway
Machine C – QuickBooks company file
Machine D - QuickBooks, QODBC, QRemote Server, Power BI Gateway

License
Machine A – QODBC Server Edition License
Machine B – QRemote License
Machine C – No QODBC / QRemote License
Machine D – QODBC Server Edition License

Keywords: Power BI, Remote Access, QRemote Configuration, QRemote License information

Problem Description:

I am using Microsoft Excel 365 and trying to import data using the Microsoft Query option. However, I cannot find Microsoft Query (Legacy) under the Get Data tab.
Why is Microsoft Query (Legacy) missing in Excel 365, and how can I enable it?

Solution:

In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab. This tool is commonly used to import data from external sources via ODBC connections. If you're using QODBC or any ODBC-based system like QuickBooks, enabling this option is necessary.

Note: We recommend using the 64-bit version of Microsoft Excel for better performance and compatibility when working with large datasets.

Prerequisites:

Microsoft Excel 2016 or later (including Excel 365)
Administrative access to change Excel options.

As shown in above image, the Microsoft Query option does not appear under the Get Data tab by default.

Step-by-Step Instructions to enable Microsoft Query:

Step 1: Enable Legacy Data Import Wizards.

• Open Microsoft Excel.

• Click on the File tab.

• Click on Options at the bottom-left corner.

• In the Excel Options window, select the Data category from the left-hand panel.

• Scroll down to the Legacy Data Import Wizards section.

• Check the box labeled "From Microsoft Query (Legacy)".

•  Click OK to apply changes.

•  Close and restart Excel for the changes to take effect.

Step 2: Access Microsoft Query (Legacy)

After restarting Excel:

• Go to the Data tab.

• Click Get Data > Legacy Wizards > From Microsoft Query (Legacy).

By following the steps above, you can restore and use the Microsoft Query (Legacy) wizard in Excel 365, allowing seamless data import via QODBC from external applications such as QuickBooks.

Tags: "Microsoft Query" is not a data option. Microsoft Query is missing, how to enable Microsoft Query, show Microsoft Query

Problem Description:

I want to cancel my QODBC Driver for QuickBooks Desktop monthly subscription.

How do I cancel the subscription to QODBC Driver for QuickBooks Desktop?

Solution:

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

Connect to QuickBooks Data using the QODBC 32-bit Test Tool.

Execute the following SQL statement.

sp_optimizefullsync TableName

example

sp_optimizefullsync Customer
or

sp_optimizefullsync Vendor

or

sp_optimizefullsync Invoice

Tags: QuickBooks Desktop, QBD,Desktop Subscription QODBC Desktop, Cancel subscription

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

Connect to QuickBooks Data using the QODBC 32-bit Test Tool.

Execute the following SQL statement.

sp_optimizefullsync TableName

example

sp_optimizefullsync Customer

or

sp_optimizefullsync Vendor

or

sp_optimizefullsync Invoice

Problem Description

Issue 1:

I just updated QODBC to the latest build, 349. When trying to connect to the QRemote DSN, I get the following error message.

Windows cannot find 'fqqbvsav.exe'. Make sure you typed the name correctly, and then try again.

Issue 2:

I moved everything over to a new computer and upgraded to the latest QOCBC. I can't get the connection. The latest error is windows cannot find 'fqqbvsav.exe' Make sure you typed the name correctly, and then try again.

Reason

 The issue is caused when the QRemote Client driver cannot write to the folder. 

"C:\Program Files (x86)\QODBC Driver for QuickBooks".

The issue is reported as bug/task ID: B01491. We are working on fixing the issue.

Solutions

Solution 1:

Open the QRemote Client DSN using the ODBC Data Source. Opening the DSN configuration will create/update necessary files in the QODBC folder.

This should fix the issue.

Please make sure you have logged into Windows as "Administrator".

QODBC Desktop

QODBC Online

QODBC POS

Please test your application now.

 Solution 2:

 If the above solution does not work, it means you do not have permission to write to the QODBC folder in the program files.

 Please contact your system administrator to either provide you with Read and Write access to the QODBC folder in the program files.

QuickBooks is a 32-Bit application, but other applications such as MS Excel, MS Access, and MS SQL Server are also available in 64-Bit architecture.

Since a 64-Bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect, and we call it QRemote.  QRemote works as a bridge between 64-Bit Applications and the 32-Bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-Bit Web Servers and 64-Bit applications to talk to QuickBooks live for the first time via the QODBC Driver.

Office 64-Bit

For Excel: Click Here

For Access: Click Here 

For QODBC bundled with QuickBooks Enterprise Edition, we suggest installing Office 32-Bit - How to convert MS Office from 64-bit to 32-bit.

Office 32-Bit

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

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

$conn.open()

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

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

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

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

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

$conn.open()

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

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

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

$null = $da.fill($dt)

$conn.close()

$dt

Please refer: To how to configure QRemote.

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

Also, Refer:

QODBC Reports Architecture Mismatch Problem When connecting

Problem Description:

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

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

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

Any Assistance Is Appreciated.

Solution:

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

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

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

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

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

First, you need to create 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 Linked Server, You need to create a new database in SQL Server for creating the stored procedure which accepts SQL queries. The stored procedure will call the linked server and execute the query.

To create a database, You need to right-click on "Database" & click on "New Database."

Enter the database name & click "OK." In this example, I am creating the database "QODBC."

Database created.

Now, We will create a stored procedure that accepts SQL queries. The stored procedure will call the linked server and execute the query.

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 New Stored Procedure.

We will create two stored procedures first one will give us a list of all customers & second procedure will accept SQL queries through parameters & execute them on the linked server & provide the query result.

We will create the stored procedure named GetCustomer, which will query the Customer table through the linked table in SQL Server & provide a list of the customers.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetCustomer

AS
BEGIN
SET NOCOUNT ON;

SELECT * from QRemote...customer

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

By executing this stored procedure, we will get a list of the Customers.

We will create a second stored procedure named QODBCQuery which will accept SQL query through parameters & execute on the linked server & provide the query result.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE QODBCQuery

@sqlQuery nvarchar( 4000)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(QRemote,''' + @sqlQuery + ''')'
EXEC (@TSQL)

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

We must pass the query as the parameter value to execute this stored procedure.

Query results through the stored procedure.

Now, We will create SQL Server DSN on the non-SQL Server machine to access QuickBooks Data remotely via SQL Server.

Locate the QODBC Driver for the QuickBooks program group on your desktop. Click on the Configure QODBC Data Source icon to create SQL Server DSN.

Click on the System DSN tab to create a DSN that both Windows applications and services can call. Click on the Add button to create a new DSN.

Select SQL Server & click "Finish."

Enter the DSN name and SQL Server machine IP address & click "Next."

Select the authenticity of SQL Server & click "Next."

Select the SQL Server database where we have created the stored procedure & click "Next."

Click "Finish."

Click on "Test Data Source.." to test the connection.

Test connection success.

Note: If you are using the latest version of QODBC, you can use the "QODBC 64-Bit Test tool"

Open the "QODBC 64-Bit Test tool" from the start menu

Click on the "Drop-down Menu" button

Select DSN from the Drop-down list

For example: "QuickBooks Data 64-Bit QRemote."

Click on the "Connect" button.

Write down the query in the marked field. Please refer to the image

Click on the "Run" button

Note: The above Query is, for example. You can change it as per your requirement.

First, you need to create 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 Linked Server, You need to make the stored procedure, which will read data from the Linked Server table & write it into 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 in 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 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 SQL Server Database by executing this stored procedure.

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

You can add this stored procedure to SQL Agent & schedule it. So it will automatically add all tables to 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 popup 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 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 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 Query Wizard. Choose any table and click "Next" until you reach 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 below message box about 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 below code 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

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 be should have a gap of 15-30 seconds for switching operation.)

This is applicable 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 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 below message box about 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 Excel sheet1.

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 load the company file that you have chosen in athe uto 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 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 Query Wizard. Choose any table and click "Next" until you reach 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 below message box about 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 Excel sheet2.

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 below code 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 is required to close the company file and swiswitch 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 "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 Command Button & add the below code 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 customer's name & display it in msg box:

ADODC with QODBC

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

Now we will configure ADODC control by right click on ADODC control & click 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 Customer table data through ADODC control.

Now we will configure text boxes to display data from 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, 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 below steps 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 Flat File Destination. Wes, we need to add Data Flow Task in the Control Flo to the perform tab. You can add it by double click on Data Flow Task.

4. We need to add ADO.Net Source in the Data Flow tab. You can add it by double click on ADO.Net Source.

5. Now, we need to configure ADO.Net Source. By right click on ADO.Net Source & click on Edit.

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

Add 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 condition & accessing report data using sp_report. In this example, I have selected Table or view as Data Access Mode & selected 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 Flat File Destination in the Data Flow tab. You can add it by double click on Flat File Destination.

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

8. Now, we need to configure Flat File Destination. By right click 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 which is containing 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.

For MS Access 64-bit, Please refer: How to Configure QODBC to Work With 64-bit MS Access

Setup QODBC to use with MS Excel 2016

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.

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

Before starting, please make use you have the 64-bit version of MS Excel:

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 64-bit QRemote' as an example:

NOTE: If you are using QODBC Online, Please select "QuickBooks Online Data 64-bit" instead of "QuickBooks Data 64-bit QRemote".

The first time you use of MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, always" option and click the "Continue" button. In the next window, click the "Done" button to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. In the Choose Columns window, select a table and the columns you want to import. Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here, I choose table Customer as a sample.) You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to set any query, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, you can view and edit these records, but the data is static. The modification will not be uploaded into QuickBooks.

You can view the detailed connection properties by clicking the "Queries & Connections" button in the Data menu:

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, and you can view or edit data via SQL Statement.

Click the "SQL" button to view the SQL Statement of this query:

The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only the records with a Name having a value equaling "Abercrombie, Kristy." Select the field header name and click on it, following selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button, and from the list of values, select "Abercrombie, Kristy" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

When you finish the query, choose to save so that next time, you can directly use the same question:

Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Also, Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016, Microsoft excel 2013 blank window

For 32-bit, Please refer: To how to Configure QODBC to Work With 32-bit MS Access.

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Please make sure QRemote Server is running. You can find the QRemote Server running in System Tray. 

If it is not running, then please start QRemote Server from the following:

QODBC Desktop - Start>>All Programs>> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

QODBC Online - Start>>All Programs>> QODBC Driver for QuickBooks Online >> QRemote Server for QODBC Online

QODBC POS - Start>>All Programs>> QODBC Driver for use with QuickBooks POS >> QRemote Server for QODBC POS

Open MS Access 2016, and make sure you are using the 64-bit version of MS Access from:

File->Account->About Access

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank desktop database:

In the newly created database, you will find a default blank table. Please close this default table:

You could directly extract QuickBooks records into this table. But in this article, we will show you how to build a linked table to access QuickBooks.

Build a connection via the ODBC database button in the External Data menu:

OR

Build a connection via External Data -> New Data Source -> From other sources -> ODBC Database :

Under the Get External Data window, you will see two options:

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as an example.)

NOTE: QODBC Online users, Please select "QuickBooks Online Data 64-bit" instead of "QuickBooks Data 64-bit QRemote". QODBC POS users, Please choose "QuickBooks POS Data 64-bit" instead of "QuickBooks Data 64-bit QRemote".

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract. Multiple Selection is available.

After the Linking table is successful, you will see linked tables been created in your MS Access file under "Tables":

You can view the property of these linked tables by right-clicking the linked table-> selecting linked table manager:

If you want to view QuickBooks records in this linked table, double-click the attached table, then you will see the records:

Note: If you see ###### in the linked table result, you need to increase the column header size. So you can see the actual result.

The linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table, and QODBC will automatically update the modification to QuickBooks.

Note: When you run an application that is not entirely ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Please refer: To how to use MS Access as Middleware with QODBC for using MS Access as Middleware. 

Suppose you want to import a table in MS Access. In that case, you have to select Import the source data into a new table in the current database option instead of a link to the data source by creating a linked table & repeat the above steps: this option will directly extract tables into Access Database, you can view and edit these tables, but all modification will not be uploaded unless you build another query to do so.

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

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

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 below steps to resolve this error:

1. Go to Import Export Wizard by Right click 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 data type columns.

You need to replace 23 to 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 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 below steps:

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 QuickBooks user Admin.

Before using the MS Excel Power Query application, please make sure you have installed 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 use 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 lining 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 " is 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, CompanyName, Phone & Email fields.

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

Please refer to the sample VBA code for Importing Customers to QuickBooks through 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 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 InvoiceLine. We will import these fields into QuickBooks using VBA. You can see the below records from the Invoice CSV file.

Please refer to the sample VBA code for Importing Invoices to QuickBooks through 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 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:

I used the QuickBooks Pro 2015 trial version and purchased QODBC's latest version.

I have an Access database with linked QuickBooks tables. I also have Access tables, some of which parallel the QuickBooks tables (for testing).

One of these tables is the Customer table. If I use the Access local version of the Customer table, I can edit records and move to the following form, and data is saved.

If I use the QODBC linked Customer table, the edited value appears in the field, but when I leave and return to the record, the pre-edit value appears. If I re-edit the record and move to the next record, I always get an error message that another user opens the record, and the pre-edit value appears.

In both cases, the same form is used. Only the queried table fields change; therefore, I know this is either a QuickBooks or QODBC generated problem.

If I open the linked Customer table in the datasheet view and edit a record and move to the next record, the same happens – the edit is not saved, and the record value does not change when I move to the following form.

I am the only user as I am developing an interface with QuickBooks using QODBC and QuickBooks, and my Access program only resides on my PC.

How do I get QuickBooks or QODBC to save my edits to the record and stop returning the error message that 'the record is used by another user and cannot be preserved?

Solution:

Please ensure that the record is not used from the QuickBooks UI in edit mode or that any other user is modifying the same. You can open QuickBooks in a single-user way & try to alter the record. Before changing the record, make sure that the record is not open from QuickBooks UI.

The MS Access table will require a refresh if updated records are not showing up. So please close the MS Access linked table & reopen it again. You will get updated records.

If the above steps do not resolve the issue, try to create a new MS Access database file & link table and try to edit the record and share the outcome. 

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 passes 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 passes 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 starts 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 Linked Server on SQL Server and connect to QuickBooks Data using QRemote. When you query Linked Server/tables, you will always get live QuickBooks Data.

For 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:

MS Excel shows a data protection error when we click on the "Refresh All" link.

When you click "OK," you will see the error below.

Solution:

You are receiving this error because your Excel sheet is in Protected Mode.

When the worksheet is protected, all cells are locked by default, meaning they cannot be edited.

It would be best to unprotect the worksheet by clicking the "Unprotect" link from File>>Info Tab In MS Excel 2010.

After removing protection, you can Refresh Data through the "Refresh All" link.

Also, Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

For system requirement details of QODBC Online, click here.

How to install QODBC Online & Using 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 QODBC Online

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

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

Create an Invoice with 3 Lines

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

First Line

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

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

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

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

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

Second Line

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

Third Line

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

Header Information

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

Results in QuickBooks

Related Data Location

InvoiceLineItemRefListID

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

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

InvoiceLineSalesTaxCodeRefListID

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

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

CustomerRefListID

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

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

ARAccountRefListID

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

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

ItemSalesTaxRefListID

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

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

CustomerSalesTaxCodeRefListID

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

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

Example of adding InvoiceLine items to an existing Invoice

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

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

OR

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

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

Problem Description:

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

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

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

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

The basic flow is like this:

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

cnQODBC.Open()

[Repeat for every sales order line item]

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

cmdQODBC.Parameters.AddWithValue(", "strCustomerListID)

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

cmdQODBC.Parameters.AddWithValue(", "strLineClassListID)

cmdQODBC.Parameters.AddWithValue(", "strTemplateListID)

cmdQODBC.Parameters.AddWithValue("", intSalesOrderNumber)

cmdQODBC.Parameters.AddWithValue("", strLineItemListID)

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

cmdQODBC.Parameters.AddWithValue(", "intQuantity)

cmdQODBC.Parameters.AddWithValue(", "dblLineRate)

cmdQODBC.Parameters.AddWithValue(", "dblLineAmount)

cmdQODBC.ExecuteNonQuery()

[End repeat]

cnQODBC.Close()

cnQODBC = Nothing

cmdQODBC.ExecuteNonQuery()

Solutions:

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

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

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

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

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

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

Instead of

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

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

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

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

Problem Description:

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

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

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

Msg 7343, Level 16, State 2, Line 1

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

Solutions:

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

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

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

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

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

Please refer:

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

How to use parameters in OPENQUERY

Solutions:

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

Select Query:

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

SELECT @ID = '19650'

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

EXEC (@TSQL)

Update Query:

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

SELECT @ID = '80000146-1513345553'

SELECT @CName = 'New Company'

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

EXEC (@TSQL)

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

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. 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 specifically from and to dates for more flexibility.

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



Function:

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


Example:

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

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



Put Some Checks into a Table:

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


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

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

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

a beginning date

an ending date

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



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

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


Also, refer to the following:

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

Problem Description:

I 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 below 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 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:

When ting sets QODBC POS DSN to access the company file running in QuickBooks POS (Version 10), it will fail to connect to the company file. QuickBooks POS will search for the server with no response.

Solutions:

There is a limitation of QuickBooks POS SDK 10 that it will not be able to get the currently open company file name & thus, QODBC POS is not able to connect the now available company file.

QuickBooks POS SDK Test Plus tool also requires a connection string; It will not connect QuickBooks POS without a connection string.

For Example:

Connection String = " ComputerName=WIN-O8RQBVCMJFC;CompanyName=SampleCompany;Version=10 "

You must provide the connection string to connect the company file as a workaround for this issue. Please follow the below steps:

1) Please change QuickBooks POS to run in multi-user mode. Click the "Switch Company File to Multi-User Mode" option in File Menu. 

2) Build Connection String:

Click the "..." button at QODBC POS Setup Screen->General Window to enter the company file selection window: 

3) In the company file selection window, QODBC POS will give you a list of all available company files (may take a few seconds) :

QuickBooks POS will ask you to grant permission to QODBC POS: 4) Select the company file you want to access and click ok. Then you will see the connection string has been built in the "Current Company Setting" option. And then, please click the "Test Connection" button.  

5) Click the "Yes Always" button. If QODBC POS tells you the connection is successful, you can use our test tools VB Demo to view QuickBooks POS records via QODBC POS.

6) Now, we will test the connection using QODBC POS Test Tool. Please open QODBC POS Test Tool from:
Start>>All Programs>> QODBC Driver for use with QuickBooks POS>>QODBC POS Test Tool.

7) Please select the "QuickBooks POS Data" DSN from the dropdown list & click on "Connect."

8) We can connect to DSN from QODBC POS Test Tool. Please write your SQL statement & click on "Run."

9) We can fetch data from QODBC POS Test Tool. 

Problem Description:

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

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

Please see the following SQL statement:

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

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

Solutions:

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

Please refer below mentioned link to resolve this issue:

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

You can either apply hotfix or change registry values.

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

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

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

Problem Description:

I 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 the select through 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 you, please let me run the above command entirely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of documents in the QuickBooks company file. Please let this command run entirely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.

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

Please refer: How to Use QODBC with Microsoft Access 2010

Problem Description:

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.

MS Excel 2013 or Access 2013 Shows This function requires activation of QODBC Read Write or Server Edition.

MS Excel 2013 or Access 2013 (Office 365) crashes when trying to connect to QuickBooks Data.

Note: For Automatic Fix/Patch, Click Here

Keywords: regfile 1license error

Expired Evaluation

MS Excel 2013 or Access 2013 

RegFile1

"Expired Evaluation" error message even though activated license

Office Crash

Expired Evaluation

Excel crash, Access Crash, Excel 2016 crash, Access 2016 crash

Problem Description:

When using Prepared Statements with the QRemote driver, it throws an error called ODBC driver not supported. How can I use Prepared Statements with QODBC/QRemote?

Solutions:

You may use a function/feature that the ODBC driver does not support. You can use MS-Access as middleware, i.e., Create linked tables in MS-Access, and connect your application to MS-Access. MS-Access has a linked table relating to QRemote or QODBC.

First, you need to create the linked table in MS Access. Please refer to the mentioned article for making the linked table in MS Access.

For 32-bit MS Access: How to Configure QODBC to Work With 32-bit MS Access

For 64-bit MS Access: How to Configure QODBC to Work With 64-bit MS Access

Please Note 64-bit MS Access article contains information about the Import table; Please choose the "Link to the data source by creating a linked table" option for creating the linked table.

Now we are creating MS Access DSN that points to the Access database file we created in the above steps. Please follow the below steps for creating MS Access DSN.

1) Please create a new MS Access DSN from:
Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" Tab>>Click on "Add"

2) Choose "Microsoft Access Driver (*.mdb, *.accdb)" & click on the "Finish" button. 

3) Enter DSN name & description & click on select.

4) Provide the MS Access database file location, which we created earlier & click on "OK." 

5) Click on "OK" to save DSN.

6) Newly created DSN is available in the DSN list.

7) Now, we will use this DSN as a Middleware. We will test this DSN with our QODBC Test Tool application. Please open QODBC Test Tool from:
Start>>All Programs>> QODBC Driver for use with QuickBooks>>QODBC Test Tool

8) Please select the newly created DSN from the dropdown list & click on "Connect."

9) We can connect to MS Access DSN from QODBC Test Tool. Please write your SQL statement & click on "Run."

We can fetch data with MS Access DSN from QODBC Test Tool. 

Note:  QODBC Support Wizard is used to test QODBC SQL queries only and is not a development tool; you should be able to use the MS Access DSN ("Access Middleware") on your application.

I've built a table in Access of invoice lines to be added to existing invoices. I have the TxnID and all the necessary xxxxListID fields etc., with the item, quantity, and values coming from other sources. So "all" I have to do now is to feed these records, one by one, into an INSERT INTO . . . VALUES . . . statement. I'm an experienced Access user, but a VBA-by-the-seat-of-my-pants-because-I-can't-find-a-good-training-course user - as I suspect many others may be!

To begin with, so I can see the thing works, I'm using a form based on my table with a combo box to select one record, so I thought I'd be able to have the thing look like this:
DoCmd.RunSQL "INSERT INTO QB_InvoiceLine ( TxnID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, " & _
"InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineTaxCodeRefListID, " & _
"CustomFieldVFBatchNo, FQSaveToCache )" & _
"VALUES ('Me.TxnID', 'Me.RefNumber', 'Me.InvoiceLineItemRefListID', 'Me.InvoiceLineDesc', 'Me.InvoiceLineQuantity', " & _
"'Me.InvoiceLineRate', 'Me.InvoiceLineClassRefListID', 'Me.InvoiceLineTaxCodeRefListID', 'Me.CustomFieldVFBatchNo', 0)"
It tries to add a record but fails - one field is set to Null due to a type conversion failure.
Am I on the wrong track, or is it just some annoying little thing I've missed?

Answer:

You are on the right track. InvoiceLineRate is a decimal, but if you try inserting it as a string, "'Me.InvoiceLineRate' should be "Me.InvoiceLineRate, without single quotes.

Question:

Thank you for your usual prompt reply, but then it asks me for my value of Me.InvoiceLineRate and the insert fail with Runtime Error 3155 - Incorrectly built XML from Update start (#10045). I thought I had already posted the first part of this reply, so forgive me if this is a repeat. I didn't receive a notification that my post had been accepted.

I tried removing the quotes from the two numeric fields - Me.InvoiceLineQuantity and Me.InvoiceLineRate now asks for their value, and when I give it values, it then fails to insert - Runtime error 3155, Incorrectly built XML from Update Start (#10045).

I made the procedure display the two values in a message box - they're fine. If I take them out of the insert, I still get the same error, but with no prompts for the values of any fields first. Since custom fields have been known to misbehave, I've tried removing the last area too, but I still have the same problem.

Answer:

[QODBC] Incorrectly built XML from Update Start (#10045)

It most likely means that one of your listed values or the TxnID is incorrect for the company file you are using. Listings and TxnIDs are unique to each QuickBooks company file.

Question:

I checked all my listings and TxnID - I can query them and return the correct record. I reduced to one connection - to the open QuickBooks file, deleted the OPT files (one in Program Files, one in Documents and Settings - eh? There seems to be a disparity between using 'Configure QODBC data source' and 'QODBC Setup screen') and switched off optimization, re-booted (on principle) and tried this in VBDemo:

INSERT INTO InvoiceLine ( TxnID, CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineAmount, FQSaveToCache) VALUES ('11CC9-1157644406', '200000-1135954083', 'I13462', 'DC40000-1157657910', 235.89,0) It says 'Error parsing complete XML return string.'

But if I remove the TxnID:

INSERT INTO InvoiceLine ( CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineAmount, FQSaveToCache) VALUES ('200000-1135954083', 'I13462', 'DC40000-1157657910', 235.89,0) - the invoice is inserted correctly. But of course, this means I get another invoice, not another line on an existing invoice.

Do I remember reading somewhere on the forum that there's a problem with using TxnID on inserts on the UK version? If so, I'll have to do this differently - hold the header data from the existing invoice in QuickBooks, delete it, then create a new invoice with the saved header data and my 'built' item lines.

Answer:

When you add lines to an existing invoice, use the line detail columns only and not any of the header details, as the Invoice header already exists, and don't use FQSaveToCache. For example:

INSERT INTO "InvoiceLine" ("TxnID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineRate," "InvoiceLineAmount," "InvoiceLineTaxCodeRefListID") VALUES ('5CA9-1197764583', '250000-933272656', 'Bin Permit Renovations,' 200.00000, 200.00, '20000-999022286')

It would be best if you looked at the bottom of the Review QODBC Messages and Review SDK Messages, logged in to the QODBC Setup screen for the actual error message, and posted the last entry sections only so we can see what the problem is. We will also need to know the revision and update of the copy of QuickBooks you are using.

First, please create a new MS Access database & link ALL tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

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

Create an Invoice with 3 Lines

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

First Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

Header Information

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('620000-1071526513', '40000-933270541', #9/23/2005#, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', #10/31/2005#, #10/01/2005#, '360000-1071521681', 'Memo Test,' 0, '10000-999022286') 

Results in QuickBooks 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '320000-1071525597' and found like this:

     Select ListId, FullName from Item where FullName like 'C%'

InvoiceLineSalesTaxCodeRefListID

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

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

CustomerRefListID

CustomerRefListID is '620000-1071526513' and found like this:

    Select ListId, FullName from Customer where FullName like 'Smi%'

ARAccountRefListID

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

     Select ListId, Name from Account where Name like 'Account%'

ItemSalesTaxRefListID

ItemSalesTaxRefListID field is related to ItemSalesTax, ItemSalesTaxGroup, and ItemSalesTaxGroup,Line depending on InvoiceLineType. If InvoiceLineType is GroupItem, then it is found in the ItemSalesTaxGroupLine table.

ItemSalesTaxRefListID is '360000-1071521681' and found like this:

     Select ListId, Name from ItemSalesTaxGroup where Name like 'E%'

CustomerSalesTaxCodeRefListID

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

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

An Example of Creating One ItemInventoryAssembly with Two Lines

First, please create a new MS Access database & link ItemInventoryAssembly, and ItemInventoryAssemblyLine tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.
Note the FQSaveToCache field, set to True, except on the last one.

First Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '160000-933272656', 2.0, 1)

Second Line

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, COGSAccountRefListID, AssetAccountRefListID, ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity, FQSaveToCache) VALUES ('NewAssembly1', 1, 'QODBC New Assembly1', '340000-933270542', '1E0000-933270542', '60000-933270541', '130000-933272656', 1.0, 0)

Result in QuickBooks  

Questions and Answers

Question

     This seems to work for adding multiple items (ItemInventoryAssemblyLine records) to an assembly when the assembly is first created.

     However, I am having trouble adding new ItemInventoryAssemblyLine records to an existing assembly. When I try to repeat an INSERT with FQSaveToCache set to 0, I get an error.

     QODBC Error 3100 - The name "NewAssembly1" of the list element is already in use (#10053)

Answer

   I don't think we can add items to an existing assembly. The workaround would be to create a new assembly with the additional items. 

Problem Description:

I want to add new assemblies to the item list. I've tried the two queries below. Both result in this error:

invalid operand for the operator: <assignment>
code 11024
Can you suggest a query that will work? 

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName) VALUES ('12082-74262', 'Active,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset'); 

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass) VALUES ('12082-74262', 'Active,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 'TW,' 'am');

Solution:

Due to this issue, the IsActive field contains a boolean datatype & it accepts 1/0 or true/false as input. I have used 'Active' as a value in the IsActive field. Please use the below query & try again.

INSERT INTO ItemInventoryAssemblyLine (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 1)

INSERT INTO ItemInventoryAssembly (Name, IsActive, SalesDesc, IncomeAccountRefListID, IncomeAccountRefFullName, PurchaseDesc, COGSAccountRefListID, COGSAccountRefFullName, AssetAccountRefListID, AssetAccountRefFullName, CustomFieldcoo, CustomFielditemClass, FQSaveToCache) VALUES ('12082-74262', 1, 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875; Tera Gear', '80000038-1351453685', 'BBQ Sales Income,' 'burner assy, oval twin, 3" offset, ss, 13.875 x 3.875', '80000046-1351453685', 'Cost of Goods Sold: Materials - Cost of Goods Sold,' '80000021-1351453684', 'Inventory Asset,' 'TW,' 'am,' 0)


Please refer below-mentioned link for getting details about the QODBC Table schema:

QODBC Data Layouts

For 64 Bit, Please refer: How to Configure QODBC to Work With 64-bit MS Access.

Problem Description:

Everything worked perfectly with the installation and linking to MS Access 2010 and QuickBooks Pro11. The tables I needed were related to my database in access. However, how do I update (or can I update) QuickBooks using a form (linked to my tables downloaded to Access Form using QODBC )in MS Access?

Solution:

You can insert/update/delete records by accessing local table. You need to write a VBA script to perform the SQL operation or Can, execute existing Queries Or can link the form to the existing table.

Please refer below steps which contain a sample tutorial for How QODBC can work with VBA. The tutorial is for sample reference only. For further requirements, you need to develop a form or write scripts that will fetch data from a local access table and perform the insert operation in QuickBooks using QODBC.

Creating a Form in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks Data files in the same fashion as Access databases.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office:

A default database file, name Database1 appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. acid. Save the file as type "Microsoft Office Access Databases."

Download Sample

Then Click "Create" to create a blank database:

After  the new database is created, Create a New Form via the "Form Design" link in Create Tab:

After  the new form is created, Insert a button in the form:

Rename the button as per your requirement (In this example button name is "Select Customer"):

Now go to the button property window and set the property for the "On Click" event. Select "Event Procedure" from the dropdown list, then click on the "..." button:

The new window is open, which contains VBA code (In this example, it contains code button click event):

Now Insert the module in the form by Right-clicking on the form:

Write code in the module (In this example, I am writing code for the selection of customer name):

Link newly created module with button click event:

Now click on the "Select Customer" button, and you will get the Name of all customers:

Repeating the above steps can add functionality to your VBA code as per your requirement. In this example, I have added functionality for Insert, Update & Delete of customer records:

By clicking on the "Insert Customer" button, the New customer is added with the name "Testing VB":

By clicking on the "Update Customer" button, Newly added customer name is updated with the name "Updated Testing VB":

By clicking on the "Delete Customer" button, Newly added customer is deleted:

Please refer below code which is used in this example:

Select Customer:

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

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

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

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

Insert Customer:

Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Insert into customer (Name) values ('Testing VB')"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg

Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Update Customer:

Public Sub exampleUpdate()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Update customer set Name='Updated Testing VB' where name='Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)

sMsg = sMsg & "Record Updated!!!"
MsgBox sMsg

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

Delete Customer:

Public Sub exampleDelete()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "delete from customer where name='Updated Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Deleted!!!"
MsgBox sMsg

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

Download Sample

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

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

Start your "SQL Server Management Studio"

Login to your SQL Server.

On the left-hand side panel, you will find the Server, Right Click on the "SQL Server" and Select Properties.

Please Verify that your SQL Server is 64-bit.

64 and 32-bit words are kept separate in the Microsoft Windows Operating System. QuickBooks 2021 and older versions are 32-bit applications, so a 32-bit ODBC driver is required to communicate with it.

QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-bit Web Servers and 64-bit applications to talk to QuickBooks live for the first time via the QODBC Driver.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect. We call it QRemote.

QRemote also works for 32-bit applications as an alternative to Dcom.  As SQL Server is a service-based application, it has to either use Dcom or QRemote.

QRemote has two components "QRemote Client Driver for QODBC" and "QRemote Server for QODBC." 

QRemote Client Driver: Allows 64-bit Applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.  

QRemote Server: Processes requested instruction from QRemote Client to QuickBooks using QODBC DSN Driver. 

Start "QRemote Server for QODBC POS" from the start menu. ( If QRemoteServer is not running ) 

Double-click the QRemote Server icon from the system tray

QRemote Server Main screen shows the IP address and Port allowing the QRemote Client to connect.

Note the IP address and Port number listed on the QRemote Server "Event Logs window."

Create a new DSN entry/or configure the existing one

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks POS, and click

Configure QODBC POS Data Source 64-bit

"QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC.

For 32-bit SQL Server change, QRemote DSN to "QuickBooks POS Data QRemote" is the default DSN created when installing QODBC.

Configure the DSN and provide the IP address and Port as listed under "QRemote Server."

Under the QRemote for QuickBooks – Client  DSN configuration screen, Please fill in the following information. 

• Data Source Name: Name of your DSN entry 
• Server: IP address displayed on QRemote Server 
• Port: Port displayed on QRemote Server 
• Encryption Key: Keep default key "QODBC#QRemote$1" (In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

• Remote DSN: select the QODBC POS DSN from the list. ( Make sure QRemote Server is running) 

Start Your QuickBooks POS.

Run MS SQL 2005 Dev / 2008 / 2012 (64-bit) and login in

    Run SQL Server Management Studio. You'd better choose SQL Server Authentication and input the right name and password. 

Under SQL Server Management Studio, Navigate to "Server Objects" -> Linked Server  -> New Linked Server.

 OR

Provide the "New Linked Server" information

On Clicking OK, it will start establishing a connection to QuickBooks. Please accept the certificate for allowing data access on QuickBooks.

After you have accepted the certificate, the Linked server will be shown under the "Linked Server" Category.

Configure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers)

   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 dynamic parameter should be checked to allow parameter queries. Level zero only should be checked to enable four-part naming to work. Allow InProcess should be checked to help performance. Non-transacted updates should be checked if you want to be able to update QuickBooks data. Index as access path should be checked to help performance. Disallow adhoc accesses should be unchecked to allow OPENQUERY to function.

QODBC/QRemote is ready to use with SQL Server/Linked Server

Execute below query

Select * from qremote…customer

On each query, QRemote Server Logs the events. If you are facing any issues or delays in response, please refer QRemote Server Event Logs for details on which stage it's taking more time.

Another Test

Select * from qremote…employee

On each query, QRemote Server Logs the events. If you are facing any issues or delays in response, please refer QRemote Server Event Logs for details on which stage is taking more time.

Errors and Solutions 

"[QODBC-POS] QODBC-POS  Server Edition License Required."

The QODBC CDKEY License you have activated is not of the Server edition. Please get in touch with our Sales or Support team. http://support.flexquarters.com/esupport/

“[QODBC-POS] Evaluation Expired”

You have run out of the 30-day trial/evaluation version. If you still wish to evaluate QODBC, please get in touch with our Sales team.

"QRemote" or "QODBC" contains multiple tables that match the table "TableName."

This means you have not configured the "MSDASQL" provider. Please refer above steps and configure the "MSDASQL" provider.

"[QODBC-POS] Unable to Open License File"

Please use "QuickBooks POS Data 64-bit" DSN or use the change connection string DSN=QuickBooks POS Data 64-bit; OLE DB Services=-2;

Please accept the certificate for allowing data access on QuickBooks. Please click on "Test Connection." It will start establishing the connection to QuickBooks.

Now please click certificate." It should display a table list.

Now please test the connection on the linked server by right click on the linked server name. Please click on "Test Connection."

You will receive a Test Connection success message.

Also, refer to

Using QuickBooks data with MS SQL Server using QODBC Driver and QRemote (Video)

QODBC Reports Architecture Mismatch Problem When connecting

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

Keywords: SQL Server 2008 Management Studio, SQL Server 2016 Management Studio, SQL Server 2012 Management Studio, SQL Server Management Studio, SSMS

Problem Description:

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

Is it possible to have a connection setup in an Excel file and be able to refresh on either a 64 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 below example:

1) Machine A of 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 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. 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 newly created DSN to old Excel files.

Open the Excel file. Please Go To Data Tab & 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.

Please refer to the below functions & try to execute them in QODBC Test Tool.

Functions in Select Query:

You can use distinct like:

SELECT DISTINCT CustomerRefFullName FROM InvoiceLine

Please refer to the below article for more information:
QODBC Function-List

You can use SubQuery in QODBC like:
SELECT * from customer WHERE TimeModified IN (SELECT TimeModified from customer WHERE TimeCreated < {ts '2007-06-17 00:00:00.000'})

Functions in Update Query:

You can Replace functions in the Update query like:

UPDATE TableName SET ColumnName = {fn REPLACE("ColumnName",'ValueToFind','ValueToReplace')} WHERE (condition)

For Example:

UPDATE Invoice SET RefNumber = {fn REPLACE("RefNumber",'71047','0777')} WHERE (TxnDate>{d '2012-12-31'})

You can change the table name & column name as per your requirement.

For 64 Bit, Please refer: How to Configure QODBC to Work With 64-bit MS Access

Creating a Table in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks POS Data files in the same fashion as Access databases.

Open MS Access 2013. You can find it via Windows Start->All Programs->Microsoft Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases".

Then Click "Create" to create a blank database:

After  the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

Under the Get External Data window, you will get two options:

Import the source data into a new table in the current database: this option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query to do so.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. Multiple Selection is available.

If the Import/Linking works without any problems, you will see linked tables been created in the table list of MS Access:

If you want to view QuickBooks records in this linked table, double-click the linked server, then you will see the records:

The linked table is always in sync with QuickBooks POS Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table, and QODBC will automatically update the modification to QuickBooks.

Note: When you run across an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

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

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.

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

Please select PowerPivot Window.

Please select the data input method via From Other Sources->.

And then, in the "Connect to a Data Source" Window, please select Others(OLEDB/ODBC).

After clicking next, Please specify the connection string by clicking the "Build" button:

Select DSN "QuickBooks Data" from the DSN list.

Now you will get Connection String to build automatically.

Please click the Next button.

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Choose How to Import The Data. (Choose the second option, "Write a query that will specify the data to import")

Write a SQL Statement that will specify the data to import.

Return Data to Microsoft Office Excel PowerPivot will directly export the data to PowerPivot, you can view and edit these records, but modification will not be uploaded.

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

Keywords: excel 2010 PowerPoint tutorial

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 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 Updated Custom Field.

     Please refer to the below-mentioned link 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 connection between QODBC & QuickBooks. But still, I am unable to get 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 which you have assigned it.

For Example: If you have assigned CustomField in the Customer, Vendor, Employee table, you can find it from Customer, Vendor, Employee. If you have assigned 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 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. Custom Field will display in QODBC after disconnecting all the links and connecting again.

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

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

You can download the QODBC Excel Template, which has most of the tables and reports preconfigured.

All you have to do is download the Excel Template and copy the file to the Default Template folder.

To download the QODBC Default Template, Please click here

After downloading, Please Copy/Install it to Excel's default template folder
You can copy any Excel workbook into the Templates folder. Then you can use the workbook as a template without saving it in the template file format (.xltx or .xltm). In Windows Vista, the Templates folder is usually C:\Users\<your name>\AppData\Roaming\Microsoft\Templates. In Microsoft Windows XP, the Templates folder is usually C:\Documents and Settings\<your name>\Application Data\Microsoft\Templates.

Please Refer Below links for detail

Customizing Excel defaults with templates - Excel - Office.com

or

Where do I find templates? - Excel - Office.com - Microsoft

When you start Excel, Please select "My Templates."

Select the "QODBC Default Template" and click.

A new Excel workbook will be created and automatically fetch most of the tables and reports.

Please feel free to modify the workbook/queries/reports.

Also, refer

How to Use QODBC with MS Excel 2007

How to Use QODBC with MS Excel 2010

How to Configure QODBC to Work With 64-bit MS Excel

Accessing QuickBooks data with QODBC Driver using MS Excel 2010 & MS Access 2010 (Video)

Keywords: msxel desktop download

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

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

Start your "SQL Server Management Studio"

Login to your SQL Server.

On the left-hand side panel, you will find the Server, Right Click on the "SQL Server" and Select Properties. 

Please Verify that your SQL Server is 32-bit.

64 and 32-bit words are kept totally separate in the Microsoft Windows Operating System. QuickBooks 2021 and older versions are 32-bit applications, so in order to communicate with it, a 32-bit ODBC driver is required.

QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-bit Web Servers and 64-bit applications to talk to QuickBooks live for the first time via the QODBC Driver.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect. We call it QRemote.

QRemote also works for 32-bit applications as an alternative to Dcom.  As SQL Server is a service-based application, it has to either use Dcom or QRemote.

QRemote has two components "QRemote Client Driver for QODBC" and "QRemote Server for QODBC." 

QRemote Client Driver: Allows 64-bit Applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.  

QRemote Server: Processes requested instruction from QRemote Client to QuickBooks using QODBC DSN Driver.  

Start "QRemote Server for QODBC" from the start menu. ( If QRemoteServer is not running )  

Double-click the QRemote Server icon from the system tray 

QRemote Server Main screen shows the IP address and Port allowing the QRemote Client to connect.

Note the IP address and Port number listed on the QRemote Server "Event Logs window."

Create a new DSN entry/or configure the existing one

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks, and click

Configure QODBC Data Source 

"QuickBooks Data QRemote" is the default DSN created when installing QODBC. (Earlier, it was "QRemote Data 32-bit") 

Configure the DSN and provide the IP address and Port as listed under "QRemote Server." 

Under the QRemote for QuickBooks – Client  DSN configuration screen, Please fill in the following information 

·         Data Source Name: Name of your DSN entry 

·         Server: IP address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

·         Encryption Key/Password:

(By Default Encryption Key/Password is set to "QODBC#QRemote$1". In case you want to change your Encryption Key/Password, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

·         Encryption:

(By Default, Encryption is set to Off. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure encryption on QRemoteServer and use the same on QRemote Client DSN) 

·         Remote DSN: select "QuickBooks Data "DSN from the list. ( Make sure QRemote Server is running)  

Start Your QuickBooks.

Run MS SQL 2005 Dev / 2008 / 2012 /2016 (32-bit) and log in

    Run SQL Server Management Studio. You'd better choose SQL Server Authentication and input the correct name and password. 

Under SQL Server Management Studio, Navigate to "Server Objects" -> Linked Server -> New Linked Server.

 OR

Provide the "New Linked Server" information: Insert your desired Linked Server Name, select provider Microsoft OLE DB Provider for ODBC Drivers from the provider list & insert "QODBC" as the Product name & "QuickBooks Data QRemote" as the Data source & click "OK" button. 

How to build a QuickBooks IIS Web application using QODBC Driver and Microsoft ASP.NET (Video)

Download the sample code: Click Here

Also, refer to 
Sample C#.NET Web Application for Inserting InvoiceLine to existing Invoice

Sample Code for Inserting InvoiceLine to existing Invoice using PHP

Keywords: sample .net, sample, .net, Dot Net, C#,C#.Net, ASP.NET, Web Server, IIS, C#.NET

Accessing QuickBooks data with QODBC Driver using MS Excel 2010 & MS Access 2010 (Video) - 64-Bit (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)

QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.

QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.

Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to

How to Configure QODBC to Work With 64-bit MS Access.

How to Configure QODBC to Work With 64-bit MS Excel 

Accessing QuickBooks data with QODBC Driver using MS Excel 2010 & MS Access 2010

Sample Files : 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Microsoft Excel 2003 and QODBC

Also, Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2010 32-bit
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

Creating a link to live QuickBooks data in Excel

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 data in the worksheet to the most current data in the linked QuickBooks table.

     Before using Microsoft Excel, you must ensure that the Microsoft Query Add-on is installed. See the Microsoft Office with MS Query and QODBC topic.

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.

     Select the menu Data -> Import External Data -> New Database Query.

     Now that the following Data Source screen is available select the Data Source currently opened in QuickBooks and click OK. (Here, I choose the default data source with a sample company file open. And check to Use the Query Wizard to create/edit queries.)

     Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here, I choose table Customer as a sample.)

     The following screens allow you to filter and sort the data to be imported, and then you will reach the finish screen.

     You can return the data to Excel at this point or as in the above screen by selecting Return Data to Microsoft Office Excel. Or edit the data in MS query and apply additional criteria to the QuickBooks record set by selecting View data or edit query in Microsoft Query. (Here, I choose to edit in MS query as an example.) Then click Finish.

     The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, next select Criteria -> Add Criteria. In the popped-out Add Criteria window, the button specifies the criteria Field as "Name" and Operator as "equals," then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

     The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

     Select File -> Return Data to Microsoft Office Excel. The following screen will prompt you for the destination of the data:

     Click OK, and the data will be populated into the existing Excel worksheet.

     The data from your QuickBooks table is now included in your worksheet and remains connected to the QuickBooks table from which it was imported.

     Clicking Data -> Refresh Data the  button in the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Microsoft Access 2003 and QODBC

Creating a Table in Microsoft Access

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

     Selecting the menu File -> New -> Blank database.

     Here is a Microsoft Office Access blank database. A default database filename, db1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link.mdb. Save the file as type "Microsoft Office Access Databases." Click on Create.

     Click the New button -> Link Table -> OK. (You can also select the Import Table option to import the QuickBooks data into the Access project, but the imported data will not be updated along with it in QuickBooks.)

     In the popped-out window, change the "Files of type" list to the setting for "ODBC Databases."

     Then comes up with the Select Data Source window.

     Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

     Select one or more QuickBooks tables to add to your Microsoft Access project and click OK. (Here, I add the table Customer, for example.)

     Your QuickBooks tables are now part of your Microsoft Access project as externally linked tables. This does not mean the data in these tables have been imported into Access, but rather that Access will open and work with the live QuickBooks data files, using QODBC to communicate with them.

     Double-click the table name to bring up the data grid view.

     In this grid, you can modify, delete, and add new records to your QuickBooks table per QuickBooks's table/field accessibility. Your QuickBooks files are connected live to your Microsoft Access applications.

Note: When you run across an application that is not fully ODBC compatible, oftentimes, they do support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

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
Using QuickBooks Data with Access 2013(Import Tables) 32-bit
Using QuickBooks Data with Access 2013 (Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access

How to use QODBC with Microsoft Access 2016 / 365 / 2013 / 2019 64-Bit to Import QuickBooks Data using Import tables (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)

QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.

QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.

Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to How to Configure QODBC to Work With 64-bit MS Access.

Creating a Table in Microsoft Access:

Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Open MS Access 2016/2013; you can find it via Windows Start->All Programs->Microsoft Office 2016/2013:

Office 2016:

Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

In the newly created database, you will find a default table:

Close this table when there is no need to use it:  

Please Navigate to "External Data"

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

OR

Build a connection via External Data -> New Data Source -> From other sources -> ODBC Database :

Under the Get External Data window, you will get two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its connection to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in complete sync with QuickBooks.

This article will show you how to import data from QuickBooks into MS Access.

Import Data from QuickBooks

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you would like to extract. Multiple Selection is available.

It may take some time for MS Access to extract those records:

If the Import works without any problems, you will get a window reporting successful:

You can see those imported tables under the "Tables" list:

Double Click a table to view Record under the table:

These import records are stored as MS Access tables so that you can change the schema rules of these tables. Please remember these tables do not sync back to QuickBooks. Thus, all the modifications you make to these tables will NOT be updated into QuickBooks.

Note:  When you run an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Please refer: To how to use MS Access as Middleware with QODBC for using MS Access as Middleware.

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

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
2003 and QODBC

How to use QODBC with Microsoft Access 2016 / 365 / 2013 / 2019 64-Bit (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)

QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.

QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.

Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to Configure QODBC to Work With 64-bit MS Access.

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Open MS Access 2016/2013. You can find it via Windows Start->All Programs->Microsoft Office 2016/2013:

Office 2016:

Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

After  the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

OR

Build a connection via External Data -> New Data Source -> From other sources -> ODBC Database :

Under the Get External Data window, you will get two options:

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as an example.)

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. Multiple Selection is available.

If the Import/Linking works without any problems, you will see linked tables been created in the table list of MS Access:

If you want to view QuickBooks records in this linked table, double-click the linked server, then you will see the records:

The linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table. QODBC will automatically update the modification to QuickBooks.

Note:  When you run an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Please refer: To how to use MS Access as Middleware with QODBC for using MS Access as Middleware.

Suppose you want to import a table in MS Access. In that case, you have to select Import the source data into a new table in the current database option instead of linking to the data source by creating a linked table & repeat the above steps: this option will directly extract tables into Access Database, you can view and edit these tables, but all modification will not be uploaded unless you build another query to do so.

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

Keywords: read/write with ms access, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

For 64 Bit, Please refer: How to Configure QODBC to Work With 64-bit MS Access

Creating a Table in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks data files in the same fashion as Access databases.

Open MS Access 2010, and make sure you are using the 32-bit version of MS Access:

Then create a blank database from "File" -> "New":

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

In the newly created database, you will find a default blank table. Please close this table:

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

Under the Get External Data window, you will see two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to import data from QuickBooks into MS Access.

Import Data from QuickBooks

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract. Multiple Selection is available.

After all the tables are successfully imported with no errors, you will get a window reporting successful:

You will see those imported tables under the "Tables" list:

Double-click the table to view the data:

These import records are stored as MS Access tables so that you can change the schema rules of these tables. Please remember these tables do not sync back to QuickBooks. Thus all the modifications you do to these tables will NOT be updated in QuickBooks.

Note: When you run across an application that is not fully ODBC compatible, often, they do support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

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

Keywords: create odbc connection

For 64 Bit, please refer: How to Configure QODBC to Work With 64-bit MS Access

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Open MS Access 2010, and make sure you are using the 32-bit version of MS Access:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QB Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

In the newly created database, you will find a default blank table. Please close this default table:

You could directly extract QuickBooks records from this table. But in this article, we will show you how to build a linked table to access QuickBooks.

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

Under the Get External Data window, you will see two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as an example.)

NOTE:

If you use QODBC Online for QuickBooks Online, please select "QuickBooks Online Data" instead of "QuickBooks Data."

If you use QODBC POS for QuickBooks POS, please select "QuickBooks POS Data" instead of "QuickBooks Data."

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please select the tables you want to extract. Multiple Selections are available.

After the Linking table is successful, you will see linked tables been created in your MS Access file under "Tables":

You can view the property of these linked tables by right-clicking the linked table-> selecting linked table manager:

If you want to view QuickBooks records in this linked table, double-click the linked table, then you will see the records:

The linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. However, you can directly modify the data in the linked table. QODBC will automatically update the modification to QuickBooks.

QuickBooks Desktop:

You should be able to read and write data to your QuickBooks Desktop application. 

QuickBooks Online:

You should be able to read and write data to your QuickBooks Online application. 

QuickBooks POS:

You should be able to read and write data to your QuickBooks POS application. 

Note:  When you run an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

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

Keywords: create odbc connection, read/write with ms access, QuickBooks Online, QBO, Access, link tables

For MS Access 64-bit, Please refer: How to Configure QODBC to Work With 64-bit MS Excel

Setup QODBC to use with MS Excel 2010

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 data in the worksheet to the most current data in the linked QuickBooks table.

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.

Before starting, please make use you have the 32-bit version of MS Excel:

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

'QuickBooks Data' as an example:

NOTE: If you are using QODBC Online, Please select "QuickBooks Online Data" instead of "QuickBooks Data."

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, always" option and click the "Continue" button. In the next window, click the "Done" button to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. Select a table and the columns you want to import in the Choose Columns window. Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here, I choose table Customer as a sample.) You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to put any query, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, you can view and edit these records, but the data is static. The modification will not be uploaded into QuickBooks.

You can view the detailed connection properties by clicking the "connection" button in the Data menu:

In the Workbook Connection Window, click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, and you can view or edit data via SQL Statement.

Click the "SQL" button to view the SQL Statement of this query:

The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, following select Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name" and Operator as "equals," then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

When you finish the query, choose to save so that next time, you can directly use the same question:

Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Also, Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

Keywords: create odbc connection, QuickBooks Online, QBO, Excel

How to use QODBC with Microsoft Excel 2016 / 365 / 2013 / 2019 64-Bit (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)

QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.

QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.

Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to How to Configure QODBC to Work With 64-bit MS Excel.

Setup QODBC to use with Microsoft Excel

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.

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.

Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:

Office 2016:

Office 2013:

Office 2016:

Office 2013:

Create a blank workbook or can open an existing one. In this example, we are creating a new workbook:

An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "Get Data -> From Other Sources->From Microsoft Query."

It will take some time to get the DSN list:

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

The first time use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select the "Yes, always" option, and then click the "Continue" button; in the next window, click the "Done" button to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. In Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And once a table is set, it is the default that all its columns are selected. You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to put any questions, please click the "Next" button:

This window allows you to sort your data. If you don't want to change the order, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, you can view and edit these records, but the data is static. The modification will not be uploaded into QuickBooks.

You can view the detailed connection properties by clicking the "Queries & Connections" button in the Data menu:

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information: 

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, and you can view and edit data via SQL Statement there, and all modifications will be uploaded to QuickBooks.

Click the "SQL" button to view the SQL Statement of this query. The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, following selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

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:

     Clicking Data -> Refresh Data or clicking the button in the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download 

Also, Refer :
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016, Microsoft excel 2013 blank window

Setup QODBC to work with Microsoft Office 2007

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.

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

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.

First, please select the data input method via Data->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.).

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, always" option and click the "Continue" button. In the next window, click the "Done" button to finish authorizing.

Then you will be guided by Query Wizard. In Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And once a table is set, it is the default that all its columns are selected. You can add or remove columns from the correct list "Columns in your query."

The following screens allow you to filter and sort the data to be imported, and then you will reach the finish screen.

Now the Query Wizard is moving to the final step.

Return Data to Microsoft Office Excel will directly export the data to Excel, you can view and edit these records, but modifications will not be uploaded.

View data or edit query in Microsoft Query will go to the MS Query window, and you can view and edit data via SQL Statement there. All modifications will be uploaded to QuickBooks.

You could use SQL Statements to build your query. Press the "SQL" button at the top of the window to enter the SQL view.

The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, following selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, the worksheet specifies the criteria Field as "Name," Operator as "equals," then click the Value button, and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

Once you have done this, click File->Return Data to Microsoft Office Excel.

Then they will be displayed on your worksheet.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Also, Refer:

Using QuickBooks Data with Excel 2010 32-bit
Using QuickBooks Data with Excel 2013 32-bit
How to Configure QODBC to Work With 64-bit MS Excel

Creating a Table in Microsoft Access

Open MS Access 2007, and create a blank database.

Click on the "Create" button. Then you get a Microsoft Office Access blank database.

The newly created database shall have one table by default. You could extract QuickBooks data to this table or create another table to do this.

Build a connection to QuickBooks via External Data->More->ODBC Database

In the "Get External Data" Window, you will see two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table. This option will create a table just like the first option but will keep its link to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

We have selected a link to the data source in this example by creating a linked table.

Click the OK button. Then you will see the Select Data Source window.

Please Select "QuickBooks Data" or the one you may have created for your QuickBooks File.

Please make sure the DSN you choose is available. You can check it by pressing the "Test Connection" button in QODBC Setup Screen->General Window.

Once the connection is built, Access will return a list of available tables in the Select Tables window. Multiple Selections are available. Please, select the tables you want to extract.

After the extraction, you will notice a new table has been created. It can be viewed in the All Tables list.

Double-click the extracted table (In this example, it's table Customer). QODBC will connect to QuickBooks, read all records from QuickBooks, and show it in MS Access.

And you can also query tables using MS Access via Create->Query Design.

This will create a query (default name "Query1") in your database. Please add the table you want to query in the "Show Table" Window.

Here is the design view of a query:

And please click the View->SQL View option to check the SQL Statement of this query.

And this is the SQL View of a query. You can edit your query using SQL Statement.

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