Problem Description:

I need a list of all joins for QuickBooks tables

Solution:

QuickBooks tables are designed around two primary joining patterns: Header-to-Detail (linking a transaction to its line items) and Transaction-to-List (linking a transaction to its associated master records).

The joins are primarily made using unique QuickBooks identifiers: TxnID for transactions and ListID for master records (like Customers, Items, Accounts).

Transaction/Detail-to-List Joins (Linking to Master Records)

These joins link transaction or line item tables to List tables (e.g., Customer, Item, Account, Vendor) to retrieve descriptive names and details.

The convention is to join a RefListID field (found in the transaction or detail table) to the corresponding ListID field in the master table.

These “Relations” entries are embedded throughout each table page of the QODBC schema documentation. To view all joins comprehensively:

1. Visit https://doc.qodbc.com/qodbc/usa/

2. Select “Table List (USA)” under “Data Layouts”.

3. Each table entry includes “Relations / Jumpins / Detail”, describing its join paths and matching foreign keys.

For further precision, you can programmatically list joinable tables using QODBC’s internal stored procedure:

Running sp_columns on any table (for example, sp_columns InvoiceLine) shows which columns are keys that link to other tables — essentially revealing all possible join relationships.

Problem Description:

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

How do I cancel the subscription to the 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:

We have searched the wiki and the internet and tried endlessly to 'Insert an Employee.'

Below is our latest effort, all to no avail-

Insert into Employee (FirstName, LastName, EmployeeAddressAddr1, EmployeeAddressAddr2, EmployeeAddressState, EmployeeAddressPostalCode) Values ('Jake,' 'Wrestl,' '123 Main','456 stand', 'FL,' '12345')

I do not know what is wrong.

Can you shed some light on the error below, and 'More Simply' provide a Sample to 'Insert an Employee'?

Solutions: 

The solution is to include the field 'EmployeeAddressCity' in your SQL statement.

For Example:

Insert into Employee (FirstName, LastName, EmployeeAddressAddr1, EmployeeAddressAddr2, EmployeeAddressState, EmployeeAddressCity, EmployeeAddressPostalCode) Values ('Jake,' 'Wrestl,' '123 Main','456 stand', 'FL,' 'Coral,' '12345')

You can view the newly added Employee records in the QuickBooks application.

Go to QuickBooks, and you will see the Employee has been added.

In the "Address & Contact" tab, you will see the city information added in QuickBooks.

The "EmployeeAddressCity" field is not mandatory to use when inserting an Employee record, but there are specific fields that are conditionally required.

For Example:

If you specify FirstName, the Last name is optional. Either FirstName or LastName is required.

Tags: Employees, Employee insert

Problem Description:

What does 'Red text' in the QODBC driver dialogs mean

Solutions: 

Whenever you run any query for the first time, QODBC will first build the optimizer.

Please refer to the link below on how the optimizer works.

Refer to: How Optimizer Works

The Display Driver Status will show the current status in 'Red color,' which means data is fetched from the QuickBooks SDK instead of the QODBC optimizer.

Either you are fetching the data for the first time, or you might not be using QODBC Optimizer.

If the QODBC Display Driver Status is showing in 'Red color,' the data fetching process will be longer.

The reason is that QODBC has to fetch all the data from the QuickBooks SDK.

The time required to fetch data will depend on the number of records in your QuickBooks company file.

The data will be stored in an Optimizer file to increase the retrieval performance of queries.

From the second time onwards, you will notice the current status inside the QODBC Display Driver Status in 'Black color,' which means data is fetched from the QODBC Optimizer.

Tags: Optimizer, Red text, QODBC optimizer

Problem Description 1:

I want to apply the Payment to a particular Invoice using Deposits.

How can I do so using the QODBC?

Solutions: 

Refer to the screenshot of the QuickBooks UI Invoice created.

You can use this 'Invoice No.' or 'RefNumber' to search for a specific Invoice.

Note the ref number of the Invoice, i.e., '7777777-124.'

You can find this Invoice created using the QODBC Test Tool using the following SQL query:

SELECT * FROM InvoiceLine where RefNumber='7777777-124.'

Note the value of the field 'TxnID' - '8DF3-1481796954' from the 'InvoiceLine' table.

Use this value of the field 'TxnID' in the 'ReceivePaymentLine' table.

SELECT * FROM ReceivePaymentLine where AppliedToTxnTxnID='8DF3-1481796954'

The value in the field 'AppliedToTxnTxnID' from the ReceivePaymentLine table and 'TxnID' in the InvoiceLine table are equal.

i.e., ReceivePaymentLine.AppliedToTxnTxnID = InvoiceLine.TxnID

Note the value of the field 'TxnID' - '8DF7-1481797010' from the 'ReceivePaymentLine' table.

Use this value of the field 'TxnID' in the 'DepositLine' table.

SELECT * FROM DepositLine where DepositLineTxnID='8DF7-1481797010'

The value in the field 'TxnID' from the ReceivePaymentLine table and 'DepositLineTxnID' in the DepositLine table are equal.

i.e., DepositLine.DepositlineTxnID = ReceivePaymentLine.TxnID

Search for the field 'DepositLineCheckNumber' in the 'DepositLine' table.

In this example, the value is '20181225-0222'.

Search for this specific reference number in QuickBooks UI in 'Deposit.'

Below is the screenshot of the QuickBooks application of 'Customer Payment' or 'ReceivePayment.'

Tags: Link invoice to deposit, Deposit, Invoice

Note: The following example uses the QuickBooks USA Sample Rock Castle Construction company file.

To create a new Transfer Inventory record

    We have provided the tools to help you determine where field information comes from in QuickBooks Online, which tables contain the information you want, how tables can be related, and sample SQL statements. Please click here to check.

Using SP_COLUMNS in QODBC Online Test Tool

     You can also use the stored procedure command SP_COLUMNS table name in the QODBC Online Test Tool to get the EXACT table schema rules for the QuickBooks Company file you have open. The last few columns have QUERYABLE, UPDATEABLE, and INSERTABLE, and they will show you the related tables for each table in QODBC via a column named RELATES_TO.

     You need to run the query, such as "sp_columns Customer", in the QODBC Online Test Tool:

QODBC Stored Procedures Command-List

Click here to view a list of all Stored Procedure commands

How to use QuickBooks Reporting with QODBC Online

Click here for more information

Data Formats & Data Layouts

How QODBC Interfaces with QuickBooks

We have provided tools to help you determine where field information comes from in QuickBooks, which tables contain the information you want, how tables can be related, and sample SQL statements.

GENERAL TABLE INFORMATION: Designed to be your starting point for information research, these lists tell you the table name, how it is used, which other tables it can be related to, and a QuickBooks screenshot to demonstrate where the table information originates from.

DETAILED TABLE/FIELD INFORMATION: This tool shows you the details of each field in a table, how it is used, and its relation to other tables/fields (if any).

RELATIONSHIPS: We have provided one-click access to table/field relationships at both the table and the field levels.

REPORT DETAILS: For sp_reports (stored procedure reports), you can get information about columns and parameters for each report.

Click on the link below to view the tables and reports available in QODBC Online.
	QuickBooks Online

Keywords: Data Layouts, Data Format, Data Definition, Table Schema, Table Design, Tables & Fields, Table Field Types, Field Types, What does field type mean, QuickBooks Online, QODBC Online

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

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

$conn.open()

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

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

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

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

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

$conn.open()

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

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

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

$null = $da.fill($dt)

$conn.close()

$dt

Please refer to how to configure QRemote.

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

Also, Refer:

QODBC Reports Architecture Mismatch Problem When Connecting

Problem Description:

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

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

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

Any Assistance Is Appreciated.

Solution:

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

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

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

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

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

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

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

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

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

After creating a Linked Server, you need to create a 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 an 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 a 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 a 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 a Linked Server in SQL Server.

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

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

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

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

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

For Example:

SELECT * INTO SQL_Local_TableName FROM YourLinkedServerName...TableName;

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

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

SELECT * INTO Tbl_Account FROM QRemote...Account

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

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

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

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

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

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

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

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

Stored Procedure Created.

Stored Procedure Code:

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

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


Create PROCEDURE [dbo].[Import_QODBC_Tables]


AS
begin

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

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

SELECT * INTO    Tbl_AccountTaxLineInfo    FROM    QRemote...AccountTaxLineInfo

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

SELECT * INTO    Tbl_ARRefundCreditCard    FROM    QRemote...ARRefundCreditCard

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

SELECT * INTO    Tbl_ARRefundCreditCardRefundAppliedTo    FROM    QRemote...ARRefundCreditCardRefundAppliedTo

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

SELECT * INTO    Tbl_BarCode    FROM    QRemote...BarCode

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

SELECT * INTO    Tbl_Bill    FROM    QRemote...Bill

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

SELECT * INTO    Tbl_BillExpenseLine    FROM    QRemote...BillExpenseLine

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

SELECT * INTO    Tbl_BillItemLine    FROM    QRemote...BillItemLine

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

SELECT * INTO    Tbl_BillLinkedTxn    FROM    QRemote...BillLinkedTxn

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

SELECT * INTO    Tbl_BillingRate    FROM    QRemote...BillingRate

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

SELECT * INTO    Tbl_BillingRateLine    FROM    QRemote...BillingRateLine

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

SELECT * INTO    Tbl_BillPaymentCheck    FROM    QRemote...BillPaymentCheck

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

SELECT * INTO    Tbl_BillPaymentCheckLine    FROM    QRemote...BillPaymentCheckLine

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

SELECT * INTO    Tbl_BillPaymentCheckLineLinkedTxn    FROM    QRemote...BillPaymentCheckLineLinkedTxn

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

SELECT * INTO    Tbl_BillPaymentCreditCard    FROM    QRemote...BillPaymentCreditCard

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

SELECT * INTO    Tbl_BillPaymentCreditCardLine    FROM    QRemote...BillPaymentCreditCardLine

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

SELECT * INTO    Tbl_BillPaymentCreditCardLineLinkedTxn    FROM    QRemote...BillPaymentCreditCardLineLinkedTxn

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

SELECT * INTO    Tbl_BillToPay    FROM    QRemote...BillToPay

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

SELECT * INTO    Tbl_BuildAssembly    FROM    QRemote...BuildAssembly

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

SELECT * INTO    Tbl_BuildAssemblyComponentItemLine    FROM    QRemote...BuildAssemblyComponentItemLine

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

SELECT * INTO    Tbl_Charge    FROM    QRemote...Charge

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

SELECT * INTO    Tbl_ChargeLinkedTxn    FROM    QRemote...ChargeLinkedTxn

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

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

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

SELECT * INTO    Tbl_CheckApplyCheckToTxn    FROM    QRemote...CheckApplyCheckToTxn

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

SELECT * INTO    Tbl_CheckExpenseLine    FROM    QRemote...CheckExpenseLine

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

SELECT * INTO    Tbl_CheckItemLine    FROM    QRemote...CheckItemLine

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

SELECT * INTO    Tbl_Class    FROM    QRemote...Class

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

SELECT * INTO    Tbl_ClearedStatus    FROM    QRemote...ClearedStatus

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

SELECT * INTO    Tbl_Company    FROM    QRemote...Company

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

SELECT * INTO    Tbl_CompanyActivity    FROM    QRemote...CompanyActivity

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

SELECT * INTO    Tbl_CreditCardCharge    FROM    QRemote...CreditCardCharge

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

SELECT * INTO    Tbl_CreditCardChargeExpenseLine    FROM    QRemote...CreditCardChargeExpenseLine

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

SELECT * INTO    Tbl_CreditCardChargeItemLine    FROM    QRemote...CreditCardChargeItemLine

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

SELECT * INTO    Tbl_CreditCardCredit    FROM    QRemote...CreditCardCredit

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

SELECT * INTO    Tbl_CreditCardCreditExpenseLine    FROM    QRemote...CreditCardCreditExpenseLine

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

SELECT * INTO    Tbl_CreditCardCreditItemLine    FROM    QRemote...CreditCardCreditItemLine

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

SELECT * INTO    Tbl_CreditMemo    FROM    QRemote...CreditMemo

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

SELECT * INTO    Tbl_CreditMemoLine    FROM    QRemote...CreditMemoLine

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

SELECT * INTO    Tbl_CreditMemoLinkedTxn    FROM    QRemote...CreditMemoLinkedTxn

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

SELECT * INTO    Tbl_Currency    FROM    QRemote...Currency

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

SELECT * INTO    Tbl_Customer    FROM    QRemote...Customer

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

SELECT * INTO    Tbl_CustomerAddtionalNote    FROM    QRemote...CustomerAddtionalNote

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

SELECT * INTO    Tbl_CustomerContacts    FROM    QRemote...CustomerContacts

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

SELECT * INTO    Tbl_CustomerShipToAddress    FROM    QRemote...CustomerShipToAddress

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

SELECT * INTO    Tbl_CustomerMsg    FROM    QRemote...CustomerMsg

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

SELECT * INTO    Tbl_CustomerType    FROM    QRemote...CustomerType

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

SELECT * INTO    Tbl_CustomField    FROM    QRemote...CustomField

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

SELECT * INTO    Tbl_DateDrivenTerms    FROM    QRemote...DateDrivenTerms

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

SELECT * INTO    Tbl_Deposit    FROM    QRemote...Deposit

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

SELECT * INTO    Tbl_DepositLine    FROM    QRemote...DepositLine

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

SELECT * INTO    Tbl_Employee    FROM    QRemote...Employee

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

SELECT * INTO    Tbl_EmployeeAddtionalNote    FROM    QRemote...EmployeeAddtionalNote

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

SELECT * INTO    Tbl_EmployeeEarning    FROM    QRemote...EmployeeEarning

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

SELECT * INTO    Tbl_Entity    FROM    QRemote...Entity

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

SELECT * INTO    Tbl_EntityAdditionalNote    FROM    QRemote...EntityAdditionalNote

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

SELECT * INTO    Tbl_EntityContacts    FROM    QRemote...EntityContacts

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

SELECT * INTO    Tbl_EntityShipToAddress    FROM    QRemote...EntityShipToAddress

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

SELECT * INTO    Tbl_Estimate    FROM    QRemote...Estimate

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

SELECT * INTO    Tbl_EstimateLine    FROM    QRemote...EstimateLine

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

end

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

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

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

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

Now we will add this stored procedure to SQL Agent.

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

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

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

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

Job step added.

The job is added to SQL Server Agent.

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

The job is executing.

Download Sample

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

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

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

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

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

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

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

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

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



Private Sub CommandButton1_Click()

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

RowCount = 0
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"
sSQL = "SELECT * FROM InvoiceLine"
Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic
Set sh = ActiveSheet
For Each rw In sh.Rows

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

End If

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

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

End If

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

rs. Close
oConnection.Close

End Sub

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

Invoice Added.

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

Problem Description:

I am getting an error SOCKET Closed because of an abnormal package in the QRemote client. I am accessing QuickBooks Data from the remote machine over the internet (WAN). 

Solution:

This issue will occur when the QRemote client & QRemote Server have different network types.The 

Windows component is internally checking the connection every 4 minutes. If there is no activity between the client & server, the Windows component will close the connection after 4 minutes & due to this, when the QRemote client tries to connect to the QRemote Server, it will not be able to connect, & SOCKET is Closed because an abnormal package error occurred.

To resolve this issue, you need to set the AutoClose value to 3 minutes for closing the broken connection in QRemote Server.

You can find the QRemote Server running in the System Tray. If QRemote Server is not running, then you can run it from Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC.



Open QRemote Server. Go to File Menu -> Settings -> Advanced Tab. Set AutoClose broken connection after 3 minutes & apply the setting by clicking the Apply button.



After applying the settings. Please restart the QRemote Server & test again. To continue QRemote Server, go to File Menu -> Exit -> :

You will get a pop-up window with the message. Do you want to close the QRemote Server? Please click on OK to close QRemoteServer.

Please rerun QRemoteServer & test. You can QRemoteServer from:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

Problem Description:

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

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



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

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

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

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

Solution:

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

OptimizerAllowDirtyReads=N

For Example:

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

After changing this, you can get the correct result.

Problem Description:

We are using QRemote with SQL Linked Servers with several different QuickBooks files. How can we close the connection to a QuickBooks file after the query has run, then open it to connect to another database?  

Solution:

SQL Server Linked Server automatically handles disconnections.

SQL Server Linked Server does not have a disconnection command that can be fired/executed.

After executing the query, you will have to wait 30-45 seconds. SQL Server will automatically send the disconnect command if there is no activity on the linked server connection.

Problem Description:

Has anyone run a report that will pull all the details for a range of invoices? In particular, I'm looking to run a report that shows the ship-to addresses for specific products over a date range. 

Solution:

Please use the query below to get the ship-to addresses for particular products over a date range.

Select InvoiceLineItemRefFullName, RefNumber, TxnDate, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressAddr5, ShipAddressCity, ShipAddressState, ShipAddressPostalCode, ShipAddressNote from InvoiceLine where TxnDate >= {d'2009-01-01'} and TxnDate <= {d'2009-03-01'} and (InvoiceLineItemRefFullName = 'Cabinets' or InvoiceLineItemRefFullName = 'Window') order by InvoiceLineItemRefFullName, RefNumber, TxnDate.

Problem Description:

I have an MS Access application using QODBC to connect to desktop QuickBooks. Can I use the QODBC Online driver to connect to QuickBooks Online without changing my MS Access code?  

Solution:

QODBC Online is a different product from the QODBC Desktop driver. So you cannot use the QODBC Desktop driver with QuickBooks Online. It would be best if you used the QODBC Online driver to connect to QuickBooks Online.

Most of the Tables, table columns, reports, report columns & report parameters in QuickBooks Desktop & Online are the same. But not all the tables, reports & fields available in QuickBooks desktop are available in QuickBooks Online. You might have to change a few areas to match QuickBooks Online. You need to refer to QODBC Online reports/tables/fields & make changes in your code.

Please refer Difference between QODBC for QuickBooks Desktop and Online & make necessary changes in your code & use the code with QODBC Online.

Tags: QuickBooks Online, QBO, code, QODBC Online, QODBC Desktop

Download Sample

ADODB with QODBC

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

Create a New Standard EXE project.

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

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

Private Sub Command1_Click()

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

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

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

End Sub

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

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

ADODC with QODBC

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

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

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

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

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

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

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

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

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

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

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

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

The first step is to install and test QODBC.

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

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

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

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

Please follow the steps below to use QODBC with SSIS.

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

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

The Project is created.

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

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

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

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

Add a new Data connection by clicking on New.

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

Test Connection Succeeded.

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

You can preview output data by clicking the Preview button.

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

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

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

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

Add a new Flat File connection by clicking on New.

Select Flat File Format & click OK.

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

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

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

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

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

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

Please click here to download the Sample SSIS Package.

Problem Description:

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

Unknown Data type found in column

Solution:

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

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

using namespace std;

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

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

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

SQLRETURN retcode;
int iKey;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

Please click here to download the C++ code.

Problem Description:

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

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

Solution:

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

Something along the lines of:

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

Problem Description:

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

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

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

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

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

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

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

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

Any help with this would be greatly appreciated! 

Solution:

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

Please click here to get the latest QODBC version.

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

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

Set up 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 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 sure 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 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 the 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 the 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, where you can view and edit these records, but the data is static. The modification will not be uploaded to 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, followed by 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 the 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,n 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 a linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, 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 created in your MS Access file under "Tables":

You can view the properties 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, and 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 into 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:

I am trying to connect to QuickBooks/QuickBooks POS using QRemote Server, but QRemoteServer is Having on Connection.

Solution:

Please check if QuickBooks or QuickBooks POS has a Certificate Popup.

In the QuickBooks POS certificate, pop-up windows go in the background, so you need to check the pop-up window in the background & accept the certificate.

OR

Try Restarting QRemoteServer.
Close/Exit QRemoteServer. You can find the QRemote Server running in the System Tray.

Go to File Menu -> Exit -> :

Do you want to close the QRemote Server? You will get a pop-up window with the message. Please click "OK" to close QRemoteServer.

Please rerun QRemoteServer & test. You can QRemoteServer from:
Start>> All Programs >> QODBC Driver for use with QuickBooks POS >> QRemote Server for QODBC POS

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

OR

Remove QODBC POS/QODBC-related certificates from QuickBooks POS/QuickBooks.

Please refer to How to remove QODBC Related Certificates from Integrated Application Under QuickBooks POS for more details.

For more details, please refer to How to remove QODBC Related Certificates from Integrated Application Under QuickBooks.

Restart QRemoteServer using the above steps, connect again,n and accept the certificate.

Problem Description:

How do I update Customer custom fields? 

Solution:

You can update Customer custom fields using the update query.

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

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

You can check it on QuickBooks, also.

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

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

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

You can check it on QuickBooks, also.

Problem Description 1:

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

Problem Description 2:

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

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

We are attempting to import four tables for now.

- Account, Class, Entity, Transactions

The reported error below occurs for each table.

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

Solution:

Please follow the steps below to resolve this error:

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

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

3. Choose Destination and click on "Next."

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

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

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

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

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

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

8. Click on "next" until the finish.

Save and Run Package.

Complete the Wizard.

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

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

Problem Description:

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

Problem Description:

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

Solution:

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

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

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

Or Raise a Support Ticket

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

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

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

Please refer to QODBC Data Layout for more details.

Problem Description:

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

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

Is the use of QODBC correct? 

Solution:

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

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

Problem Description:

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

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

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

Solution:

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

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

Problem Description:

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

Solution:

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

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

Problem Description:

I am using the following SQL statement:

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

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

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

Solution:

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

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

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

Problem Description:

I've explored the related fields, but cannot determine how a line on the invoice is linked to the estimate.

I'm designing a report that needs to see the item on the estimate and how much has been billed out and retained for each item (as well as other related data). 

Solution:

Please refer to How to convert a Sales Order into an Invoice for the basics of how an Estimate can be made into a Sales Order and an Invoice.

The InvoiceLinkedTxn table shows Payments, Credit Memos, and Deposit Line Items, but doesn't include reimbursements, transfers from Sales Orders, or Estimates (depending on your QuickBooks version). But in QuickBooks, you probably understand it better as the History of an Invoice. The number of InvoiceLinkedTxn lines to Invoice lines won't match where multiple payments or deposits have been made.

To better understand, refer to: How to use InvoiceLinkedTxn query using QODBC.

While the EstimateLinkedTxn table shows all the transfers to Invoice or Sales Order; but does NOT include any transfers to PO.

Problem Description:

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

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

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

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

Solution:

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

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



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

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

Problem Description:

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

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

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

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

Can I do this? 

Solution:

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

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

Problem Description:

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

Solution:

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

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

Where:

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

Problem Description:

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

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

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

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

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

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

Thanks in advance for any help.

Solution:

You can get these details from the PayrollTransactionDetail report:

For this example, PayCheck:



You can find all the details using the following:

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

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

Problem Description 1:

I am using QuickBooks Enterprise with Advanced Inventory enabled.

I am trying to find where the bin location for items is stored in QuickBooks through QODBC.

Problem Description 2:

We are looking for the following tables in QuickBooks Enterprise Edition 16 via QODBC (Linked Server) SQL server, with no luck:

We can find the table for the Inventory Site, but there is no distinction for BIN. They are all listed equally in the table. (tbl = InventorySites)

Solution:

You can find the inventory site list & location below the tables:

BillItemLine, PurchaseOrderLine, InvoiceLine, SalesOrderLine, CheckItemLine, CreditCardCreditItemLine, CreditMemoLine, SalesReceiptLine, CheckItemLine, CreditCardChargeItemLine, BuildAssembly, BuildAssemblyComponentItemLine, TransferInventoryLine, InventoryAdjustment, InventoryAdjustmentLine & ItemSites.

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

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

To change, please follow the steps below:

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

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

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

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

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

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

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

Please select Power Query Window.

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

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

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

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

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

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

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

For Excel 64-Bit:

http://qodbc.com/links/2771

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

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

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

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

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

Tags: The driver does not support this parameter.

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

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

1) Using Wizard

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

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

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

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

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

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

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

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

Click Finish to complete the import process.

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

Or

2) Using VBA Code

Please refer to Using QuickBooks Data with VBA

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

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

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

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

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

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

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

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

Import Customer:

Option Compare Database

Public Sub exampleCsvImportCustomer()

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

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

sSQL = "SELECT * FROM customer"

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

Set fso = CreateObject("Scripting.FileSystemObject")

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

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

Loop

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

End Sub

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

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

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

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

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

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

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

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

Import Invoice:

Option Compare Database

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

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

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

sSQL = "SELECT * FROM InvoiceLine"

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

Set fso = CreateObject("Scripting.FileSystemObject")

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

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

Loop

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

End Sub

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

Download Sample

Also, Refer:

QODBC Reports Architecture Mismatch Problem When connecting

Information:

QuickBooks allows users to put foreign (i.e., Chinese, Japanese, etc) characters into item descriptions, etc. Unfortunately, these characters are not appropriately returned in QBXML responses (and, therefore, QODBC responses since they're based on Intuit's QBXML SDK)

The problem is two-fold. The Intuit code wasn't written to handle Unicode characters at the SDK level, so any characters QuickBooks returns to the SDK level aren't appropriately interpreted. Secondly, the parser used by the SDK runtime in QuickBooks doesn't support some of the character sets for foreign characters, so even if the underlying SDK runtime code handled Unicode characters, the SDK parser wouldn't. That means one cannot pass foreign characters into QuickBooks through the SDK to QODBC.

Problem Description:

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

How to convert the date to a QuickBooks date.

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

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

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

I am looking for your early reply with thanks. 

Solution:

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



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





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

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

Problem Description:

The following selection gives me the correct data using DISTINCT, but how do I get a sum of it, which is what I need?

Select distinct billtopayduedate, billtopayrefnumber, and bill.vendorreffullname, billtopayamountdue, assetaccountreffullname from bill nosync, bill pay nosync, billitemline nosync, item nosync where account to pay. billtopaytxnid = bill. tuned and bill.ispaid=false and bill. txnid = billitemline. txnid and BillItemLine.ItemLineItemRefListID = Item.ListID and assetaccountreffullname like '%inventory%.'

I want my SELECT statement to return one number: a sum of all the records in "Bill to Pay" where one or more corresponding line items in the expense line or item line are for inventory.

Solution:

In SQL, we do sum(column) and group by, for example:

SELECT Employee.PayrollInfoClassRefFullName, sum(Transaction.Amount) as Amount FROM Transaction, Employee where Transaction.EntityRefListID=Employee.ListID and Transaction.TxnType='Paycheck' and (Transaction.TxnDate >= {d'2017-01-01'} and Transaction.TxnDate < {d'2018-01-01'}) Group by Employee.PayrollInfoClassRefFullName

To total all the BillToPay amounts, you do the following:

Select sum(BillToPayAmountDue) as OpenAPBalance from BillToPay.

To total all the BillToPay amounts involving stock (inventory) only and not expenses, you do the following:

Select sum(BillToPayAmountDue) as OpenInvtBalance from BillToPay, BillItemLine where BillToPay.BillToPayTxnID = BillItemLine.TxnID and BillItemLine.ItemLineSeqNo = 1

Problem Description:

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

Solution:

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

For Example:

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



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

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

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

Problem Description:

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

Solution:

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

For Example:

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



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

Problem Description:

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

DELETE FROM Invoice WHERE TxnID = 'xxxx-xxxxxxxxx'

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

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

Solution:

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

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

Where 'YourRefNumber' is the Reference to the Invoice.

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

sp_optimizefullsync InvoiceLine

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

Solution:

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

For Example:

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

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

Or

You can go straight to the BillToPay table like this.

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

Problem Description:

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

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

Solution:

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

For Example:

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

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

Problem Description:

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

Solution:

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

For Example:

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

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

Problem Description:

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

sp_columns BillExpenseLine

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

However, the following insert failed:-

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

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

Solution:

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

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

Statement 1

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

Statement 2

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

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

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

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

Create an ItemGroup with Three Lines

Note: Let's create a new item group example with three lines using QODBC. Note the FQSaveToCache field, set to True except on the last one.

First Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 1', 1, 'Test Item Dec, 1, '8000009D-1481800631', 1.0, 1)

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

Second Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 2', 1, 'Test Item Dec, 1, '670004-1044572237', 2.0, 1)

Third (Last) Line

INSERT INTO "ItemGroupLine" ("Name," "IsActive," "ItemDesc," "IsPrintItemsInGroup," "ItemGroupLineItemRefListID," "ItemGroupLineQuantity," "FQSaveToCache") VALUES ('Test1 W3 Lines 3', 1, 'Test Item Dec, 1, '6D0003-1044572301', 3.0, 0)

Result in QuickBooks

The result looks like this in QuickBooks 2022:

SELECT ListID, Name, ItemDesc, IsActive FROM ItemGroup

Invoice the ItemGroup

Note: We can now create a PurchaseOrder using the item group by utilizing the ListID:'8000009F-1481791619' and making one simple QODBC insert statement below.

INSERT INTO "PurchaseOrderLine" ("VendorRefListID," "RefNumber," "PurchaseOrderLineGroupItemGroupRefListID," "FQSaveToCache") VALUES ('390000-1039739488', 'Group2', '8000009F-1481791619', 0)

Result in QuickBooks

When we look at the new invoice in QuickBooks 2013, we will see the following:

Note: Qty, Rate, and the Amount are shown as per Qty, Rate, and the Amount for the service or stock item

Problem Description:

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

What QODBC SQL Statement would I use?

This is what I have so far:

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

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

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

Solution:

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

You can run this query:

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



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


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

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

Problem Description:

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

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

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

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

Solution:

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

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

For Example:

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

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

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

Problem Description:

I 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 the 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 has opened 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, 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:

Does your enterprise driver support the advanced inventory module for QuickBooks Enterprise? Would we be able to query stock per item, per location? 

Solution:

You can access the Advanced Inventory data from the QuickBooks enterprise add-on.

Yes, the site and location field is not available in ItemInventory & other Item tables through the Intuit SDK.

You can find the inventory site list & location below the tables:

BillItemLine, PurchaseOrderLine, InvoiceLine, SalesOrderLine, CheckItemLine, CreditCardCreditItemLine, CreditMemoLine, SalesReceiptLine, CheckItemLine, CreditCardChargeItemLine, BuildAssembly, BuildAssemblyComponentItemLine, TransferInventory, TransferInventoryLine, InventoryAdjustment, InventoryAdjustmentLine, ItemSite

Problem Description:

I am evaluating the product. When I made this SQL Statement:

Select * from Customer where ListId=557.

I was getting a column not found error. The QBO online documentation says the Customer table has a field called "Id," but this also comes back as "column not found."

Can you let me know if this unique ID column is being renamed or something?

Solution:

ListID column data type is varchar, so you need to add quotes (i.e.,') around it. Please use the query below & try again.

select * from Customer where ListId='557'

Regarding ID, I think you might have tried

select * from Customer where ID='557'

ID is not part of the QODBC-Online Schema. Please use the listed.

You can get the QODBC Online table schema from the link below:

Please refer to QODBC Online Data Layout for more details:

Tags: QuickBooks Online, QBO, QODBC Online, Customer

Problem Description 1:

I have searched many of the tables connectable through the QODBC. I cannot find customer list information, such as the customer's website, Facebook, LinkedIn, multiple phone numbers, alternative emails, etc.

Problem Description 2:

I'm trying to find the field that keeps track of attachments for a particular item. I can see the physical files are stored on the file system, but I'm looking for the reference to those files. 

Solution:

I'm sorry to inform you that the field website, Facebook, LinkedIn, multiple phone numbers, alternative emails, and attachment references for a particular item are unavailable through the Intuit SDK. Hence, they are not available through QODBC.

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

QODBC will, of course, support it when this feature is available through the SDK. Please get in touch with Intuit with your request.

As a workaround, you can export the same data from the QuickBooks UI export feature & check if QuickBooks UI is providing that field.

You can export the data from QuickBooks UI, get the rest of the information from QODBC, integrate the knowledge, and generate the report/application you are looking for.

For Example:

QuickBooks Desktop:

Exporting Customer Data to Excel:

Navigate to Customer Center from QuickBooks UI & click on the Excel drop-down, and click on Export Customer list...

Please check the Excel file & look for the fields/data you want.

QuickBooks Online:

You can export data from QuickBooks Online Web UI, also.

Exporting Customer Data to Excel:

Click on customer & click on export to Excel icon.

The customer list will be downloaded in an Excel file. Please check the Excel file & look for the fields/data you want.

Problem Description 3:

We are considering setting up the ODBC to allow us to customize a specific report. Before we do, we wanted to ensure that the ODBC database will allow us to filter by the "end date" field under customers/jobs. We need to create a P&L by Job report and filter on a range of end dates. I could not see that we could access this field.

When I look at the links on QODBC, there are some with listings of fields. None of the areas includes "End Date." We need to be able to run a P&L by Job report that filters on job date ended (only have jobs that were completed within a date range). Note: The end date is not the same as the transaction date filter; instead, it is located under the customer center, then job info. Is the end date (not the transaction date) available to filter results?

Solution:

No parameter Job End Date is available from the QuickBooks SDK for the ProfitAndLossByJob report.

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

You can find JobEndDate from the Customer table & export report data & table data in an Excel sheet & you can merge report data & Customer table data and build reports per your requirement. 

First, you need to export the report to an MS Excel sheet. 

For Example:

sp_report ProfitAndLossByJob show Amount_Title, Text, Label, Amount parameters DateFrom={d'2016-01-01'}, DateTo={d'2016-08-30'}

Please refer to the link below for exporting sp_report in MS Excel:
How to create sp_reports using Microsoft Excel

After that, you must export the Customer table data to another Excel sheet.

Please refer to the link mentioned below for exporting QuickBooks data in MS Excel:
How to Use QODBC with MS Excel 2016 / 365 / 2013 32-Bit

After that, you need to add the new column in sheet 1 to show JobEndDate data from sheet 2. By using the Excel lookup formula, you can get JobEndDate in sp_report.

How to contact Intuit: http://qodbc.com/links/3058

Tags: QuickBooks Online, QBO, Missing columns from QuickBooks Online, missing data in QuickBooks Online export.

Problem Description:

I get a generic error when using QODBC to create an invoice.

I am using the following query:

INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, '', 1)

INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80003194-1404606055', 'My item 2',22.50, 1, '', 1)

INSERT INTO Invoice ("TemplateRefListID," "CustomerRefListID," "CustomerRefFullName," "BillAddressAddr1", "BillAddressAddr2", "BillAddressCity," "BillAddressState," "BillAddressPostalCode," "ShipAddressAddr1", "ShipAddressAddr2", "ShipAddressCity," "ShipAddressState," "ShipAddressPostalCode," "SalesRepRefFullName," "ShipMethodRefListID") VALUES ('80000011-1203804797', '80000440-1448981709', 'FAKEEBAY,' 'Someone,' 'Address 1 ', 'The city,' 'State,' 'Zip,' 'Someone,' 'Address 1', 'City,' 'State,' 'Zip,' 'Rep,' '80000004-1202486852')

The error is:

[QODBC] Error: 3000 - The given object ID "in the field "list id" is invalid.

Solution:

You are trying to insert a blank value for the field "InvoiceLineSalesTaxCodeRefListID."

To resolve this issue, insert a valid value for the InvoiceLineSalesTaxCodeRefListID field or remove the InvoiceLineSalesTaxCodeRefListID field from your insert statement.

Remove the LISTED field from the query. Example:
INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, 1)


Specify a value for LISTED field Example:
INSERT INTO InvoiceLine ("TemplateRefListID," "InvoiceLineItemRefListID," "InvoiceLineDesc," "InvoiceLineAmount," "InvoiceLineQuantity," "InvoiceLineSalesTaxCodeRefListID," "FQSaveToCache") VALUES ('80000011-1203804797', '80002C72-1345040417', 'My item 1',19.95, 1, '90000011-1234567800', 1)


So please insert a valid value in the InvoiceLineSalesTaxCodeRefListID field or remove the InvoiceLineSalesTaxCodeRefListID field from your insert query.

Table Differences:

QODBC for QuickBooks Desktop has 150 tables while QODBC for QuickBooks Online has 83 tables. 

Table in QODBC for QuickBooks Desktop But not in QODBC for QuickBooks Online:

Table in QODBC for QuickBooks Online But not in QODBC for QuickBooks Desktop:

Field in QODBC for QuickBooks Desktop But not in QODBC for QuickBooks Online: