Problem Description:

When I connect to QODBC, I am prompted to free disk space.

Solution:

Please ensure you have 1 GB of free space in your primary drive where your Windows Profile / Roaming Profile or QODBC Optimizer folder is saved.

Ideally, it is the "C" Drive

Location

%appdata%\QODBC Driver for QuickBooks

Example

C:\Users\YourWindowsUserName\AppData\Roaming\QODBC Driver for QuickBooks

On your C Drive, you should have 1 GV free space available.

Alternatively, you can turn off the optimizer from the QODBC 32-Bit Setup Screen.

Problem Description

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

Sp_report InventoryStockStatusByItem show QuantityOnOrder
Sp_report PurchaseByItemDetail

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

I am using QuickBooks 2023.

QuickBooks SDK Logs

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

Response from QuickBooks SDK

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

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

Solutions

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

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

Affected reports -  InventoryStockStatusByItem, InventoryStockStatusByVendor & InventoryValuationDetail

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

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

Updates:

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

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

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

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

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

Intuit Case ID: 00129682

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

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

Problem Description:

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

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

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

Msg 7343, Level 16, State 2, Line 1

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

Solutions:

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

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

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

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

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

Please refer:

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

Step 1 - Download QXL

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

Step 2 - Running the Installer:

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

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

Step 3 - Use QXL:

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

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

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

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

QXL Features:

1. Export QuickBooks Data to XML format via QODBC.

2. Export QuickBooks Data to CSV format via QODBC.

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

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

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

6. Ability to save your default settings from Options.

Also, Refer:
QXL options and settings

Problem Description:

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

Solution:

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

For example, the below query will return a row whose InventoryDate is blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NULL

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

For example, the below query will return a row whose InventoryDate is not blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NOT NULL 

Problem Description:

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

Solutions:

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

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

For Example:

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

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

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

2) Build Connection String:

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

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

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

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

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

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

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

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

Problem Description:

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

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

Solutions:

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

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

Problem Description:

How do I auto-increment the Ref Number for the Invoice?

Solutions:

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

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

Problem Description:

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

Solutions:

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

SELECT Max(RefNumber) FROM InvoiceLine  

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

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

Problem Description:

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

Solutions:

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

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

Problem Description:

How do I Auto Increment Alphanumeric RefNumber for Invoice?

Solutions:

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

Problem Description:

How do I take an error screenshot?

Solutions:

If you've got a problem with a Windows program, you may wonder how to show the errors you're receiving. Luckily, taking a screenshot of your current display is just a quick keystroke away. Follow this guide to take a screenshot of your whole screen, a specific window, or any display section you want.

1. Press the "Print Screen" (⎙ PrtScr) key on your keyboard. This will capture an image of your entire screen and copy it to the clipboard. The dimensions of the image will be the same as your desktop resolution. The ⎙ PrtScr button may be labeled "PrtScn," "Print Scrn," "Print Scr," or something similar. On most keyboards, the button is usually found between F12 and Scroll ⇩. You may have to press the Fn or "Function" key on laptop keyboards to access the "Print Screen." [1] You will not receive any confirmation that the screenshot has been taken.

2. Paste the screenshot. Once you've made the screenshot, you must paste it into a program to see it. The most common way to paste it is into a Paint file. This will allow you to save it as an image file that can be transferred to others—open Paint from the Accessories section in your Start menu. With a new blank canvas available, press ^ Ctrl+V to paste the image onto the canvas. You can also open the Edit menu and select Paste or right-click on the canvas and select Paste. Click the file, then Save to set the image's name and file type. The most common file types are JPG and PNG. The recommended format for screenshots is PNG due to the high quality and small file size. You can also paste the screenshot into other programs, such as Word, or into the body of an email. Open the program you want to paste the image into and press ^ Ctrl+V.

3. Save the screenshot: Perform any edits you want, such as cropping the image to a specified area, and click the main File option in the top-left corner. Afterward, select Save as, title the new file, choose a save location, and select your desired file format from the drop-down menu. JPG will do fine for most purposes, but various other forms are available, such as PNG, BMP, GIF, and others. Click the gray Save button in the bottom right-hand corner when finished. 

Problem Description:

Can I adjust the value of a stock item without changing the stock quantity?

Solutions:

Yes, you can adjust the value of a stock item without changing the stock quantity. Here is an example:

This item has a COGS Average Cost of $69.73429:

Using QODBC, I adjusted the opening balance equity cost of the stock item by $250.00 using the following SQL insert statement:

INSERT INTO "InventoryAdjustmentLine" ("AccountRefFullName," "TxnDate," "RefNumber," "Memo," "InventoryAdjustmentLineItemRefFullName," "InventoryAdjustmentLineValueAdjustmentNewValue," "FQSaveToCache") VALUES ('Opening Bal Equity,' {d'2007-05-23'}, '110', 'Value Adj 250', 'Wood Door: Exterior', 250.0, 0) 

This item now has a COGS Average Cost of $106.69857:

The value adjustment details can be seen in the Inventory Valuation Detail: 

Problem Description:

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

Solutions:

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

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

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

Please refer to: How to Use QODBC with Microsoft Access 2010

Problem Description 1:

1. I'm attempting to utilize your ODBC driver for reporting through Excel from QuickBooks Enterprise. The tables in your report help guide aren't accessible to me and appear as part of the QBAdvancedReportGroup. An example is a v_account view. I couldn't find any other place to get a list of the chart of accounts. Is it possible to obtain permission to be part of that group? I have added every privilege to the ODBC setup in the application but did not see that group as an option.

Also, when opening QODBC Test Tool, I cannot use the sp_report procedure - I get the message "procedure: sp_report not found." Is that something related to the privileges? How can I get access to that procedure as outlined in your documentation?

2. I have a customer using QuickBooks Enterprise, and they are using the built-in ODBC in conjunction with MS Query and Excel to create a refreshable report.

Whenever they close & reopen QuickBooks, the query no longer works. This is because the DATABASE NAME in the "Connect to SQL Anywhere" screen changes each time QuickBooks is closed & reopened.

3. I have set up a connection from Access to QuickBooks. However, I can only open tables in the QBReportAdminGroup, not those in the QBAdminGroup. I get the following error:

ODBC--call failed

[Sybase][ODBC Driver][SQL Anywhere]Permission denied: you do not have permission to select from "v_sales_receipt_sn_list" (#-121).

The ODBC user I created has all groups assigned to it.

Also, I do not have permission to update the tables I do have access to, i.e., "QBReportAdminGroup_v_lst_item," view data.

Please advise how to resolve these problems.

Problem Description 2:

I am connected to QODC. When I run the QODBC Test Tool, it gives me an error [Sybase][ODBC driver]SQL Anywhere] Invalid user ID or password.

I'm entering the QuickBooks administrator user ID and administrator password. Other users and passwords also don't work.

Solutions:

You're using Intuit Custom Reporting for QuickBooks Enterprise and Not QODBC. I kindly request you remove any connection you use with Sybase SQL Anywhere and use QODBC.

It would be best if you connected to "QuickBooks Data" DSN (from system/machine DSN), not to any file DSN ( Do not try connecting to File DSN, which is next to your company file with the name COMPANYFILENAME.dsn). Please refer to the below articles for how to use QODBC:

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

Please note: you need to select "QuickBooks Data" (if the application is 32-bit) or "QuickBooks Data-64-bit QRemote" (if the application is 64-bit) when you choose a data source instead of selecting another data source.

Tags:

QBReportAdminGroup_v_txn_bill_line, v_lst_time_activity,v_lst,v_cf, v_txn_sales_receipt_line.transaction_date, v_lst_item.description, v_lst_item.name, v_cf_item.name, v_cf_item.field, v_txn_sales_receipt_line.item_id, v_txn_sales_receipt_line.unit_price_amt, v_txn_sales_receipt_line.amount_amt, v_txn_sales_receipt_line.quantity_qnty, v_txn_sales_receipt_line.doc_num_h, v_txn_sales_receipt_line.customer_id, v_lst_ship_to.state, v_lst_ship_to.postal_code,QBReportAdminGroup.v_cf_item v_cf_item, QBReportAdminGroup.v_lst_item v_lst_item, QBReportAdminGroup.v_lst_ship_to v_lst_ship_to, QBReportAdminGroup.v_txn_sales_receipt_line v_txn_sales_receipt_line, qbreportadmingroup_v_lst_account

Problem Description:

I have exported the customer list from QB, and now I want to insert these using the insert query. But some of them fail because of the apostrophe (').
How to deal with special characters like this.

Solutions:

I want to inform you that you can get an additional quote for your query.
For Example, if you want to add a customer whose name contains extra quotes, then you can insert the name using the following query:
Insert into customer (name) values ('Larry's Custom Invoice ')
The above question will create a customer entry named Larry's Custom Invoice.        

Problem:

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

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

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

R6025
- pure virtual function call

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

Solution:

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

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

Also, please follow the steps below:

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

Keywords: Runtime Error, MS Access Crash

Problem Description:

When making an inventory adjustment in a company file whose multiple inventory sites feature is not enabled, there are no values in the field "InventorySiteRefListID."

So, trying to insert an inventory adjustment using a query like this:

INSERT INTO InventoryAdjustmentLine(AccountRefListID, InventorySiteRefListID, InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentQuantityDifference) VALUES ('80000003-1350417287', '', '80000296-1350423383', 10)

But QODBC reports an error [ The given object ID "in the field "ListID" is invalid ] :

Solution:

If you are not working with a multiple inventory sites feature-enabled company file, then there is no need to add the field "InventorySiteRefListID."

So you could directly remove this field and run the query like this:

INSERT INTO InventoryAdjustmentLine (AccountRefListID, InventoryAdjustmentLineItemRefListID, InventoryAdjustmentLineQuantityAdjustmentQuantityDifference) VALUES ('80000003-1350417287', '80000296-1350423383', 10)

This error usually occurs when you try to insert records with a null value.

When there are many columns in the table InventoryAdjustmentLine, not all of them are necessary for insertion. You don't want to assign data to those columns; remove them from your insert query. Please do not manually add it to the question and assign a null value.

QuickBooks will automatically set those columns' null value when the record is inserted.

I have a customer who does their invoicing with an ASP.NET application, and it flows into QuickBooks. I created the interface with QODBC, and it works with no problem except that sometimes the processes will lock up, and I have to remote into their web server and physically kill the processes (QBXMLR~1, QBServerUtilityMgr). What would cause these processes to lock them up? Could it be that more than one person is invoicing at the same time? At the end of my save routine, I even force a kill off the processes if they haven't ended.

I have "Use DCOM Servers" checked. Here is all the QODBCLOG.TXT says.

IsAService: False
Begin Session Failed. S0000 00000 [QODBC] QuickBooks  Begin Session Failed. Error = 800706be, Unknown error.

Begin Session errors can be caused when running ASP or other system services and not setting the "Advanced" button option of either "Use DCOM Servers" or "Remote Connector."

Every morning I have to kill the processes, and I also have to do it throughout the day. I've checked everything, and I cannot solve this problem.

Solution:

The error is saying, "The remote procedure call failed," which leads me to believe you have a broken error recovery table in the company file.

Usually, corruption is caused by sending many requests in one message set or not clearing the error table between message sets.

To overcome this error, you must reinstall QODBC or upgrade to QODBC's latest version.

How to view details on what QODBC is performing / Display Driver Status / Display Optimizer Status

Problem Description:

Question: QODBC and my application are not responding. How can I know what QODBC is performing in the background?

Question: I cannot get the connector to work with my system. It's pulling in some of the data, but not all. I've tried everything.

Solution:

You can enable the QODBC status panel using Windows Start >> QODBC Driver for QuickBooks >> Configure 32-Bit QODBC Driver or  Configure QODBC Data Source.

or

Go to the "System DSN" Tab, select "QuickBooks Data" DSN & click "Configure."

Navigate to the "Message" tab ->Select "Display Driver Status" and "Display Optimizer Status" options.

Then the next time you run a query, if you see "Waiting for QuickBooks," It means QuickBooks is taking time to process the request.

There will be a status panel at the lower right corner of your screen, showing a window with information on what QODBC is working on.

Please note the step on which QODBC spends the most time or gets stuck and share it with us.

Also refer to How QODBC Optimizer Works

After you have enabled the driver status panel, please try the following steps.

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

Please wait for the SQL statement to generate the result.

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 execute a few SQL statements/queries to see if 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."

Suggestion:

Suppose you have a large QuickBooks company file with several years of data.
The QuickBooks SDK has to scan and process many records to extract data, resulting in performance issues and slowness.
Historical data may not be required in day-to-day transactions/reports.
It is advisable to reduce the size of large QuickBooks company files for better performance.
To reduce the size of the QuickBooks company file, you can consider archiving old data.
To archive old data, you can create a copy of the company file and save the company file under a new name.
Condense the new QuickBooks Desktop company file.
You should notice performance gains when using a condensed QuickBooks company file.
You can always refer to the archived data at any time using the QuickBooks application or QODBC.

Refer to
https://quickbooks.intuit.com/learn-support/en-us/help-article/data-utilities/use-condense-data-utility/L7zprPIcj_US_en_US
https://quickbooks.intuit.com/learn-support/en-uk/other-questions/quickbooks-desktop-2020-condense-data-utility/00/532124
https://quickbooks.intuit.com/learn-support/forums/searchpage/tab/message?advanced=false&allow_punctuation=true&q=condense%20data
https://quickbooks.intuit.com/learn-support/en-us/other-questions/condensing-a-large-file/00/573043
https://quickbooks.intuit.com/learn-support/en-us/other-questions/best-way-to-split-one-company-file-into-two-originally-used-for/00/810101

Tags: configurations, why QODBC is slow. QODBC not responding

When trying to connect to QuickBooks using the QODBC Test Tool, I am facing the error :

QuickBooks Begin Session Failed. Error Calling OpenQBConnection Returned Error: 10061 - [10061] Connection refused

Solution:

Please first keep QuickBooks running and log in as Admin in single-user mode. Go to QuickBooks, "File" list menu, and then check if there is a "switch to Multi-User Mode" option. If there is, then you are running QuickBooks in single-user mode. But if there is no option, click the "switch to single-user mode" option in the same list menu to set QuickBooks running in single-user mode.

If you are not sure how to connect to QuickBooks using QODBC, please refer to this article for more information:

How to Connect to QuickBooks for the First Time

If you are using "Remote Connector" to build a remote connection, please check if you have configured it correctly.

Please refer to this article for more information about setting up a "Remote Connector":

How to Setup QODBC Remote using Remote Connector

Please make sure you have unchecked the "Connect To QuickBooks Online Edition" option in the QODBC Setup Screen, "Online Edition" window if you are not going to use QODBC to access Connect To QuickBooks Online Edition(QBOE):

And also, please make sure you have unchecked the "Use RDS Client/Server" option in the QODBC Setup Screen, "RDS Server" window if you are not going to access QuickBooks remotely:

RDS is a product from QuickBooks; we may not be able to provide much help if there is a problem using RDS. If you are going to set up QODBC to access QuickBooks remotely, please use QRemote. For more information on using QRemote as a remote access solution, please refer to this article:

Using QuickBooks data remotely via QODBC

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

Solution:

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

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

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

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

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

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

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


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

For example:

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

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

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

Keywords: assignment error

QuickBooks 2014

QODBC 14.0.0.298

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks & QuickBooks company files. QODBC can show/fetch only the information that is exposed/provided by the QuickBooks SDK.

A few changes in the QuickBooks SDK for QuickBooks 2014 return incorrect data, causing QODBC to fail.

Below are the known issues with QuickBooks SDK.

1) InventoryStockStatusByItem, InventoryStockStatusByVendor & InventoryValuationDetail do not show any result.

2) Customer table  "Contact" Field is not returning data.

3) A few of the reports show detailed information, which earlier showed summary information. Rows are no longer grouped as "--Multiple--."

4) A few of the reports show summary information that earlier showed detailed information. Rows are now grouped as "--Multiple--."

Please get in touch with Intuit if you need to use the data that this recent update has disabled.

Problem Description 1:

QRemote Initialization Error,

QRemote Error: Listening failed. Please restart the application

QRemote Closing in 15 Seconds.

Problem Description 2:

I'm following the tutorial here: http://qodbc.com/QODBCremote.htm to access QuickBooks remotely. However, when I launch QRemote, the application immediately closes. My QRemote is version 1.0.0.321. 

Solution:

There might be multiple instances of QRemoteServer.exe running, or the Machine would require a restart.

QRemote Server reports "Error: Listening failed. Please restart the application," usually because there is already one QRemote Server running in the system.

Please open Windows Task Manager and go to the "Processes" window. Select the "Show processes from all users" option. Then find "QRemote Server.EXE" in the process list, and terminate this process.

Note: In Windows 8, Windows 10, Windows 11, and Windows Server 2012, you can find QRemoteServer.exe details under the "Details" tab:

Start Windows Task Manager, Switch to Details,

After you ensure there is no "QRemote Server.EXE" running in the system, please start QRemote Server to see if the problem is still there.

If you are on a Terminal Service Machine/Remote Desktop-enabled machine and have multiple users logged in.

QRemoteServer gets installed as a startup for all the users, and the 1st instance of QRemoteServer will be used to connect to QODBC DSN/QuickBooks.

If you want to change this, you may use MSConfig and remove QRemoteServer from Startup. Then manually add the user's startup folder who will be using the QRemote/QuickBooks. Please refer to http://qodbc.com/links/2595.

Problem Description:

The TimeTracking / Transaction table always returns null/ no rows. I tried to reset the QODBC optimizer, but it didn't work.

Solution:

Please update your QuickBooks to the latest version & recheck the issue.

Also, please ensure that the short date format in the Regional Setting is not set to "yyyy mmm dd." If your short date format is "yyyy mmm dd," you might face this issue. 

We have received multiple reports about the table TimeTracking not returning any data, and we found that this is usually caused by Corruption in the Company file. To locate your issue, please test the same query on another company file, such as a sample company file, to see if it can bring data back.

Also, try limiting the result set by adding filters and applying a date range.

If the same query can bring data back to other company files, please try rebuilding your company file via File->Utilities->Rebuild Data in QuickBooks to see if it can fix your issue.

And if rebuilding does not work, please get in touch with Intuit for more help.

And if QODBC cannot extract TimeTracking/Transaction data from other company files, please post a ticket to us so we can further investigate the issue.

For Advanced Troubleshooting, Click Here

Problem Description:

When trying to update the memo field in the table Invoice or InvoiceLine, QODBC reports the error:

[QODBC] Error: 1030-Not supported

It is not only in the field memos but also in other fields in the table Invoice and InvoiceLine.

Solution:

There is no InvoiceMod subject in Intuit SDK, meaning QBOE does not support any modification to the Invoice. You may need to update the records in QBOE once it is created.

This limit is not only in the Invoice table. The schema rules in QBOE are different from those in Desktop QuickBooks. Please refer to this article: The Schema Rules Differences Between Local QuickBooks and QBOE, for more information about the differences.

Also, refer to How to get the table schema For the QuickBooks you are connected to.

Tags: QuickBooks Online, QBO, Update Invoice, Invoice online

Note: VB Demo is deprecated.

Please refer to How to use the QODBC Test Tool for testing

Problem Description

     1. When I query my tables, e.g., Bill or Customer, I notice QODBC is not returning all the records, and it seems a few are missing.

      We have several invoices that don't exist in the linked invoice table. The invoices can be pulled up in QuickBooks, but searching for the invoice number in the RefNumber field doesn't return any results.

    2. I can create an IRL and an IR using QODBC and see the IR in QB. But when I run the VB DEMO or SQL using QODBC, it says record not found. I am unable to retrieve anything that I added after 11/18. But I can see the data in QB and process it in QB.

I also tried creating a Bill in QB and attempted to use a simple SQL to run in VBDEMO, but nothing showed up.

Solutions

The reason could be that the QuickBooks Company file requires Rebuild/Verify, the QODBC cache file/Optimizer is corrupted, or a mismatch has happened in the cache file.

Try the steps below to see if it fixes the issue  

1. Rebuild your company file

To rebuild the QuickBooks company file, please select File->Utilities->Rebuild Data option:

Please follow the instructions of QuickBooks and make a backup before rebuilding.

2. Delete Optimizer file(s)

Close all applications other than QuickBooks/Reckon.

Follow the steps from the How to Remove Optimizer file

Verify that the optimizer file(s) are removed.

The optimizer file can be found at "%appdata%\QODBC Driver for QuickBooks\Optimizer."
For Example: C:\Users\YourName\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer

3. Rebuild the Optimizer file

Close all applications other than QuickBooks/Reckon.

Start QODBC Test Tool from Start >> All Programs >> QODBC Driver for use with QuickBooks >> QODBC Test Tool.

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

To Connect to DSN, Select "QuickBooks Data" -> Click "Connect."

QODBC Test Tool connected.

Write the "sp_optimizefullsync ALL" command in QODBC Test Tool & execute it by clicking the "Run" button.

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

Click on the "Run" button to start optimizing the records.

The optimizer-building process started.

The optimizer building process is finished.

Or ( For Selected tables)

Close all your applications
Start QODBC Test Tool
Connect to your DSN. Execute the command below
sp_optimizefullsync Bill
sp_optimizefullsync Customer

Also, Refer

VB Demo Missing

Troubleshooting: Cannot Get Full Records From QuickBooks

How to switch OFF or RESET the QODBC Optimizer

Keywords: QB odbc slow, multiple web queries into Excel slow running, Excel running background query slow, qodbc and Excel connection speed

"HY111 database connector error invalid bookmark value" on the Crystal Report when refreshing the report.

Solutions

This could be the issue with the company file or Optimizer.

I kindly request that you follow the steps and let us know if that resolves the issue.

In QuickBooks, Verify & Rebuild the Company file.
On QODBC Setup Screen -> Optimizer -> Turn On the Optimizer and Click Reload All Data.
Try your SQL Statement with VBDemo/Crystal Reports

If you are still facing the issue, log in to any Sample company file of QuickBooks and follow the above step.

If the Sample file can work, I guess this could be the issue with your company file and some data in your company file.

If the Sample file can NOT work, I guess this could be an issue with QuickBooks or QODBC Configuration. I would suggest trying the report/query on a different machine.