Problem Description:

I want to get copies of invoices.

Solution:

Proforma Invoice

Please visit the following link: https://flexquarters.onfastspring.com/account.

Input the email address associated with your license.

Check your email inbox, and you should receive an email with the subject:

<Your Fullname>, here is a link to manage your orders

From: FastSpring Checkout <mailer@fastspring.com>

Click the link received to manage your account.

The link (URL) should look like https://flexquarters.onfastspring.com/account/somerandomtext.

Click the Manage icon and then click Proforma Invoice.

The Proforma invoice will be generated and will be opened in a new tab.

Please ensure you click Allow pop-up so that a new tab can be opened.

Past Invocies:

Please check your email inbox for Invoices received from FastSpring.

If you need any specific Invoice that you are unable to find in your mailbox, please email us the following details.

Your Order ID:  or QODBC CDKEY:

Product Name:

Invoice Month:

Please raise a support ticket and submit the requested information.

https://support.flexquarters.com/esupport/index.php?/Tickets/Submit

Tags: QuickBooks Online, QBO, Subscription QODBC Online, Cancel subscription

How FQSaveToCache Works

The process involves a sequence of INSERT statements for the line items of a transaction, with the FQSaveToCache flag controlling when the data is sent to QuickBooks.²

1. Caching the Lines (FQSaveToCache = 1 or TRUE)

• For the first line item INSERT statement, and all subsequent intermediate line items, you must set the FQSaveToCache value to 1 OR TRUE

  • Action: When QODBC sees this value, it takes the data from the INSERT statement and stores it locally in a cache specific to your current QODBC connection.

  • Result: The data is not yet written to the QuickBooks company file.

• Example (Conceptual):

  SQL

   

  INSERT INTO "InvoiceLine" (...) VALUES (..., 1) -- First line, cache it
  INSERT INTO "InvoiceLine" (...) VALUES (..., 1) -- Second line, cache it

2. Finalizing and Writing the Transaction ( FQSaveToCache = 0 or FALSE)

• For the last line item INSERT statement for that transaction, you must set the FQSaveToCache value to 0 OR FALSE. If you do not include FQSaveToCache field and value in the insert statement, it will be considered as FQSaveToCache =0

  • Action: When QODBC sees this value, it performs two main steps:

    1. It combines the data from this final INSERT statement with all the previous cached line items for the same connection.

    2. It sends this complete, combined transaction (header and all lines) as a single batch to QuickBooks via the QuickBooks SDK.

  • Result: The complete transaction is now permanently saved in your QuickBooks company file.

• Example (Conceptual):

  SQL

   

  INSERT INTO "InvoiceLine" (...) VALUES (..., 0) -- Final line, write all cached lines + this line to QuickBooks
  

I am getting the following error trying to insert a record in Invoice:

[QODBC] Not supported - At least one Line record is required. Please insert Child/Detail record(s) before inserting Parent/Header record. (#10074)

Solution:

How to Fix the Error
To resolve this issue, you must change the order of your data insertion or SQL statements to ensure the required detail exists before the header.

Insert the Detail Records First: Before you attempt to insert data into the Parent/Header table (e.g., Invoice), you must successfully insert at least one corresponding record into the Child/Detail/Line table (e.g., InvoiceLine).

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ( '320000-1071525597', 'POWERTRAK-2000', 200.00000, 200.00, '20000-999022286',1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-3000', 1)

INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache) VALUES ('POWERTRAK-4000', 0)

Insert the header record: 

INSERT INTO Invoice (CustomerRefListID, RefNumber, FQSaveToCache) VALUES ('670000-1071517519', '71099',0)

Insert the Header Record Last: Once the detail record(s) are in place, you can then insert the Parent/Header record, completing the transaction.

MS Access:

If you are using MS Access application, the connection string or the connection object is getting closed after you insert the Invoice Line and before you can insert the Invoice (Header) record.
This could be because your linked tables have different connection strings. You can delete the linked table and re-add the linked tables.

If you are using ADO/DOA objects, please ensure you do not close the connection between the insert of InvoiceLine and the Invoice table.

Download Sample

Sample Code for using QODBC DSN:

# Array of ListIDs to be updated

$listIDs = @(
"20",
"21",
"22"
)

# Define the DSN name
#$dsnName = "QuickBooks Data"
#$dsnName = "QuickBooks Online Data"

# Add your DSN name here
$dsnName = "Your DSN Name"

Write-Output "Connecting to $dsnName"

# Build connection string
$connStr = "DSN=$dsnName;"

# Create a new ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = $connStr

try {
# Open the connection
$conn.Open()
Write-Output "Connected to DSN: $dsnName"

Write-Output "Updating records."
foreach ($listID in $listIDs) {
#Prepare the SQL statement
# Either use ifnull or use the 2nd SQL statement
$sql = @"
UPDATE Account SET Description = {fn IFNULL(Description, '')} + '.' WHERE ListID = '$listID'
"@

# Prepare the SQL statement
$sql = @"
UPDATE Account SET Description = Description + '.' WHERE ListID = '$listID'
"@

Write-Output "SQL statement: $sql"
# Create command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql

try {
# Execute the update
$rowsAffected = $cmd.ExecuteNonQuery()
Write-Output "Updated ListID: $listID ($rowsAffected rows affected)"
} catch {
Write-Error "Error updating ListID: $listID - $_"
}
}
}
catch {
Write-Error "Could not connect to ODBC DSN: $dsnName - $_"
}
finally {
# Close the connection
if ($conn.State -eq 'Open') {
$conn.Close()
Write-Output "Connection closed."
}
}

Script execution & Result:

For 32-Bit Driver

Open Windows PowerShell (x86) from the Start menu:

For 64-Bit Driver

Open Windows PowerShell (x64) from the Start menu:

or

Open Windows PowerShell from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Export the QODBC Online connection key from your computer

Click here for the detailed steps.

Deactivate QODBC Online from your computer

Click here for the detailed steps.

Once you have deactivated QODBC Online from one computer, you can switch to another or a new computer and activate the same CDKEY.

Activate QODBC Online on your new computer

Click here for the detailed steps.

Import the QODBC Online connection key from your computer

Click here for the detailed steps.

QODBC is licensed to each computer. When you purchase a single-user license, you are authorized to use the product on one computer only. If you need to run two computers, you need to purchase another copy.  For contact information, click here.

Keywords
How do I move the license from one server to another?

Swapping the QODBC driver to another machine

Also refer to: QODBC Licensing Information.

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

Please refer to the detailed information on QODBC Licensing below.

1) Web Server applications accessing QuickBooks remotely with client browsers:

Machine A - QuickBooks, QODBC, QRemote Server, and QuickBooks company file
Machine B - QRemote Client, Web Server, and Web Browsers
Machine C, D, E – Web Browsers

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

2) Web Server applications accessing QuickBooks remotely with client applications – QuickBooks company file on a different machine:

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

Machine A - QuickBooks, QODBC, QRemote Server
Machine B - QRemote Client, Web Server, and Web Browsers
Machine C – QuickBooks company file
Machine D, E, F – Web Browsers

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

Keywords: QRemote Configuration, QRemote License information, how does Citrix work diagram 

Also refer to: QODBC Licensing Information

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

Please refer to the detailed information on QODBC Licensing below.

1) Power BI Gateway application accessing QuickBooks remotely:

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

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

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

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

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

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

3) Multiple Power BI Gateway applications accessing QuickBooks remotely:

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

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

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

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

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

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

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

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

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

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

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

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

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

 Question

I am facing the following issue with the Power BI Gateway.

Begin Session Failed. S0000 00000 [QODBC] QuickBooks Begin Session Failed. Error = 80040408, Could not start QuickBooks.

Begin Session errors can be caused when running SQL Server, Web Server, or other service based applications and not using a QODBC Server Edition license and QRemote

Answer:

Power BI Gateway is a service-based application. You will need a QODBC Server Edition license to use QODBC and QRemote with Power BI Gateway.

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

Problem Description:

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

Solution:

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

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

Prerequisites:

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

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

Step-by-Step Instructions to enable Microsoft Query:

Step 1: Enable Legacy Data Import Wizards.

• Open Microsoft Excel.

• Click on the File tab.

• Click on Options at the bottom-left corner.

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

• Scroll down to the Legacy Data Import Wizards section.

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

•  Click OK to apply changes.

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

Step 2: Access Microsoft Query (Legacy)

After restarting Excel:

• Go to the Data tab.

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

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

Tags: "Microsoft Query" is not a data option. Microsoft Query is missing. How to enable Microsoft Query and show Microsoft Query.

Problem Description:

I would like to use the new QuickBooks integrator module from Bookkeeper to allow me to import data directly into Bookkeeper.

Solution:

1. Create a New Company in Bookkeeper

• Launch Bookkeeper and create a new company (this will be the target for the QuickBooks data).

• Choose the standard setup.

• Enter basic company info (it will be overwritten during import).

• Set:

  • Accounting method (irrelevant, will be replaced).

  • Next fiscal year-end.

  • Advanced Payroll (ensure this is selected).

• Set a password.

• Select “Import from QuickBooks” and click Import.

 Please ensure you have downloaded and installed the "QuickBooks Integrator tool."

2. Prepare the Bookkeeper Database for Integration

• Click “Prepare Integration” in Bookkeeper.

• Ensure you have taken a backup of your Bookkeeper database.

• The bookkeeper will copy the integration tool to the Integration folder and will shut down the Bookkeeper application.

• This will copy the database to a staging area for the integrator module.

3. Open QuickBooks

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

• Important: Do not run QuickBooks as an administrator.

   

4. Open the QuickBooks Integrator Module

• Double-click on the QuickBooks Integration tool from the Desktop

• Important: Do not run QuickBooks as an administrator.

• Click the "Connect" button.

• When prompted by QuickBooks, allow access and confirm the connection.

• You should see the QuickBooks company name.

5. Connect to Bookkeeper

• Click “Connect” in the Integrator to connect to the Bookkeeper company you created.

• Select the correct company from the dropdown list if needed.

• Select the Bookkeeper company from the list.

6. Configure Import Options

• Optionally select a start date to limit historical data.

• By default, company info, bank accounts, and the chart of accounts are imported.

• Choose whether to import:

  • Transactions

  • Lists (customers, vendors, etc.)

• Click “Start” to begin the import.

   

7. Monitor the Import Process

• The integrator will show status messages and record counts.

• Large datasets may take hours, depending on size.

• Let the process complete fully.

8. Finish Integration in Bookkeeper

• After completion, close the integrator module.

• Launch Bookkeeper from the desktop. Right-click on the Bookkeeper shortcut icon and click Run as Administrator.

• Go to File > Import > Complete Integration.

• This copies the staged database back into Bookkeeper.

• This copies the staged database back into Bookkeeper.

• The Bookkeeper application will restart.

9. View Imported QuickBooks Data

• Reopen Bookkeeper (Run as Administrator).

• Log in with your set password.

• You’ll now see all your QuickBooks data:

  
  

  • Bank accounts

  • Customers

  • Vendors

  • Transactions

 Important Tips

• Don’t run QuickBooks or the Integrator as Administrator.

• Run Bookkeeper as Administrator only during the final import step.

• Be selective with data to import to save time and avoid performance issues.

Problem Description:

How to insert data into the VendorCredit table

Solution: 

Open the "QODBC Online Test tool" from the start menu.

Click on the "Connect" button.

Used the below SQL statement to insert data into the VendorCredit table

Insert into VendorCreditItemLine(RefNumber,VendorRefListID,APAccountRefListID,TxnDate,PrivateNote,ItemLineItemRefListID,ItemLineMemo,ItemLineQuantity,ItemLineRate,ItemLineAccountRefListID,ItemLineCustomerRefListID,FQSaveToCache)
Values('TestRef','98','165',{d'2021-11-01'},'New building','36','Installation',15,100,'231','59',0)

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:

How to export QuickBooks Data to Google Sheets using QXL

Solutions: 

Install the Google Drive desktop application and authorize it to connect to your Google Drive account.

Start Windows Explorer and navigate to your local Google Drive folder.

Create a QXL folder in Google Drive.

Start QXL Desktop and click on Settings.

In the "General" tab, set the path to the "Google Drive" path in the "Output folder."

For Example- C:\Google Drive\QXL

Close settings

Export the QuickBooks Data

Once the export is complete, the output folder showing the list of files will open automatically.

The output folder will contain the list of exported files.

The exported files will be automatically uploaded to Google Drive.

Open your web browser and navigate to - drive.google.com.

Navigate to the QXL folder.

You will find your QuickBooks company file folder.

Double-click the QuickBooks company folder to view the list of exported files.

To preview the file, double-click on any of the files.

In preview mode, you will not be able to change/modify any records.

To edit any file, go back to the preview.

Right-click on the selected file, and in the "Open with" option, select "Google Sheets."

Tags: QXL Desktop, Google Sheets

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:

Missing QODBC Menus in the Start Menu

Solutions: 

Navigate to the path: C:\Users\Administrator

In the Administrator folder, you will see the following options.

Switch to the 'View' tab in the menu bar and tick the checkbox 'Hidden items' option.

You will find the folder named 'AppData.'

After locating the AppData Folder, navigate further to the path:

AppData\Roaming\Microsoft\Windows\Start Menu\Programs

You will find the folder "QODBC Driver for use with QuickBooks."

Copy the folder "QODBC Driver for use with QuickBooks."

Navigate to the path below:

C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\Windows\Start Menu\Programs

It would be best if you replaced YOURUSERNAME with your Windows UserName.

In this example, we will take 'Ricky' as the Windows UserName.

C:\Users\Ricky\AppData\Roaming\Microsoft\Windows\Start Menu\Programs

Paste the copied folder to this location.

You will need Administrator Permission to paste the folder.

Click "Continue" to move ahead with pasting the copied folder.

Click the Windows Start button to check if you can view the QODBC menu.

Tags: QODBC Menu, QODBC Users, Administrator user

Problem Description:

Unable to use QODBC Online on Windows 7

Error - session failed. 653 - There was an error while communicating with the Ids Server. - - The Response Stream was null or empty

Solutions: 

You must enable TLS 1.1 and TLS 1.2 on the Windows 7 OS.

To allow QODBC Online to communicate securely with the Intuit QuickBooks Online server using TLS 1.1 and TLS 1.2, you have to do the following:

Please enable the internet connection on your machine. Please follow the steps below to enable TLS 1.2 on your system.

Follow the steps below to launch 'Services' from the start menu:

Click the Windows Start button from the desktop and search for the keyword 'Services.'

Click on 'Services' to launch the 'Services' application.

Start the two services below:

1) Background Intelligent Transfer System (Right-click and go to "Properties" and select start type as "Automatic" and click on the "Start" button)

2) Windows Update (Right-click and go to "Properties" and select start type as "Automatic" and click on the "Start" button)

Create a folder named TLS12 in your C: drive.

Download the two files below and place them in the TLS12 folder.

install-kb.ps1

tls-reg-edit.ps1

To download the compressed file - click here

Run PowerShell (without x86) from the Start menu as "Run as administrator."

In the PowerShell command prompt, navigate to the TLS12 folder and execute the commands below one by one.

PS C:\Windows\system32> cd\

PS C:\> cd .\TLS12

PS C:\TLS12> Set-ExecutionPolicy Bypass -Scope Process ; .\install-kb.ps1

Type "Yes" and press the "Enter" key.

It will update the system to KB3140245.

Restart your machine.

Run PowerShell (without x86) from the Start menu as "Run as administrator."

In the PowerShell command prompt, navigate to the TLS12 folder and execute the commands below one by one.

PS C:\Windows\system32> cd\

PS C:\> cd .\TLS12

PS C:\TLS12> Set-ExecutionPolicy Bypass -Scope Process ; .\tls-reg-edit.ps1

Type "Yes" and press the "Enter" key.

Restart your machine.

After restarting the machine, launch the QODBC Online Test Tool from Start Menu>> QODBC Driver for use with QuickBooks Online>> QODBC Online Test Tool.

Connect using the DSN' QuickBooks Online Data'.

You should be able to connect.

Source of PS  -  How+to+Configure+Microsoft+Windows+7+to+use+TLS+Version+1.2

If you are still facing the issue, install the Windows update KB3140245, either through Windows Update, where it is available as an Optional Update, or download it from the Microsoft Update Catalog (www.catalog.update.microsoft.com).

Download the file MicrosoftEasyFix51044.msi from the following page and install it on your computer:

Update-to-enable-tls-1-1-and-tls-1-2-as-a-default-secure-protocols-in

The file is available for download in the section labeled Easy To Fix on the above-mentioned page. Suppose the easy fix option is not suitable for you, and you prefer to manually edit your computer's registry. In that case, the article also provides that information in the section "How the DefaultSecureProtocols registry entry works."

If you are still facing the issue, please raise a support ticket to the QODBC Technical Support Department from the link below & provide the requested information:

Submit a Ticket

Please attach the following files:

1) Screenshot of QODBC Setup Screen -- > About

2) Screenshot of the issue you're facing.

Share the entire log files as an attachment in text format from

3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages

4) QODBC Setup Screen -- > Messages -- > Review SDK Messages

Refer to: How to take a screenshot: How to take a screenshot

Tags: QuickBooks Online, QBO, OAuth, Windows 7, TLS, Unable to connect, QODBC Online, QO

Problem Description:

How do I determine which database it is using to set up the connection with QuickBooks?

Solutions: 

You may run the following command to determine which database you are connected to.

sp_qbfilename

This will return the database (QuickBooks company file) path with which you are connected.

Using the QuickBooks application, you may verify the database (QuickBooks company file) path.

You can find the QuickBooks company file path via QuickBooks UI. Press the F2 key (Function Key 2) you will see the file information.

You may verify the QuickBooks company you are connected to.

To verify, please execute the SQL Statement using your application.

Select * from Company

Tags: QuickBooks company file name, company file path, database, database connection

All versions of QuickBooks from 2002 through 2021 are built on a 32-bit architecture. In contrast, many modern applications—such as Microsoft Excel, Microsoft Access, and Microsoft SQL Server are commonly deployed in 64-bit environments.

For users running QuickBooks 2021 or earlier, this architectural difference creates a compatibility challenge: 64-bit applications cannot directly communicate with 32-bit drivers. To address this limitation, we provide QRemote.

For the users of QuickBooks 2021 or eariler, since a 64-Bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect, and we call it QRemote.  QRemote works as a bridge (only for QuickBooks 2022 or lower) between 64-Bit Applications and the 32-Bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-Bit Web Servers and 64-Bit applications to talk to QuickBooks Live for the first time via the QODBC Driver.

For users running QuickBooks 2022 and later, this additional layer is no longer required. These versions of QuickBooks are native 64-bit applications, and the latest release of the QODBC Driver includes a fully compatible 64-bit driver. As a result, 64-bit applications can connect directly to QuickBooks (using the DSN "QuickBooks Data" ) without the need for QRemote.

QRemote Server is designed to enable connectivity and compatibility across different environments and application types. Its primary use cases include:

1. Service-Based Applications
   QRemote Server is used with service-based applications such as IIS,  SQL Server, PHP, Apache, Windows Services, Data Adapters, and other web server environments where direct interaction with QuickBooks may not be feasible.
2. Remote Connectivity
   QRemote Server allows applications running on a remote machine to connect to QuickBooks hosted on another system, enabling distributed setups and centralized QuickBooks access.
3. 32-bit and 64-bit Compatibility Bridge for QuickBooks 2021 and earlier
   QRemote Server enables 64-bit applications to communicate with 32-bit versions of QuickBooks (2022 and earlier) by bridging the architectural gap between modern applications and legacy QuickBooks environments.

Office 64-Bit (QuickBooks 2021 and eariler)

For Excel: Click Here

For Access: Click Here 

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

Office 32-Bit or Office 64-Bit with QuickBooks 64-Bit (2022 and later)

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

Problem Description

• I want to upgrade QODBC Online to the latest version. Can I reauthorize QODBC Online to connect to my QuickBooks Online company?
• Can I upgrade QODBC Online without reauthorizing QODBC Online with my QuickBooks Online?

Solution

During the installation of QODBC Online, a default System DSN "QuickBooks Online Data" will be created.

If you use "QuickBooks Online Data" DSN to connect to your QuickBooks Online, you can retain the connection to your QuickBooks Online company.

Please follow the steps below.

Note: Due to the change in OAuth 1.1 to OAuth 2.0, the following article is not applicable when upgrading from QODBC Online v19.00.00.333 or below to QODBC Online v19.00.00.334 and above. The following method will work when upgrading from QODBC Online v17.x to QODBC Online v19.00.00.333. The following method will continue to work when upgrading from QODBC Online v19.00.00.334 to any upgrade version/build.

1) Make a backup of your connection information - Export the current connection key:

Please follow the steps below to configure QODBC Online:

1) Open QODBC Online Setup Screen & click on "Connect To QuickBooks," & refer to the below step-by-step guide:

Start >> All Programs >> QODBC Driver for use with QuickBooks Online >> Configure QODBC Online Data Source. 

Go To "System DSN" Tab>> select QuickBooks Online Data>> click "configure"

Enter the name of the connection key & choose the location where you want to save the connection key:

Now the connection key is exported successfully. 

2) Uninstall the current version of QODBC Online.

3) Download and install the latest version of the QODBC Online.

Download the latest version of the QODBC Online installer from the link below:

QODBC Driver for QuickBooks Online Download

Install the newest version of QODBC Online. 

4) Import the connection key:

Start>>All Programs>> QODBC Driver for use with QuickBooks Online>> Configure QODBC Online Data Source>> Go To "System DSN" Tab>> select QuickBooks Online Data>> click "configure">> switch to General tab, click on "Import Connection key" button & import key.

Open the connection key from the location where you keep the connection key:

Now the connection key has been imported successfully. You can connect to QuickBooks Online using this connection key.

For system requirement details of QODBC Online, click here.

Tags: QuickBooks Online Connection key, QODBC Online connection key, connection key, import-export connection key, QuickBooks Online, QODBC Online, QBO

Problem Description

• How does QODBC validate all the business logic that is part of the QuickBooks application?
• Can QODBC read or write directly to the QuickBooks company file (.QBW or . QBB)?

Solution

QODBC works as a Windows ODBC driver (DLL) to access the QuickBooks data. QuickBooks Accounting stores its data in its own proprietary encrypted file system.

Due to the nature of accounting systems, balancing, and transaction integrity, the QuickBooks system prevents all external applications from directly accessing the data files used by QuickBooks. However, Intuit created a workaround called the QuickBooks SDK (software development kit), which resides inside the QuickBooks application interface.

QODBC allows users of any ODBC-compliant front-end application to read and write data contained in the QuickBooks system, similar to any other ODBC-enabled database, by processing commands via the QuickBooks SDK interface in the desktop application, so QODBC will not run if QuickBooks is not installed on the same machine or a connected machine (see: QRemote) because the SDK resides in the application
itself.

QODBC accepts SQL (structured query language) commands from applications through the ODBC interface, then converts those calls to navigational XML commands, passing them to the QuickBooks SDK for processing and returning recordsets that qualify for the query results.

This driver is not a Client/Server product or a direct database access tool because that method is prohibited by Intuit, but rather communicates directly with the QuickBooks application as it runs.

QuickBooks is a flat-file database; this driver will not change it into a relational database, so keep that in mind when developing with this driver.

QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools, speeding development time.

QODBC does not write to the QuickBooks company file directly. QODBC is writing through the QuickBooks SDK, which follows all business rules & validation.

Note: QuickBooks requires you to approve the security settings for QODBC to allow it to access the data in QuickBooks once for each application/company file combination. After this step, the connection to data in QuickBooks can be fully automated and does not require user input.

Tags: QuickBooks start, QuickBooks autostart, QuickBooks startup, QuickBooks connect, Hidden QuickBooks, Quiet QuickBooks, Silent QuickBooks, Background QuickBooks, How to setup QODBC for Auto-login into QuickBooks, a process for automatically start of QuickBooks, QuickBooks automatic, automatic quick books, autostart QuickBooks, automatically start of Quickbooks, running QuickBooks automatic, autologin, connection to data does not work unless Quickbooks is running even though I have that option turned on in the QODBC control panel, how to run without QuickBooks

Problem Description:

How to use the QODBC Support Wizard?

Solution:

Please follow the steps below for using the QODBC Support Wizard.

The "QODBC Support Wizard" application is included with QODBC v18.0.0.325 and above. If you are using an older version of QODBC, please upgrade your QODBC to the latest version. Click here to download the newest version of QODBC.

OR Click here to download "QODBC Support Wizard." 

Click on the "QODBC Support Wizard" icon from the Windows Start menu.

"QODBC Support Wizard" is open. Click "Next."

Follow the instructions on the wizard.

On the QODBC DSN Verification step, make sure it passes all the checks.

In case you see a warning icon below. Please click the "FIX" button so the QODBC Support Wizard can automatically fix the issue.

Once the QODBC Support Wizard has performed all checks, please click the "Next" button.

Once the QODBC Support Wizard has performed all checks, please click the "Next" button.

Now, click the "Test Connection" button.

On clicking the "Test Connection" button, you will notice "Connecting to QuickBooks" as shown below.

When QODBC tries to access the company file for the first time, QuickBooks will ask you for permission to allow the driver to access your QuickBooks company file. Switch to the QuickBooks application & accept the certificate.

OR

After accepting the Application Integration certificate, the QODBC Support Wizard should be able to connect to QuickBooks and should report "Test connection successful."

Click the "Next" button, select the default option "Yes, I can connect to QuickBooks," and click "Next."

You may click one of the options on the "Optional Configurations" steps. The default is "I can connect and would like to skip optional configuration/tests."

Click "Next" to navigate to the Finish window.

Testing your SQL Statement:

Start the QODBC Support Wizard from the Windows Start menu.

Click "Test my SQL commands in QODBC directly."

You may test connectivity and the data fetch on the "Test your SQL statements" steps. Click one of the five buttons, write your SQL statement, and click "Query."

Detailed explanation of each check

QODBC latest version check 

This step will check if your machine is installed with the latest QODBC version available on the website.

If your QODBC version is lower than the latest version, it will show a warning/failure mark.

To fix the issue, click the "FIX" button, and you will be navigated to the QODBC website to download the latest QODBC version.

(Note: Internet connectivity is required.

QuickBooks check (Multiple versions of QuickBooks installation) 

This step will check for multiple versions of QuickBooks installed on your machine.

If multiple QuickBooks are installed, you may not be able to use the QODBC Auto-login feature. This is because QODBC uses QuickBooks SDK to work with QuickBooks, and QODBC /QuickBooks SDK will not be able to judge from the company file path to determine which edition of QuickBooks to start.

For Example:

You have QuickBooks 2018 and QuickBooks 2020 installed on your machine.

You have created two DSNs to access the QuickBooks Company file 2018 and 2020 data.

When you auto-connect with the QuickBooks company file 2018, the QuickBooks SDK will not be able to identify which QuickBooks version to start. Hence, it may begin QuickBooks 2020 in the background (since QuickBooks 2020 is the latest QuickBooks version, it will start by default).

The solution is to keep the QuickBooks application running so that the QODBC / QuickBooks SDK does not have to load QuickBooks and can connect to an existing instance.

Or

If you are using only one of the QuickBooks versions, you may uninstall the non-required QuickBooks version.

QRemote Server firewall check 

This step will check whether incoming or outgoing connection requests from the QRemote Server port number are allowed via your Windows firewall.

If there is no entry of the QRemote Server port number 4500 in the firewall, then it will create one in the allowed list.

If you have selected disallowed in the firewall for connection requests coming from or to the QRemote Server port number 4500, then it will not alter those settings. You will need to change it manually.

QuickBooks and QODBC major version check 

This step will check whether your QODBC license OR version is higher than or equal to the QuickBooks version.

If your QODBC license or version is lower than the QuickBooks version, it will show a warning/failure mark.

For Example:

If you use QuickBooks 2020 and have QODBC version 2019, it will show a warning/failure mark.

Or

If you have QuickBooks and the QODBC version is 2020, the QODBC version is 2020, but your QODBC license is 2019, it will show a warning/failure symbol.

To fix this, you will need to activate the QODBC license and the version equal to or higher than QuickBooks.

Office 32/64-bit – QODBC license compatibility check 

This step will check whether you have a compatible QODBC license to work with MS Office 64-Bit.

If you do not have a QODBC license and are using MS Office 64-Bit, it will show a warning/failure symbol.

Once you have purchased and activated the QODBC license, the validation check will change to a green tick mark.

If you don't want to use MS Office 64-bit or purchase a QODBC license, you may switch to MS Office 32-bit.

Click on the "FIX" button and follow the instructions.

Or

Please refer to the link below on how to convert 64-bit to 32-bit MS Office:

http://qodbc.com/links/3000

QuickBooks check (Elevated Mode – Run as Administrator) 

This step will show a warning/failure symbol if your QuickBooks application runs under elevated permissions or as "Run as administrator."

The QuickBooks application and your application, like Microsoft Excel, Microsoft Access, or your custom application, should be running under normal/standard user mode.

For further instructions, click on the "FIX" button and follow the instructions.

Or

You may navigate to the following link:

http://qodbc.com/links/2529

Optional Configurations and Tests:

Set up Auto-login in QuickBooks 

This is an optional step.

This will help you create a QODBC DSN for Auto-login.

QODBC Support Wizard can create a new DSN and configure it to auto-login to QuickBooks.

Click the "Browse" button, provide a DSN Name (in case you want to change the auto-generated DSN name), and click "Create a new DSN."

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

Click "Test QODBC Connection"

Company file path – Specify the QuickBooks company file path for which you wish to configure Auto-login.

You may use the "Browse" button to locate the QuickBooks Company file.

or

You must copy the QuickBooks company file path and paste it into this field. You can find the QuickBooks company file path in the QuickBooks UI (press F2 in QuickBooks UI).

DSN Name – Once you have specified the company file path, the QODBC Support Wizard will automatically generate a DSN name based on your QuickBooks company file name.

For Example:

Suppose the QuickBooks company file name is "sample_product-based business.qbw" (refer to the image).

The wizard will generate a DSN Name – "sample_product-based bu" (refer to the image).

If you want to change it, you may create a new DSN Name per your requirement.

Test QODBC connection – Click on this button to test the QODBC connection to connect to your QuickBooks company file.

Test QRemote connection - Click this button to test the QRemote connection to connect with your QuickBooks company file.

Repeat the above steps to create multiple DSNs if you have numerous companies.

Rebuild the QODBC Optimizer file. 

This is an optional step.

This will help you reset and rebuild your QODBC Optimizer files.

QODBC Support Wizard can reset and rebuild the QODBC optimizer file.

Click "Reset Optimizer Files" to delete the existing QODBC Optimizer file.

Click "Optimize All Tables" - This will start the process and rebuild the entire optimizer file for the opened QuickBooks company file.

This process may take a few hours (4 to 24 hours), depending on the number of records in your QuickBooks company file.

Reset optimizer files – When you click this button, your QODBC Optimizer file will be deleted from the Optimizer folder.

It will ask for confirmation. On confirmation, the optimizer file will be deleted.

If you have multiple QuickBooks company files, it will ask for confirmation for each optimizer file.

Once you have reset/deleted the optimizer file, you can optimize all or the selected QODBC data tables.

Optimize All Tables – If you want to optimize all tables, click on this button.

Or

Optimize Selected Tables – If you want to optimize selected tables, then click on this button.

From the list of tables, select the tables you want to optimize.

For Example:

To optimize Bill, Invoice tables, select Bill, Invoice tables, and then click "Optimize Selected Tables."

QRemote connectivity test 

This is an optional step.

QODBC Support Wizard can also perform a "QRemote Test Connection" to check if QRemote is configured correctly and can connect.

Troubleshooting with QODBC Support Wizard:

If you cannot connect to QuickBooks, select "No, I am not able to connect to QuickBooks" and click "Next."

You may link one or more relevant topics from the FAQs and Knowledgebase, or search our knowledge base using the search button.

If you are still facing the issue, report the problem to us by creating a support ticket.

Also, refer to How to use QODBC Support Wizard - Remote Connectivity.

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

I am trying to install QODBC on my system installation hung (not responding) at 10 %, then gave the error below:
I could not load the DLL library
C:\Windows\System32\fqqb32.dll. Invalid access to memory location.

Problem Description 2:

I am trying to install QODBC on my system & got the below error:

The ordinal 2874 could not be located in the dynamic link library LIBEAY32.dll

Solution:

You might face this issue if ssleay32.dll & libeay32.dll (OpenSSL files) are corrupted or do not have any version or details of the file in the file properties.

The cause of the corrupted or wrong libeay32.dll file could be any other software that also uses similar technology that might have replaced this. QODBC will not install this file if it already exists and is the same or a newer version.

To resolve this issue, you need to rename the libeay32.dll & ssleay32.dll files & install QODBC again.

You can find libeay32.dll & ssleay32.dll from:

If you have a 32-bit machine:
C:\Windows\System32\

If you have a 64-bit machine:
C:\Windows\SysWOW64\

To rename a file, Right-click on the file & click on "Rename."
Rename both files one by one.

Rename file:

Rename file:

Now install QODBC. Refer: Connecting QODBC Driver to QuickBooks for the First Time (Video)

Verify both OpenSSL files after installation. You will notice that files have versions of other file details in the file property.

Keywords: The ordinal 2874 could not be located in the dynamic link library LIBEAY32.dll

For Windows 8 or 8.1, click here

Installation Steps for Windows 10

After you have downloaded QODBC Installer, navigate to the "Downloads" Folder or the location where you have saved QODBC Installer. Run the installer as administrator by clicking on the Installer and selecting "Run As Administrator." Don't forget to read the "Readme" file during installation for the latest information on QODBC setup, new features, and other driver information.

Please close all the applications. It is better to reboot the machine before starting the installation.  During the installation, you will be asked to select the Region of QuickBooks you are using. This should match the version of the driver you purchased. Next, select the destination directory to install the QODBC support files. Click "OK" to continue.

The FLEXquarters QODBC Driver for QuickBooks^® has been successfully installed on your computer. You can begin to use it immediately because it will run as a free 30-day evaluation edition, serving as all editions of QODBC, depending on how it is configured.

QODBC is installed on the Machine                        

To install a full purchase QODBC registration code to an evaluation copy from a previous download, see the Icons that have been added to your Start Menu on your Windows 10. i.e., "Purchase QODBC Now."

The first step in configuring the driver is to click on the QODBC Setup Screen icon, and the setup screen will become available.

Also, refer to the following:

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

How to set up QODBC Driver to auto-connect to QuickBooks. (Video)

How to setup QODBC to run without QuickBooks

There is no difference in the commands, queries, data, or schema between the QODBC Server Edition license and the QODBC Read/Write license.

QODBC is a client-side driver that typically resides on the workstations/client machine(s).

QODBC Server edition license is required when you have any service-based application like SQL Server, Web Server, and Windows Service connecting to QODBC or installing QODBC on a Terminal Service/Remote Desktop Service enabled machine.

QODBC license depends on the application you want to use. When the machine uses services like a web server, PHP, MS SQL Server, Windows task scheduler, and connecting to QRemote/QODBC needs a server license.

So, if you are using any service-based application, you need QODBC Server Edition.

If you want to use a desktop-based application like MS Access or MS Excel and access read-only, you need a QODBC Read Only license.

If you want to use a desktop-based application like MS Access or MS Excel and you want read-write access, then you need a QODBC Read-Write license.

QODBC licensing is machine-wise. QODBC licensing is machine-wise. You need to buy a license QODBC/QRemote for each workstation you want to use the QODBC/QRemote driver(s). If you have a QODBC Server Edition license activated on your Server, your workstations, if using QODBC/QRemote driver(s) directly, will still need a QODBC License.

Also, refer to the following:
QODBC Licensing Information
QODBC with QRemote Licensing Information

When you install QODBC Online below menu items are created under QODBC Driver for use with the QuickBooks Online folder in the Start menu.

1. Configure QODBC Online Data Source 64-bit

Configuring the QODBC Online Data Source 64-bit option will help you to create/configure a QRemote 64-bit DSN, which is used in the 64-bit application.  

2. Configure QODBC Online Data Source

Configuring the QODBC Online Data Source option will help you to create/configure QODBC & QRemote 32-bit DSN, which is used in the 32-bit application.  

3. Purchase QODBC Online Now

The purchase QODBC Online Now option will navigate you to the QODBC website for purchasing a QODBC Online license.  

4. QODBC Online Quick Start Guide

The QODBC Online Quick Start Guide option will open a start guide that will guide you on how to use QODBC Online.  

5. QODBC Online Setup Screen

The QODBC Online Setup Screen option will help you configure different setup options in QODBC Online.  

6. QODBC Online Support Wizard

QODBC Online Support Wizard is a tool available with QODBC, which is useful in testing the connection with QuickBooks. QODBC Online Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks Online\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks Online\QSW\QODBCSupportWizard.exe

7. QODBC Online Test Tool

QODBC Online Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC Online Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC Online

QRemote Server for QODBC Online will help you start QRemote Server if it is not running.  

9. QuickBooks Online Table Schema

The QuickBooks Online Table Schema option will navigate you to the QuickBooks Online Table Schema/Data Layout page.  

10. README File

README File option will open a README file containing instructions on how to install the QODBC Online, how to use the essential functions of the QODBC Online, and what the QODBC Online does. It also includes a list of recent updates made to the QODBC Online.  

11. Uninstall QODBC Online

Uninstalling the QODBC Online option will uninstall QODBC Online from the system. If you are updating the QODBC Online version, then you need to uninstall the older QODBC Online version & install the newer QODBC version. At that time, uninstalling the QODBC Online option will help you.  

12. VB Demo

VB Demo is a tool available with QODBC Online, which is helpful in testing connections with QuickBooks Online. VB Demo is also useful for querying table data. VB Demo can also perform insert/update/delete operations using standard SQL language.

Tags: QuickBooks Online, QBO, QODBC Online menu, configure QODBC Online

When you install QODBC POS below menu items are created under the QODBC Driver for use with the QuickBooks POS folder in the Start menu.

1. Configure QODBC POS Data Source 64-bit

Configure QODBC POS Data Source 64-bit option will help you to create/configure QRemote 64-bit DSN, which is used in the 64-bit application.  

2. Configure QODBC POS Data Source

Configuring the QODBC POS Data Source option will help you create/configure QODBC & QRemote 32-bit DSN used in the 32-bit application.  

3. Purchase QODBC Driver for QuickBooks POS

The purchase QODBC POS Now option will navigate you to the QODBC website for purchasing a QODBC POS license.  

4. QODBC Driver for QuickBooks POS Quick Start Guide

QODBC Driver for QuickBooks POS Quick Start Guide option will open a start guide that will guide you on how to use QODBC POS.  

5. QODBC POS Setup Screen

THE QODBC POS Setup Screen option will help you to configure different setup options in QODBC POS.  

6. QODBC POS Support Wizard

QODBC POS Support Wizard is a tool available with QODBC, which is useful in testing the connection with QuickBooks. QODBC POS Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks POS\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks POS\QSW\QODBCSupportWizard.exe

7. QODBC POS Test Tool

QODBC POS Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC POS Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC POS

QRemote Server for QODBC POS option will help you to start QRemote Server if it is not running. Note: QRemote Server has started automatically on system startup.  

9. README File

The README File option will open a README file containing instructions on installing the QODBC POS, how to use the QODBC, and what the QODBC POS does. It also includes a list of recent updates made to the QODBC POS.  

10. Uninstall QODBC POS

Uninstalling the QODBC POS option will uninstall QODBC POS from the system. If you are updating the QODBC POS version, you need to uninstall the older QODBC POS version & install the newer QODBC POS version. At that time, the Uninstall QODBC option will help you.  

11. VB Demo

VB Demo is a testing tool available with QODBC POS, which is helpful in testing the connection with QuickBooks POS. VB Demo is also a valid query table for data. VB Demo can also perform insert/update/delete operations using standard SQL language.

When you install QODBC, the following menu items are created under QODBC Driver for use with the QuickBooks folder in the Start menu.

1. Configure QODBC Data Source 64-bit

Configure QODBC Data Source 64-bit will help you to view/create/configure 64-bit DSNs like "QuickBooks Data 64-bit QRemote" or "QRemote Data 64-bit". 64-bit DSNs are used by the 64-bit application(s). You can run the 64-bit ODBC admin utility to see the data source from:
C:\Windows\System32\odbcad32.exe 

2. Configure QODBC Data Source

Configuring the QODBC Data Source will help you to create/configure QuickBooks Data & QRemote 32-bit DSN, which is used in the 32-bit application. You can run the 32-bit ODBC admin utility to see the data source from:
C:\Windows\SysWOW64\odbcad32.exe 

3. Purchase QODBC Now

Purchase QODBC Now will navigate you to the QODBC website for purchasing a QODBC license. 

4. QODBC Quick Start Guide

QODBC Quick Start Guide will open "QODBC Quick Start Guide.pdf." The Quick Guide document should help you get started and use QODBC. You can find information regarding how to use QODBC for the first time, samples, FAQ & support information. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QODBC Quick Start Guide.Pd

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QODBC Quick Start Guide.Pdf

5. QODBC Setup Screen

QODBC Setup Screen will help you to configure different setup options in QODBC. You can perform different configurations from the QODBC Setup Screen, like setting up auto-login to QuickBooks, checking QODBC & QuickBooks SDK logs, setting up the Optimizer option, and configuring other Advanced Setup options. 

6. QODBC Support Wizard

QODBC Support Wizard is a tool available with QODBC that is useful in testing the connection with QuickBooks. QODBC Support Wizard is verifying the configuration to connect to QuickBooks, which will help fix the issue. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QSW\QODBCSupportWizard.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QSW\QODBCSupportWizard.exe

Please refer to the How to use QODBC Support Wizard

7. QODBC Test Tool

QODBC Test Tool is a tool available with QODBC that is useful in testing queries with QuickBooks. QODBC Test Tool can also perform insert/update/delete operations using standard SQL language.

8. QRemote Server for QODBC

QRemote Server for QODBC will help you to start QRemote Server if it is not running. Note: QRemote Server has started automatically on system startup. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\QRemote\Server\QRemoteServer.exe

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\QRemote\Server\QRemoteServer.exe 

9. QuickBooks Table Schema

QuickBooks Table Schema will navigate you to the QuickBooks table Schema/Data Layout page, using that link. You can get details about the QODBC table & report schema. You will get information on each table's relation with other tables, table/report columns, and report parameters that are supported.  

10. README File

README File will open a README file containing instructions on how to install the QODBC, how to use the basic functions of the QODBC, and what the QODBC does. It also includes a list of recent updates made to the QODBC. Link location:
For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\readme.rtf

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\readme.rtf

11. Uninstall QODBC

Uninstalling QODBC will uninstall QODBC from the system. If you are updating the QODBC version, you need to uninstall the older QODBC version & install the newer QODBC version. At that time, uninstalling the QODBC option will help you. Link location:

For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\UNWISE32.EXE

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\UNWISE32.EXE

12. VB Demo

VB Demo is a tool with QODBC that is useful in testing the connection with QuickBooks. VB Demo is also useful for querying table data. VB Demo can also perform insert/update/delete operations using standard SQL language. Link location:

For 32-bit System:
C:\Program Files (x86)\QODBC Driver for QuickBooks\VBDEMO32.EXE

For 64-bit System:
C:\Program Files\QODBC Driver for QuickBooks\VBDEMO32.EXE

Problem Description:

I am testing the connection of QODBC & I am getting the QODBC test connection failed error:



Could you advise on the error and its solution? 

Solution:

The above error indicates that multiple instances of QuickBooks are running. Please close all instances of QuickBooks. Verify that no instances of QBW32 are running in the Task Manager (Processes> View all user processes).

Please run only one instance of QuickBooks as a context user, log in to the company file as Admin & try to connect using the QODBC Test Tool.

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

Problem Description:

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

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

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

Any Assistance Is Appreciated.

Solution:

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

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

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

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

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

Problem Description:

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

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

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



Any Assistance Is Appreciated.  

Solution:

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

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

For Example:

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

QODBC Desktop 

QODBC is a Windows Driver (DLL) with the exact system requirements as the QuickBooks Desktop Accounting version it communicates with.

QODBC POS 

QODBC is a Windows Driver (DLL) with the exact system requirements as the QuickBooks POS Desktop version it is communicating with.

QODBC Online 

QODBC Online supports Windows 7 SP1, Windows Server 2008 R2 SP1 (or above), Windows 8, Windows 10, and Windows 11

The latest Intuit IPP v3 .NET SDK minimum requirement is .NET Framework > 4.6.1

QODBC Online 18.00.00.325  to 19.00.00.333 uses .Net Framework 4.6.1

QODBC Online 19.00.00.334 or later uses .NET Framework 4.7.2

Refer to .NET Framework system requirements for more details about the supported Operating System detail.

Also, refer to Intuit's News:  Upgrading your apps to support TLS 1.2

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

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

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

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

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

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

'QuickBooks Data' as an example:

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

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

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

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

Report data is available in Microsoft Query:

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

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

Report Data is exported to an Excel spreadsheet.

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

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

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

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

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

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

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

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

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

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

Problem Description:

I just purchased a QODBC 2024 license.

I have everything set up, but I can't activate the CDKEY.

"CDKEY has an invalid checksum."

Solution:

QODBC 2024 CDKEY/license can only be activated on QODBC Desktop driver version 24.

QODBC 2023 and below CDKEY/license can be activated on QODBC Desktop driver version 24 and below.

Please download the latest version of QODBC Driver for QuickBooks - https://qodbc.com/qodbc-downloads/

CD Key has an invalid checksum (other than QODBC 2024 CDKEY/license)

Problem Description:

I am moving to a new server.

I have everything set up, but I can't get the license to transfer. On the old machine, when I go to the 'deactivate' using my CD key, I get:

"CD Key has an invalid Checksum."



Please help me to resolve this error.

Solution:

You will get the error "CD Key had invalid Checksum" when you try activating the wrong product's CD KEY.

QODBC Desktop license can only be activated on a QODBC Driver for QuickBooks Desktop. 

How to activate QODBC Desktop

http://qodbc.com/links/1957

QODBC Online license can only be activated on a QODBC Driver for QuickBooks Online.

How to activate QODBC Online

http://qodbc.com/links/2971

QODBC POS license can only be activated on a QODBC driver for QuickBooks Point of Sale.

How to activate QODBC POS

http://qodbc.com/links/3077

Please ensure you have purchased the correct QODBC product and activated it on the correct driver.

Problem Description:

I am trying to connect to QuickBooks Online using QODBC Online. However, I am encountering the error below at the time of connection.



Could you please help me resolve this error?

Solution:

QODBC Online is using the default port 8383. You will get the above error message when you have changed the port number in QODBC Online.

So, if you have changed the port number from the QODBC Online Setup Screen, then please close all the applications, wait for 5 to 10 minutes, and try to connect again.

If the above does not resolve the issue, then please ensure that no other application is using this port.

If any other application is not using the port specified on the QODBC Online Setup Screen, then you need to restart Windows & test again.

If any other application uses the port specified on the QODBC Online Setup Screen, you need to change the port from the QODBC Online Setup Screen.

You can change the port from:

Start >> All Programs >> QODBC Driver for use with QuickBooks Online >> Configure QODBC Online Data Source >> Go To "System DSN" Tab>> select QuickBooks Online Data >> click "configure" >> switch to General tab



Simply enter the port number you want to use in the "Port:" textbox and click "OK." In this example, I am changing it to the default port 8383.



After changing the port, please close all applications, wait for 5 to 10 minutes, and then try to connect again. If it does not work, you need to restart Windows & test again.

If you are still facing issues, then please raise a support ticket to the QODBC Technical Support department by clicking here

Tags: QuickBooks Online, QBO, QODBC Online, Port, Begin session, Unable to load component

Download Sample

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

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

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

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

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

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

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

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

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



Private Sub CommandButton1_Click()

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

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

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

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

End If

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

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

End If

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

rs. Close
oConnection.Close

End Sub

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

Invoice Added.

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

Download Sample

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

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

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

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

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

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

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

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

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

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

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

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

'CompanyA' as an example:

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

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

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

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

Report data is available in Microsoft Query:

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

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

Report Data is exported to an Excel sheet.

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

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

Here is my DSN configuration for the Second Company file.

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

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

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

'CompanyB' as an example:

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

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

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

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

Report data is available in Microsoft Query:

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

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

Report Data is exported to an Excel sheet 2.

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

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

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

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

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

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

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

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

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

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

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

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

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

Problem Description:

How many concurrent connections does the Server edition support?

Is the product QuickBooks version specific, requiring additional purchases with each version? AKA. Upgrading from V15 to V16? 

Solution:

There is no limit on QODBC for the concurrent connection, but the more connections, the more QuickBooks has to work. QuickBooks SDK will process all the requests in a sequence, so if you have three connections and three queries going at the same time, the SDK will execute them in sequence.

QODBC license is licensed for QuickBooks Version(Year). E.g., If you have QODBC licensed for 2015, you can use QODBC with QuickBooks 2015,2014,2013, and so on, but not with QuickBooks 2016.

You can install QODBC v15 and v16 with no upgrade cost. Upgrade cost is only applicable when your license does not match the year.

For Example:

If you have QODBC licensed for 2015 & you are using QODBC v15 & QuickBooks 2015, you can install QODBC v16 and use it with QuickBooks 2015.

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.

Problem Description:

I am trying to export data from QXL. But when I clicked on the QXL export button, I got the following error message.

Unable to connect to QuickBooks. Popup window(s) in the QuickBooks application MUST be closed before QXL can communicate with QuickBooks.

Error Occurred: ERROR [00000] [QODBC] QuickBooks Begin Session Failed. Error = 80040414. A modal dialog box is showing in the QuickBooks user interface. Your application cannot access QuickBooks until the user dismisses the dialog box.

Solution:

Please navigate to QuickBooks & make sure that there is no modal pop-up window open in QuickBooks. If any modal pop-up window is available in QuickBooks, you will get an "Error = 80040414; A modal dialog box is showing in the QuickBooks user interface....". QuickBooks says that a modal connection is active for this part of QuickBooks, and you need to wait.

It would be best if you closed this modal popup window in QuickBooks & export data using QXL.

QXL does not have control over the modal pop-up window in QuickBooks. QXL cannot suppress or bypass this type of dialogue. It would be best if you disabled the modal pop-up from QuickBooks.

Tags: Unable to connect to QuickBooks using QXL

Problem Description:

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

How to cancel the subscription of the QODBC Driver for QuickBooks Online?

 I received an email saying a subscription was going to be renewed. I need to cancel this subscription.

Solution:

Latest Method:

Please visit the following link: https://flexquarters.onfastspring.com/account.

Input the email address associated with your license.

Check your email inbox, and you should receive an email with the subject:

<Your Fullname>, here is a link to manage your orders

From: FastSpring Checkout <mailer@fastspring.com>

Click the link received to manage your account.

The link (URL) should look like https://flexquarters.onfastspring.com/account/somerandomtext.

Latest Method:

You can manage the subscription of the QODBC Driver for QuickBooks Online using the link you received in your renewal email.

On your monthly subscription renewal receipt, you will see the following, as in this example:

Order ID: FLE1XXXXX-1XXX-6XXXXB
1 QODBC Desktop Driver for QuickBooks Online USA
https://sites.fastspring.com/flexquarters/order/s/ (your order number). Click on this link on YOUR receipt, and you can cancel the subscription.

Example
https://sites.fastspring.com/flexquarters/order/s/FLE111111-1111-111111

Please visit that link & click on Cancel Subscription to cancel your QODBC Online subscription:

Tags: QuickBooks Online, QBO, Subscription QODBC Online, Cancel subscription

Intuit is reinforcing existing security features that help protect QuickBooks customers’ sensitive information.
Refer: Intuit's News: Changes to the Integrated Application Authentication for QuickBooks Desktop users


As a result of this effort, Intuit is delivering an update that impacts third-party applications that connect to QuickBooks using the QuickBooks SDK.

A new file specific to a Windows user on a given machine is created on user login that provides access to an SDK app in QuickBooks.

The implementation of this update will require customers to re-enter their login credentials & authorize the application(i.e., QODBC) at QuickBooks.

For Example, you have four Windows users. All four Windows users use QODBC via Microsoft Excel, Access, Crystal, or other applications. You will need to authorize EACH application from all 4 Windows users. That means you will need to log in to the 1st Windows user, endorse all the applications that will be using QODBC, then log in to the 2nd Windows user and authorize all the applications that will be using QODBC. This is because Intuit's LGB file, which was common earlier, has not changed to a new method, and integration information will be stored for each Windows user separately.

The above is applicable for a Single machine, Terminal Services(Remote desktop connection), or Workstation(s).

Problem Description:

Get the following errors from the QODBC driver:

ERROR [00000] [QODBC] QuickBooks Begin Session Failed. Error = 8004041d,80040418. This application cannot log into this QuickBooks company data file automatically. The QuickBooks administrator can grant permission for automatic login through the Integrated Application preferences.

Test connection to QuickBooks - FAILED: "[QODBC] QuickBooks Begin Session Failed. Error = 8004041a. This application does not have permission to access this QuickBooks company data file. The QuickBooks administrator can grant access permission through the Integrated Application preferences."

Solution 1:

Step 1: Launch QuickBooks and Open the Company File

Open QuickBooks Desktop.

Log in to the desired company file using the QuickBooks user "Admin".

Log in to the QuickBooks user "admin" is required to authorize the connection to the QuickBooks company file.

This is a one-time activity for each new application you want to connect to QuickBooks Data.

Step 2: Test connection using QODBC 32-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 32-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

QuickBooks may prompt you to authorize access: (optional)

If prompted, choose “Yes, always; allow access even if QuickBooks is not running”.

Select the Admin user from the dropdown (if applicable).

Click Continue → Done.

If the connection is successful, you can optionally run a test query (e.g., SELECT * FROM Customer).

Click "Disconnect" to close the connection.

Step 3: Test connection using QODBC 64-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 64-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

This should successfully establish a 64-bit connection. You may not be prompted for permission, as it has been granted in Step 2.

Once confirmed, click "OK" or "Disconnect" to close the session.

Step 4: Connect Using Your Application

In your application (e.g., PHP, .NET, Excel, Access, etc.), use the DSN name "QuickBooks Data" to establish a connection.

QuickBooks may prompt you to authorize access: (optional)

If QuickBooks does not ask for authorization, your application might already be authorized. You can run a few SQL statements/queries to verify that the connection is successful.

If you are unable to connect, please ensure you have logged in to the QuickBooks company file as the QuickBooks user "admin."

Solution 2:

You need to follow the steps below to reauthorize QODBC for QuickBooks.
1. Log in to Windows, Open QuickBooks & log in to the company file as Admin.
2. Switch Company File to Single User Mode.

3. Connect using your application. It should pop up the Data Access Certificate on QuickBooks. Please accept the Certificate. QuickBooks issues a Certificate for each application, so you need to get the Certificate for all applications that you use with QuickBooks.

4. You must log in to each Windows user accessing QuickBooks Data using QODBC.
If Windows User/Work Station changes, you must reauthorize the application using the above steps.

If the company file has Personally Identifiable Information (PII) in QuickBooks, users must log in with a password; the SDK app will only be allowed to connect once the login has been completed for that user. 

Customers will be instructed to set up a strong password when logging in. Customers should use unique letters and numbers in a password, not essential words that can easily be found online or in the dictionary.

Problem Description:

We downloaded the QODBC trial from your website. At the end of the installation process - after clicking the 'finish' button, a dialog appears saying that 'explorer.exe' cannot be found.

I am getting the following error:

Windows cannot find 'C:\Users\SAMMYH~1\WINDOWS\explorer.exe'. Make sure you typed the name correctly, and then try again.


Could not execute the external program

C:\Users\SAMMYH~1\WINDOWS\explorer.exe


The installation process aborts, and the software is then uninstalled.

Solution:

This issue occurs when using the QODBC V16.0.0.317 installer & Windows folder is personalized to point to the User's Roaming profile. We have fixed this issue in our latest version of QODBC (V318 or above). You can download the latest QODBC by clicking here.

If you want to use QODBC V317, then please follow the steps below to resolve the issue:

1. Open Windows Explorer.
Navigate to
C:\Windows\




2. Inside the "Windows" folder, you should find "explorer.exe."


Copy explorer.exe by right-clicking on explorer.exe & clicking copy.


3. Navigate to

C:\Users\Windows Username\Windows\
(Windows Username = The current logged-in Windows Username)

In this example, my Windows username is Dewani. So I am navigated to C:\Users\Dewani\Windows.


4. Paste the copied file in the directory C:\users\Windows Username\Windows\
In this example, my Windows username is Dewani. So I am pasting the file to C:\Users\Dewani\Windows. By right-clicking on the C:\Users\Dewani\Windows folder & clicking Paste.




5. Now try to install QODBC again. You will not get this error.

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.

Problem Description:

I was able to use QODBC Online. Suddenly, I got the following error when I tried to connect to QuickBooks Online using QODBC Online. It was working properly—no known changes to Windows or QODBC. Begin Session Failed. S0000 00000 [QODBC] QuickBooks Begin Session Failed. Error - Unauthorized-401 - - Fault Exception of type: AUTHENTICATION has been generated.

Solution:

The authorized access token for QuickBooks Online will remain active for 180 days. A privileged access token expires after 180 days. When this occurs, responses to calls to QuickBooks Data Services and the QuickBooks Online API indicate that the access token is invalid.

So, you need to reauthorize/reconnect QODBC Online at QuickBooks Online to resolve the issue.

Please refer to How to Reconnect QODBC Online with QuickBooks Online Edition.

Please follow the steps below.

1) "Disconnect from QuickBooks" 32-Bit.

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

2) "Disconnect from QuickBooks" 64-Bit.
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 64-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

3) Close all the applications.

4) Connect to QuickBooks:

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click "Connect to QuickBooks"

Please refer to https://qodbc.com/links/2659

5) Use the QODBC Online 32-Bit Test Tool to test the connectivity.


6) Export the connection key from "QuickBooks Online Data" DSN of type 32-bit 

Please use the DSN "QuickBooks Online Data" of type 32-bit to export the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure">> Switch to "General" tab >> Click on "Export Connection key" button to the Export key.

7) Import the connection key from the above step to the 64-bit DSN.

Please use the DSN "QuickBooks Online Data" of type 64-bit using 64-Bit ODBC Data Source.
You can import the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> "QuickBooks Online Data" of type 64-bit >> Click "configure">> Switch to General tab, click on "Import Connection key" button.

Refer to the knowledgebase article at https://qodbc.com/links/2658 Section "Import / Export connection key:"

8) Use the QODBC Online 64-Bit Test Tool to test the connectivity.

Tags: QuickBooks Online, QBO, Authorization, Authentication, 401, Begin Session Failed

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

I have a pass-through query for access:

Select * from Item

The connection string is as follows:

ODBC;DRIVER={QODBC Driver for QuickBooks};SERVER=QODBC;DFQ=x:\QuickBooks\MyQBcompanyfile.qbw;OpenMode=F;OLE DB Services=-2;OptimizerOn=No;

This works on pc #1 if the QuickBooks file is open OR closed (desired behavior).

This ONLY works on pc #2 if the QuickBooks File is closed.

Why is it operating differently?? I am using the same access file on both pc.

I need this to be running on some computers in the office. Some computers are allowed to use QuickBooks, and some are not, so I need a flexible DSN connection string that will work for both circumstances.  

Solution:

Please verify the company file path from the non-working machine. The other computer might be accessing the company file mapped via network name, i.e., \\servername\folder\ (e.g., \\QuickBooksfileserver\QuickBooks\MyQuickBookscompanyfile.qbw ) when the connection string is using a mapped drive, i.e., x:\

Please start QuickBooks from the non-working machine, log in to the company file, press F2, verify if the file path is the same in the connection string & test again.

Problem Description:

I am using MS Access. I have the integrated application certificate set up correctly (see screenshot). The username for this certificate is "odbc."



QuickBooks is closed.

Ms Access file connects to QuickBooks via the connection string:

"ODBC;Driver={QODBC Driver for QuickBooks};DFQ=D:\QuickBooks\MyCompanyFile.qbw;OptimizerOn=No;IAppAutomaticLogin=n;OpenMode=F;OLE DB Services=-2;"

Connection works.

Another user tries to log in as "Admin" into QuickBooks on another computer, but it fails and says already logged in. See screenshot.



Why is QODBC using the "Admin" account?? I was expecting it to use the "odbc" account.

Solution:

You need to remove QODBC integrated application entries in QuickBooks & companyfilename. lgb and delete it to resolve the issue.

Please refer to Connection Lost Between QODBC and QuickBooks. QuickBooks Asking for Password for more details.

Problem Description:

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

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

Solution:

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

Something along the lines of:

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

Problem Description:

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

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

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

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

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

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

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

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

Any help with this would be greatly appreciated! 

Solution:

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

Please click here to get the latest QODBC version.

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

Problem Description:

I am trying to connect to QuickBooks using QODBC. At a time of connection, QuickBooks Displays a Revoked Certificate Warning.

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, please check the digital signature of the application you are using, like Crystal Reports or Excel.

For Example:

If you are using MS Excel and getting errors, please check the digital signature in MS Excel.

Open the task manager, right-click on EXCEL.EXE, and click on Open File Location.

You will be navigated to the application location. In this example, we will navigate to the Excel exe location.

Once we reach the exe location, we must right-click on the exe and click on Properties. We will right-click on EXCEL>EXE in this example and click on Property.

Navigate to the Digital Signatures tab & click on Details.

Click on View Certificate to see certificate validity details.

You can see the certificate validity details.

If your application's Digital Certificate validity has expired, you need to get in touch with the Application Vendor to resolve the issue.

Windows Enforcement of Authenticode Code Signing and Timestamping

On Windows 7 platforms and above, the following are new guidelines from Microsoft:

• Microsoft will no longer allow new SHA-1 code signing certificates to be used for signing files on Windows 7 and above starting Jan. 1, 2016.
• Files signed previously with an SHA-1 certificate will continue to work on Windows 7 and above for an additional year as long as they were time-stamped before January 1, 2016.
• Windows 7 and Server 2008 R2 will require a hotfix for SHA-256 support.
• Starting on Jan. 1, 2017, all SHA-1 signed files would be blocked on Windows 7 and above, regardless of when they were time-stamped.

On Vista and Server 2008 platforms:

• Microsoft will execute end-of-life support for Windows 2004 in April 2017 and Server 2008 in January 2020.
• SHA-1 certificates will be supported through the end-of-life for these operating systems.
• There will be limited support for SHA-256 on these platforms, requiring a patch and only allowing downloads or installations of SHA-256 signed content.

Microsoft TechNet:
Windows Enforcement of Authenticode Code Signing and Timestamping

On Vista and Server 2008 platforms:
Microsoft Authenticode - Dual Code Signing Instructions with SHA1 & SHA256 hashing Algorithm


For more details, refer:
Microsoft Windows SHA-1 & SHA-2 Code Signing Hash Algorithm Support
SSL News
SHA-1 Hash Algorithm Migration for SSL & Code Signing Certificates
SHA-1 Deprecation, On to SHA-2

Also, refer to the following:

Revoked Certificate Errors With QuickBooks Desktop Apps

Problem Description:

How do I share/invite the FLEXquarters Support team to access my QBO company file? 

Solution:

Please follow the steps below to share/invite the FLEXquarters Support team to the QuickBooks Online company file.

1. You can share your QuickBooks Online company file connection key by exporting the connection key.

Please refer to the Import / Export connection key section for more details

2. Email the connection key to the FLEXquarters Support team.

3. Log in to https://qbo.intuit.com/ using your credentials.

4. Click on Company Name & Manage Users.

5. Go to the Manage Users section & click on New.

6. Choose user type, select Regular or custom user & click Next.

7. Set the user's access rights, select All & click Next.

8. Set the user's administrative rights. Select User management to "No," Company Information to "View Only," Subscription & Billing to "View subscription information," & click Next.

9. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

10. QODBC Support is listed under the Manage Users list as an Invited status.

11. Once we accept the invitation, the status will change to Active.

12. You can delete an invited user by selecting the user & click on Delete.

13. By clicking Invite Accountant, you can share/invite the FLEXquarters Support team to access the QuickBooks Online company file from the Accounting Firms section.

14. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

15. QODBC Support is listed under the Accounting Firms list as an Invited status.

16. Once we accept the invitation, the status will change to Active.

17. You can delete an invited user by selecting the user & click on Delete.

Tags: QuickBooks Online, QBO, QODBC Online

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

Please follow the steps to remove QODBC-related certificates from the Integrated Application Under QuickBooks POS:

Try removing all the integrated application entries in QuickBooks POS again via File Menu -> Preferences -> Company -> Integrated Applications Tab.

Switch to Integrated Applications

To know which application uses "QODBC POS" to communicate with QuickBooks POS.

You can check it from the Application ID & File Name.

To Remove, Select "QODBCPOS-APPLICATION," For Example, "QODBCPOS-MSACCESS.EXE," Click Remove and Save.

( If there are Multiple "QODBCPOS-APPLICATION" Listed, you may remove a single Integration at a time.)

Exit QuickBooks POS.

Please start QuickBooks POS and log in again to your Company file.

Problem Description:

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

Solution:

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

where RowType='DataRow'

For example:

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

Problem Description:

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

Problem Description:

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

Solution:

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

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

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

Or Raise a Support Ticket

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

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

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

Please refer to QODBC Data Layout for more details.

Problem Description:

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

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

Is the use of QODBC correct? 

Solution:

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

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

Problem Description:

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

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

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

Solution:

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

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

Problem Description:

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

Solution:

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

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

Problem Description:

I am using the following SQL statement:

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

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

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

Solution:

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

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

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

Problem Description:

I'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

Problem Description:

I have one QuickBooks Online login, which provides access to multiple QuickBooks Online companies.

When I initially configured the connection, I selected one of the clients to which I have access (Company A). The connection worked great, and I got the data I needed. Now I want to export data for another client of mine (Company B), but I'm unsure how to reconfigure it to pull from their database, not Company A's. 

Solution:

You can create a new DSN company-wise & authorize your company file, and use it in your configuration.

For Example, create a new DSN for Company A & use Company A's DSN for accessing Company A's data.

In the same way, you can create a DSN for Company B & use Company B's DSN to access Company B's data.

You can repeat this for multiple company files.

Please refer to the example below, which shows creating a QODBC Online DSN company-wise.

Launch QODBC Online Data Source Configuration

      Locate the QODBC Driver for the QuickBooks Online program group on your desktop. Click on the Configure QODBC Online Data Source icon to create or configure a QODBC Online Data Source Name (DSN).

Create a New 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 the QODBC Driver for QuickBooks Online option and click on the Finish button.

Configure the Created DSN

      Enter the Data Source Name as CompanyA and click Apply.

Click Connect To QuickBooks

You need to authorize your company file to work with QODBC Online.

During the authorization, select your Company file. 

For more details, please refer to How to Connect QODBC Online with QuickBooks Online Edition.

Once connected, you can use Excel, Access, or QODBC Online Test Tool or any other application, and use "CompanyA" instead of "QuickBooks Online Data." You should be able to connect and fetch data from QuickBooks Online.

You can repeat the above steps for another company file (i.e., CompanyB, CompanyC, et.).

Tags: QuickBooks Online, QBO, Configure online DSN, QODBC Online, connect company files, multiple

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'

Download Sample

Sample Code for using QODBC DSN:

Sample 1

import pyodbc

# Replace with your actual DSN name
dsn_name = 'QuickBooks Data'
print("connecting to the DSN" , dsn_name);
# Connect using the DSN
conn_str = f'DSN={dsn_name};'
conn = pyodbc.connect(conn_str)
print("Connected to the DSN" , dsn_name);
# Create a cursor from the connection
cursor = conn.cursor()
print("Cursor Created");
print("Executing select * from customer");
# Execute the query
cursor.execute("SELECT * FROM Customer")

print("Fetching all");
# Fetch all rows
rows = cursor.fetchall()

print("Getting column");
# Get column names
columns = [column[0] for column in cursor.description]

# Display header (first 5 column names)
print(" | ".join(columns[:5]))

print("Printing column");
# Display each row (first 5 columns)
for row in rows:
print(" | ".join(str(row[i]) for i in range(min(5, len(row)))))

# Clean up
cursor. Close()
conn.close()

Sample 2

import pyodbc 

cn = pyodbc.connect('DSN=QuickBooks Data;')

cursor = cn.cursor()

cursor.execute("SELECT Top 10 Name FROM Customer")

for row in cursor.fetchall():

print (row)

cursor.close()

cn.close() 

QODBC Configuration:

Turn on the "SQL Server Support" and set ODBC Compatibility to "Default". 

OR

Turn on the "Simulate Transaction support for SQL Server" option 

OR

Turn on the "SQL Server Support" option 

Script execution & Result in Command Prompt:

Navigate to your Python script folder:

Run your Python script:

Result:

Troubleshooting:

If you receive an error  [QODBC] Driver Not Capable when using "QuickBooks Data" DSN

To resolve this error, you need to enable the "Simulate Transaction support for SQL Server" or "SQL Server Support"  option from:

Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" Tab>> select QuickBooks Data>> click "configure">> switch to Advanced tab>> enable "Simulate Transaction support for SQL Server" option.

After enabling the above option, you can use "QuickBooks Data" DSN in the Python script.

Sample Code for using QRemote DSN:

import pyodbc 

cn = pyodbc.connect('DSN=QuickBooks Data QRemote;')

cursor = cn.cursor()

cursor.execute("SELECT Top 10 Name FROM Customer")

for row in cursor.fetchall():

print (row)

cursor.close()

cn.close() 

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

Script execution & Result in Command Prompt:

Navigate to your Python script folder:

Run your Python script:

Result: