For using QODBC with Tableau Desktop, You need to use QODBC V 18.0.0.326 or a later version.

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.

After installing QODBC latest version, We need to enable ODBC 3.8 compatibility from QODBC Setup Screen >> Advanced tab.

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks and click "Configure QODBC Data Source."

Navigate to the "System DSN" tab & select "QuickBooks Data," & click "Configure."

Note: If you are using a user-defined DSN, you need to make changes to your user-defined DSN.

QODBC Setup Screen will open on clicking configure. Now, Please navigate to the "Advanced" tab.

Please change "ODBC Compatibility" to "3.8" from "Default" & "Apply changes."

Now, Open Tableau Desktop, Navigate to "To a Server," & select the "Other Databases (ODBC)" option.

In Other Databases (ODBC), Select "QuickBooks Data" data source and click "Connect."

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

Tableau Desktop is connecting to QODBC.

Tableau Desktop is connected to QODBC. Click on "Sign In" to move ahead.

QODBC connection is added to Tableau Desktop.

Now, Write any table name you want to use & click on the "Search" icon. In this example, I have written the "Customer" table name.

The "Customer" table has been added to Tableau Desktop. Double-click on the table name to see the table schema.

Now, click "Sheet1" to add table data to the Tableau Desktop sheet.

Tableau Desktop will show an ODBC connection warning. Click "OK" to continue.

The customer table data is added to the sheet. Click "Data Sources" for the list of available table fields.

Now drag any table field in the sheet which you want to show. In this example, I have added the FullName field from "Dimensions" & Balance field from "Measures" in the sheet.

You will notice that Customer FullName & Balance is loaded into the sheet.

You can choose the option from "Show Me" to see QuickBooks Data per your requirements.

Problem Description:

I am using QODBC for working with QuickBooks. I can delete the VehicleMileage record. But After that, when I query the VehicleMileage table, it still shows deleted history.

The UNOPTIMIZED query working fine.

Solution:

This issue is related to QuickBooks SDK. QuickBooks SDK is not returning deleted records in TxnDeleted & due to this, QODBC cannot remove the deleted records from the Optimizer.

We have already reported the QuickBooks SDK issue to Intuit.

Until Intuit fixes the issue, we have applied a workaround in QODBC V18.0.0.326 that the VehicleMileage table will directly fetch data from QuickBooks & does not use the Optimizer.

So if you are using QODBC V325 or an earlier QODBC version, then you need to use the "UNOPTIMIZED" keyword in your query to fetch correct records.

For Example:

Select * from VehicleMileage UNOPTIMIZED

To use QlikSense Desktop with QODBC, Please follow the below steps.

Open QlikSense Desktop in the browser & click on the "CREATE A NEW APP" button.

Enter the app name and click the "Create" button.

A New application in QlikSense Desktop was created successfully. Click "Open app" for the opening application.

The application has two optionsfor: "Add Data" & "Data Load editor." In this example, I am using the "Add Data" option. Click the "Add Data" button to update QuickBooks Data to QlikSense Desktop.

QlikSense Desktop will show a list of the data source. Please select the "ODBC" data source.

In Create new connection (ODBC) windows select "System DSN" & enable the "32-bit" option and select DSN & click on the "Create" button. You can change DSN as per your configuration. I am connecting to the default DSN "QuickBooks Data QRemote" in this example.

The connection was made with QuickBooks through QuickBooks Data QRemote DSN & you can see the list of tables and windows available.

Select the table you want to add to the QlikSense Desktop & click the "Add data" button. In this example, I am selecting the Customer table.

Adding data is in progress.

The customer table data is added to the QlikSense Desktop. Now, You can perform any operation on table data using QlikSense Desktop.

You can view table data by double-clicking the "Customer" table icon.

Please click the "Load data" button on the QlikSense Desktop.

Loading data is in progress.

The data is loaded successfully. Click on the "Edit the sheet" button to add data to the sheet.

The empty sheet is open in the QlikSense Desktop. Click on the "Fields" icon to add table fields to the sheet.

In this example, I have added the Customer table's FullName, CompanyName, and Phone fields on the sheet. Please click on the "Done" button to finish editing the sheet.

QlikSense Desktop displays data on the sheet.

Now, You can perform any operation on table data using QlikSense Desktop.

Also, Refer:

How to Auto Add relationship Between two tables in QlikSense Desktop using QODBC

Problem Description:

I am on Windows 64-bit and Office 365 64-bit. QODBC Test Tool works fine when I use QuickBooks Data as a connection source.

If I use QRemote, I get the "Evaluation Expired. QRemote requires QODBC license." message.

That connection is not available in Excel, only in QuickBooks Data 64-bit QRemote.

Any help would be appreciated.

Solution:

You are not getting QuickBooks Data DSN in the connection window because you are using MS Office 64-bit version.

You can identify if your MS Access/MS Excel is 32-bit or 64-bit from:

MS Office 2010:

File -> Help ->

File -> Help ->

Office 2013:

File -> Account -> About Access

File -> Account -> About Excel

Office 2016/365:

File -> Account -> About Access

File -> Account -> About Excel

In About window. If you see 32-bit, then your MS Office is 32-bit.

If you see 64-bit, then your MS Office is 64-bit.

You can identify the application from the Task Manager window also.

If the application is 32-bit, you will notice *32 after the EXE name in the process tab. If you can't see *32 after the EXE name, the application is 64-bit.

QuickBooks is a 32-bit application, and we must keep QODBC a 32-bit driver to work with QuickBooks and 32-bit applications; both will run under a 64-bit OS.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect; we call it QRemote. QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol.

The QuickBooks Enterprise Edition Read Only version of QODBC will not work with 64-bit applications nor with remote data sharing; that functionality was not part of our contract with Intuit, and those functions are only included in the version of the driver we sell.

QODBC Read-only edition for QuickBooks Enterprise does not include a QRemote License. So, after 30 days, you will not be able to use QRemote.

After 30 days of evaluation, if you try to connect and use QRemote Other than QODBC Licensed, you will notice an "Evaluation Expired. QRemote requires QODBC license." message.

So, if you want to use QRemote DSN, you must purchase the latest QODBC license.

Please click here to purchase a QODBC license.

OR

If you load the 32-bit version of MS Office on the machine, you won't have any problems using the driver we build for Enterprise Solutions.

To install the 32-bit version of MS Office on the machine. First, you need to uninstall the 64-bit version of MS Office.

Uninstallation of 64-bit MS Office:

To uninstall the 64-bit version of MS Office, search "Control Panel" from Start Menu & click "Control Panel" from the result.

The Control Panel is opened; click "Program and Features" to uninstall MS Office 64-bit.

From the program list, Search for "Microsoft Office" & click "Uninstall."

MS Office 2010:

Office 2013:

Office 2016/365:

After uninstalling MS Office 64-bit version, you need to install the 32-bit version of MS Office.

Installation of 32-bit MS Office:

MS Office 2019 / 365:

The 64-bit version is installed by default unless Office detects you already have installed a 32-bit version of Office (or a stand-alone Office app such as Project or Visio). In this case, the 32-bit version of Office will be installed instead.

To change from a 32-bit version to a 64-bit version or vice versa, you need to uninstall Office first (including any stand-alone Office apps you have, such as Project of Visio). Once the uninstall is complete, sign in to www.office.com and select Other install options, choose the language and version you want (64 or 32-bit), and then select Install.

Please refer to the following links.

Use the Office offline installer.

Download and install or reinstall Office 365 or Office 2019 on a PC

Deploy Office 2019 (for IT Pros)

MS Office 2010:

You can install 32-bit MS Office by double-clicking on setup.exe from MS Office 2010 setup folder.

Please ensure that the MS Office folder does not contain x64 in the folder name. If x64 is in the folder name, it is the 64-bit installer. It would be best if you used the 32-bit installer.

Office 2013:

In MS Office 2013, You need to navigate to the x86 folder & run setup.exe from MS Office 2013 setup folder.

If you run setup.exe from the main folder, it will install 64-bit MS Office.

Office 2016/365:

In MS Office 2016/365, You need to navigate to the Office folder & run Setup32.exe from MS Office 2016/365 setup folder.

If you run Setup.exe from the main folder, it will install 64-bit MS Office.

Problem Description:

I can access the database, but when I added another table & fetch table data, it generally hung up. Not sure if I need to change anything. Any thoughts?

Solution:

When accessing any table for the first time, QODBC will bring table-specific data to the optimizer file to increase query retrieval performance.

Once the optimizer is built, accessing the same table will be much faster compared to accessing it the first time or without using the optimizer.

Please refer to How Optimizer Works for more details.

If you see the QODBC icon (not hourglass) in System Tray, it means QODBC is connected to QuickBooks, and currently, there is no query executing.

When QODBC hangs up, or your application goes to Not Responding mode. You can check what QODBC is performing from Windows System Tray.

You will notice the QODBC icon in Windows System Tray showing hourglass.

If you want to see what QODBC is performing, then right-click on the QODBC icon & click "Driver Status Panel."

QODBC will open the UAC window, and please click on "Yes" in the UAC window to open the "Driver Status Panel."

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

If you see "Waiting On QuickBooks," It means QuickBooks is taking time to process the request.

If you want to permanently enable the "Driver Status Panel, " refer to How to view details on what QODBC is performing / Display Driver Status / Display optimizer Status.

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

If the Above Solutions don't work, please post a ticket to us, and we will investigate your issue further.

Also, refer to the following:
Troubleshooting - Cannot load Customer table records.

Suggestion:

Suppose you have a large company file with multiple years. QuickBooks and QuickBooks SDK would require more time to process your data required.

We would suggest splitting the company file to make it smaller. You can keep the historical data in one company file and the latest one in another.

This way, your company file will be smaller and with fewer records.

Refer to 

https://quickbooks.intuit.com/learn-support/en-us/other-questions/best-way-to-split-large-company-file/00/1081335

https://quickbooks.intuit.com/learn-support/en-us/account-management/create-a-new-company-file-from-the-existing-company-file/00/203213

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

Problem Description:

How can a payment be applied to multiple invoices?

Scenario 1

- 3 Invoices are created.

- A payment is received that covers 2 of the invoices, but not necessarily in the order in which they were created.

How can I apply the payment to the invoices of my choosing?

Scenario 2

- 2 invoices are created

- payment received in the amount higher than the two invoices

I want to apply the payment to the two existing invoices and then use the remaining unused amount for the next Invoice that will be created later. How can I achieve this?

Solution:

You have already received Payments & Invoices created. Now, You want to apply the received payment to the desired Invoice.

As you want to link the Payment with the Invoice, You need to insert records in the ReceivePaymentLine table.

As per Scenario 1, You have 3 Invoices created & A payment is received that covers 2 of the Invoices. Now, You want to apply the amount to the desired Invoice.

Please refer below steps for applying the payment to multiple Invoices using QODBC. I am using the MS Access application for this example. You can use your desired application.

First, please create a new MS Access database & link the Invoice, InvoiceLine, receive payment, and ReceivePaymentLine tables.

Please refer to How to Use QODBC with Microsoft Access for more details.

After linking the table, we need to execute the query in the MS Access query editor:

MS Access >> Create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Here, We first get the TxnID of the ReceivePayment using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, TotalAmount FROM ReceivePayment where CustomerRefFullName = 'Easley, Paula.'

We have details for Customer payments.

Now, We will get the Invoice details from the InvoiceLine table using the below sample query.

SELECT TxnID, CustomerRefListID, RefNumber, SubTotal FROM InvoiceLine where CustomerRefFullName='Easley, Paula'

We have details for Customer Invoices.

We will use the above details & apply a payment to the 2 Invoice# 1108 & 1109 using the below sample query.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24373-1639567874', 140.00 )

The payment was applied to Invoice# 1108.

We will apply a payment to Invoice# 1109 using the sample query below.

INSERT INTO ReceivePaymentLine (TxnID, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES ('2437F-1639567954', '24379-1639567907', 90.00 )

The payment was applied to Invoice# 1109.

Related Data Location:

TxnID = TxnID from the ReceivePayment table for the payment you received.

AppliedToTxnTxnID = TxnID from the Invoice table for which you want to apply for the payment.

AppliedToTxnPaymentAmount = Payment Amount which you want to apply for the Invoice.

Also, When the payment amount is higher than the Invoice amount & you have applied it to the Invoice. The remaining payment will be shown in the UnusedPayment or UnusedCredits fields of the ReceivePayment & ReceivePaymentLine table.

Problem Description:

How to use M-Files with QODBC?

Solution:

Please follow the below steps for using M-Files with QODBC.

1. Open M-Files Admin.

2. Expand the desired connection to M-Files Server in the left-side tree view.

3. In the left-side tree view, expand the document vault of your choice.

4. Still, expand the Metadata Structure (Flat View) node in the left-side tree view.

5. Select the Value Lists node for defining a value list to use an external database connection. The value list listing is opened in the right pane.

6. In the right-pane listing, right-click the value list of your choice and select "Properties" from the context menu.

7. Select the "Connection to External Database" tab & Enable the option "Use a connection to an external database to import and modify objects that reside in the external database.". Click the "Define..." button next to the OLE DB connection string (from the server) field.

8. On the Provider tab, select "Microsoft OLE DB Provider for ODBC Driver" from the list and click "Next >>."

9. Select "QuickBooks Data" DSN from the dropdown list & click "Test Connection."

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

Test Connection successful.

10. Click "OK" to close the Data Link Properties dialog.

11. Back on the "Connection to External Database" tab, enter the SELECT statement for retrieving properties from the database to the SELECT statement field & Click the "Refresh Columns" button to fetch the data defined in your SELECT statement to the Columns listing.

For Example Select * from Customer

12. Map the Source Column properties with properties in your M-Files vault (listed in the Target Property column).

QuickBooks Data is added to M-Files using QODBC. You can make changes to the application as per your requirement.

How to extract sp_reports using Microsoft Excel

Open Microsoft Query in Excel

Open Microsoft Excel, get external data from Microsoft Query via Data Menu -> "Get Data -> From Other Sources->From Microsoft Query" as below:

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.

It will take some time to get the DSN list:

In the "Choose Data Source" Window, Select the QuickBooks Data DSN you set up with the QODBC driver. This is one of our pre-installed DSN names or one that you have created.

Note: Uncheck option "Use Query Wizard to Created/edit Queries" since we do not need to import any tables here.

Note: Youed to Select the QuickBooks Online Data DSN.

C for using QuickBooks Online, reportlose the "Add Tables" window by clicking the "Close" button.

Press the "SQL" button to input your sp_report query as below. Here we take a BalanceSheetDetail report as an example:

sp_report BalanceSheetDetail parameters DateMacro = 'ThisMonthToDate'

Click the "OK" button to close the warning message:

Press Execute button to get results in Microsoft Query:

Return Records to Excel Spreadsheet

Select Menu File -> Return Data to Microsoft Excel to return records to Excel Spreadsheet.

Also, Refer:

How to use the QuickBooks Reporting Engine with QODBC

How to use the QuickBooks Reporting Engine with QODBC Online

Tags: QuickBooks Online, QBO, Excel, MS Query, QODBC Online, Sp_reports, Balance sheet detail

By default, QRemoteServer will start as soon as a user logs on to the system.

You can manually start QRemoteServer by the below steps:

You can begin to QRemoteServer from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

Windows 10:

Windows 7 or Windows 2008:

Windows 8.1 or Windows 2012:

You can locate QRemoteServer in Windows 8.1 or Windows 2012 by searching "QRemote Server for QODBC":

Click on QRemote Server for QODBC from the search result.

You can bring up the QRemote Server main window by double click the QRemote Server icon from the system tray.

Also, Refer:

QRemote Initialization Error, Error: Listening failed. Please restart application

QRemote Error "Could not connect to server/QRemoteServer."

How to turn On/Off Encryption on QRemote

How to remove QRemote from Startup

How to add QRemote to Startup

Launch QODBC Data Source Configuration

      Locate the QODBC Driver for the QuickBooks program group from Startup Program: Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

Create a New 32-bit 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 QRemote for QuickBooks option and click on the Finish button.

Configure the Created 32-bit DSN

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

·         Server: IP Address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

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

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

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

      After filling in all the details, click "Test Connection" to test the QRemote connection.

      The test connection is successful.

      Click "OK" to save the DSN configuration.

      New DSN created. Close the ODBC Manager by clicking "OK."

      The new QODBC DSN has now been created. To modify the DSN anytime in the future, use the Configure button.

Create a New 64-bit DSN

      Navigate to Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit

      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 QRemote for QuickBooks option and click on the Finish button.

Configure the Created 64-bit DSN

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

·         Server: IP Address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

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

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

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

      After filling in all the details, click "Test Connection" to test the QRemote connection.

      The test connection is successful.

      Click "OK" to save the DSN configuration.

      New DSN created. Close the ODBC Manager by clicking "OK."

      The new QODBC DSN has now been created. To modify the DSN anytime in the future, use the Configure button.

Also, refer to the following:

How to use QRemoteServer with multiple Windows users - Use a different port for each user.

How to create or configure a QRemote DSN

Troubleshooting - QRemote Error "Could not connect to server/QRemoteServer"

How to create or configure a QODBC DSN

Problem Description:

I am working in Excel. I have set up three pass-through queries and had them work on referencing cells for DateFrom and DateTo parameters. The queries were SummarizeColumnsBy by Month.

The queries suddenly stopped working, only returning Amount_1 and not Amount_1 through Amount_12.

If I manually put the date in the command text, all 12 months will return.

Solution:

Please ensure that you pass both the dates (i.e., start date and end date) in the parameter value. There should be 12 months difference between the two dates to get all the 12-month amount.

Also, please ensure that you pass the date in the correct format through the parameter.

Please use the below format & check again:

YYYY-MM-DD

For Example:

DateFrom: 2017-01-01

DateTo: 2017-12-31

Problem Description:

I am using QODBC for exporting QuickBooks reports in MS Excel. I am using the BalanceSheetStandard report for specific date ranges as below.

sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = {d'2020-01-01'},DateTo = {d'2020-06-30'}, SummarizeColumnsBy = 'TotalOnly'

I want to pass the date value in the parameter. So, I can execute reports for a specific date period without changing the query.

Do you know how to pass a value from Excel to the DateFrom and DateTo parameters?

Solution:

Please follow the below steps for using Prompted Date Ranges in MS Excel with sp_reports.

First, You need to export the report to MS Excel.

Please refer to how to create sp_reports using Microsoft Excel 2007 to export reports in MS Excel.

I have already exported the BalanceSheetStandard report to MS Excel.

On the "Data" tab, click the "Queries & Connections" button in the Excel sheet.

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

Navigate to the Definition tab. You can view the Connection string and Command text.

In the Excel command text, You need to replace the actual date value with the question mark for the parameter you want & save it by clicking the "OK" button.

For Example: sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = ?,DateTo = ?, SummarizeColumnsBy = 'TotalOnly'

Now, whenever you Refresh Data:

Excel will prompt for the parameter value.

Please use the below format for providing the date value.

YYYY-MM-DD

Please enter both parameter values one by one & click "OK."

The report data get refreshed with the parameter date value.

Problem Description:

I recently ran into an issue when querying the inventory for each lot number. In QuickBooks, there is a report called "Lot Numbers in Stock." You can click on the Lot Numbers on that report, and it opens a Quick View that shows the On Hand QTY for each Lot Number.

I've searched through the tables with the help of QODBC, and the only table I could find that appears to offer the information was the "BuildAssembly" table. However, the Quantity On Hand does not match what shows when I run the report in QuickBooks.

Could you please point me in the right direction to where I can query this information using QODBC?

Solution:

I am not sure How the "Lot Number In Stock" or "Serial Number In Stock" report is showing QuantityOnHand & which table it is using. You can verify QuantityOnHand from BuildAssembly through QuickBooks UI & QODBC, which will be identical.

The BuildAssembly table will show QuantityOnHand details when Assembly was built. It will not conduct the current Quantity as shown in the report.

Also, I'm sorry to inform you that the "Lot Number In Stock" or "Serial Number In Stock" report is not available through the Intuit SDK, so they are not available through QODBC.

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

Refer: How to export QuickBooks data not available in QODBC

Problem Description:

I am working on the transactions table but do not see a "class" field. How would I produce a transaction list that includes the class field?

For example, I can run a GL report in QBE 18 that includes the class field and wish to do the same with transactions.

What tables have the class field?

Could you please point me in the right direction to where I can query this information using QODBC?

Solution:

I'm sorry to inform you that the Class information in the Transaction table is not available through the Intuit SDK, so they are not available through QODBC.

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

Refer: How to export QuickBooks data not available in QODBC

You can use reports such as GeneralLedger, CustomTxnDetail, TxnDetailByAccount, TxnListByDate, etc.

For Example:

sp_report GeneralLedger shows Blank, TxnType, Date, RefNumber, Name, Class, Memo, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'All.'

Refer: To how to use the QuickBooks Reporting Engine with QODBC for more details.

Problem Description:

I have QODBC running on a terminal server.o

I can use 64-bit Excel to query QODBC via the "from other sources." I have set up a 64-bit remote DSN that talks to the user DSN.

I have written a test program in .net that connects to QODBC and successfully returns data.

When I run the same code within an Excel Add-in. I get the error that it cannot find the data source name:

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This occurs on my test application when I put in the wrong DSN. That is, it's not a 32/64-bit compatibility error, as you get a different message.

I can use QODBC in every way other than running as an Excel Add-in. Can you please advise where I have gone wrong?

Solution:

The data source you are attempting to connect to does not exist on your machine.

On Windows, data sources are created by the Microsoft ODBC Administrator. The available System data sources are listed in the System DSN tab. The User data sources available for the currently logged-on user are listed in the User DSN tab.

OR

You do not have permission to access the location where the data source is stored.

On Windows, the user account attempting to access the data source needs sufficient permissions to read the registry key where the data source is stored. System data sources are stored under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. User data sources are stored under HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.

OR

You are using a DSN-less connection on Windows that specifies a driver whose architecture differs from the applications. For example, you are using a 64-bit application, but the driver you specify in the connection string is only available as a 32-bit library. The application and ODBC driver architecture must be the same.

Please refer below-mentioned link for more details:

[Microsoft][ODBC Driver Manager] Data source name not found, and no default driver specified

Problem Description 1:

How to add QRemote to ALL users Startups?

Solution 1:

It would be best to enabled QRemote Server to auto-start from the QRemote Server.

Open the QRemote Server from the Start Menu or the Windows System Tray (near the system clock).

Navigate to File -- Settings.

Switch to the "Advanced" tab.

Enable "Start QRemote Server on system startup"

To Remove QRemote from startup.

Navigate to File -- Settings.

Switch to "Advanced" tab.

Uncheck "Start QRemote Server on system startup"

Problem Description 2:

How to manually add the QRemote Server to the user's Startup?

Solution 2:

You can manually add the startup folder of the user who will be using the QRemote/QuickBooks.

Please create a QRemote Server shortcut: Start >> All Programs >> QODBC Driver for use with QuickBooks >> Right-click on QRemote Server for QODBC & click on "Send to" and select the "Desktop (create shortcut)" option.

Now, Copy the QRemote Server shortcut from the desktop. Right-click on the QRemote Server shortcut & click "Copy."

Now, Explore the user's startup folder who will be using the QRemote/QuickBooks.

Start >> All Programs >> Right-click on "Startup" & click on "Explore."

Alternative.

Open the run command (Use the Windows + R keyboard shortcut.) and type shell: Startup & click "OK."

The startup folder is opened.

Paste the shortcut file from the desktop & place it in the Startup folder.

Now QRemote Server will start for the selected user on startup (i.e., On System startup, Restart).

Problem Description 3:

How to manually start QRemoteServer?

Solution 3:

You can start QRemoteServer from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

After starting QRemoteServer, You can verify the QRemote configuration by clicking here.

Also, Refer:

Troubleshooting - Listening failed. QRemote Server is shutting down in 15. Sec

Troubleshooting - How to turn On/Off Encryption on QRemote

Problem Description:

I'm trying to extract QuickBooks budget data to SQL for reporting purposes. However, I don't see any tables that would hold the budget. My research is telling me this may not be possible. Can someone please tell me how I can extract Budget data?

Solution:

We are sorry to inform you that the Budget table is unavailable through the Intuit SDK, so budget-related information is unavailable through QODBC.

QODBC is an ODBC driver for QuickBooks. QODBC uses the Intuit QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose the field/table/feature to the application in SDK, QODBC could not do it either.

Refer: How to export QuickBooks data not available in QODBC

You can refer Budget related reports for Budget details.

You can refer below reports.

sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2015, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2015, BudgetCriterion = 'Accounts,' SummarizeBudgetColumnsBy = 'Date,' SummarizeBudgetRowsBy = 'Account'

Refer: To how to use the QuickBooks Reporting Engine with QODBC for more details.

Problem Description:

I need a report by a particular Customer showing the beginning balance, payments made by the Customer, invoices with details showing the expense, and the ending balance. QuickBooks does not provide enough detail in the customer statements so parents can read them. I would also like to be able to email the statement to an email address.

Solution:

You need to query different tables & reports to get the required data. Once you get the data, you need to combine all data.

You need to query the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine table for a particular Customer.

You can use MS Excel for this & can write VBA code in MS Excel which will query the above table & report for particular Customers & store results on a separate sheet. Once you get the impact on the separate sheet, you need to combine all sheet data & paste it into the new sheet.

tatWe have created seven full sheets (i.e., Sheet0 to Sheet6). Sheet0 will be input form & Sheet1 to Sheet5 will contain data from the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine table for particular Customers, and Sheet6 will combine data from Sheet1 to Sheet5. We have created a sample MS Excel macro that will fetch the required data.

Sheet 0 will be the input form. To use this, you need to enter Customer FullName, StartDate & EndDate & click on the "Generate Report" button. In this example, I have used "Abercrombie, Kristy: Remodel Bathroom" as Customer FullName & 1/1/2016 as the start date, and 12/31/2022 as the end date.

On clicking the "Generate Report" button, we will query the report & table for a particular Customer & store results in Sheet1 to Sheet5.

Sheet 1 will contain the result of the CustomerBalanceDetail report.

The query used for fetching data for Sheet1 is:

sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account, Amount, RunningBalance as Balance parameters DateFrom = {d'2016-01-01'}, DateTo = {d'2022-12-31'}, EntityFilterFullNames= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 2 will contain the result of the InvoiceLine report.

The query used for fetching data for Sheet2 is:

Select CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, AppliedAmount, Memo, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 3 will contain the result of the CreditMemoLine report.

The query used for fetching data for Sheet3 is:

Select CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, TotalAmount, Memo, CreditMemoLineItemRefFullName, CreditMemoLineDesc, CreditMemoLineQuantity, CreditMemoLineRate, CreditMemoLineAmount from CreditMemoLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 4 will contain the result of the ReceivePaymentLine report.

The query used for fetching data for Sheet4 is:

Select CustomerRefFullName, TxnDate, RefNumber,'", ' TotalAmount, Memo, UnusedPayment, UnusedCredits, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, AppliedToTxnTxnType, AppliedToTxnTxnDate, AppliedToTxnRefNumber, AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Sheet 5 will contain the result of the CheckExpenseLine report.

The query used for fetching data for Sheet5 is:

Select PayeeEntityRefFullName, TxnDate, RefNumber,'", 'Amount, Memo from CheckExpenseLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and PayeeEntityRefFullName= 'Abercrombie, Kristy: Remodel Bathroom.'

Note: The ReceivePaymentLine & CheckExpenseLine table does not have the TermsRefFullName field. To maintain the data structure in Sheet6, which contains combined data, I have added '" ' in the query of ReceivePaymentLine & CheckExpenseLine.

Now, We have the required data in Sheet1 to Sheet5. We will combine these data into Sheet 6.

Sheet 6 will contain the combined result of Sheet 1 to Sheet 5.

Please click here to download Sample Macro.

Application Source Code:

Private Sub CommandButton1_Click()

Dim cn As Object
Dim rs As Object

Dim strCon As String
Dim strSQL, CustName As String, Date1 As Date, Date2 As Date

Dim mainworkBook As Workbook

Set mainworkBook = ActiveWorkbook

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

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Dim a As String

Date1 = ThisWorkbook.Sheets("Sheet0").Cells(7, 3).Value
Date2 = ThisWorkbook.Sheets("Sheet0").Cells(8, 3).Value
CustName = ThisWorkbook.Sheets("Sheet0").Cells(6, 3).Value

strSQL = " sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account,Amount, RunningBalance as Balance " & _
"parameters DateFrom = " & fncqbDate(Date1) & ", DateTO = " & fncqbDate(Date2) & ", EntityFilterFullNames=  '" & CustName & "'  "


Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet1").Cells.ClearContents
Sheets("Sheet1").Cells.ClearFormats
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,AppliedAmount,Memo,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount from InvoiceLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet2").Cells.ClearFormats
Sheet2.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,TotalAmount,Memo,CreditMemoLineItemRefFullName,CreditMemoLineDesc,CreditMemoLineQuantity,CreditMemoLineRate,CreditMemoLineAmount from CreditMemoLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet3").Cells.ClearContents
Sheets("Sheet3").Cells.ClearFormats
Sheet3.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,'""',TotalAmount,Memo,UnusedPayment,UnusedCredits,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnTxnType,AppliedToTxnTxnDate,AppliedToTxnRefNumber,AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet4").Cells.ClearContents
Sheets("Sheet4").Cells.ClearFormats
Sheet5.Range("A1").CopyFromRecordset rs
rs.Close

strSQL = "select PayeeEntityRefFullName,TxnDate,RefNumber,'""' ,Amount,Memo from CheckExpenseLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and PayeeEntityRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet5").Cells.ClearContents
Sheets("Sheet5").Cells.ClearFormats
Sheet6.Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

Set cn = Nothing


Sheets("Sheet6").Cells.ClearContents
Sheets("Sheet6").Cells.ClearFormats
ActiveWorkbook.Sheets("Sheet1").Select

  Dim N As Long
  N = Cells(1, 8).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A1:H2")
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 0).Select
  Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Sheets("Sheet2").Select

  
  N = ThisWorkbook.Sheets("Sheet2").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet2").Range("A1:N" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet3").Select
  
  N = ThisWorkbook.Sheets("Sheet3").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet3").Range("A1:L" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet4").Select
  
  N = ThisWorkbook.Sheets("Sheet4").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet4").Range("A1:M" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet5").Select
  
  N = ThisWorkbook.Sheets("Sheet5").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet5").Range("A1:E" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet1").Select

  
  N = ThisWorkbook.Sheets("Sheet1").Cells(1, 8).End(xlDown).Row
  If (N > 4) Then N = N - 2
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A" & N & ":H" & N + 1)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


End Sub

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function
Function fncqbDate(myDate As Date) As String

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

End Function

Problem Description:

How to Auto Add relationship Between two tables in QlikSense Desktop using QODBC?

Solution:

Please create a new application in QlikSense Desktop & connect to QuickBooks Data through QODBC. Please refer to How to use QlikSense Desktop with QODBC for connection detail.

I am assuming that you have referred above link & connected to QuickBooks from QlikSense Desktop using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the QlikSense Desktop & click the "Add data" button. In this example, I am selecting the Customer & Invoice table.

Adding data is in progress.

The Customer & Invoice table data is added to the QlikSense Desktop.

Please click the Relationship icon to associate table data according to the recommendation.

The relationship is automatically added between the Customer & Invoice table.

Please click on the join icon to see the relationship details.

You can see that the Customer table's "ListID" is related to the Invoice table's "CustomerRefListID" field.

Please click the "Load data" button on the QlikSense Desktop.

Loading data is in progress.

The data is loaded successfully. Click on the "Edit the sheet" button to add data to the sheet.

The empty sheet is open in the QlikSense Desktop. Click on the "Fields" icon to add table fields to the sheet.

In this example, I have added the Customer table's Phone, Email field & Invoice table's CustomerRefFullName, RefNumber, and SubTotal field in the sheet. Please click on the "Done" button to finish editing the sheet.

QlikSense Desktop displays data in the sheet.

Now, You can perform any operation on table data using QlikSense Desktop.

To use QlikSense Desktop with QODBC, Please follow the below steps.

Open QlikSense Desktop & click on the "CREATE A NEW APP" button.

Enter the app name and click the "Create" button.

A New application in QlikSense Desktop was created successfully. Click "Open app" for the opening application.

The application has two options: "Add Data" & "Data Load editor." In this example, I am using the "Add Data" option. Click the "Add Data" button to update QuickBooks Data to QlikSense Desktop.

QlikSense Desktop will show a list of the data source. Please select the "ODBC" data source.

In Create new connection (ODBC) windows select "System DSN" & enable the "32-bit" option and select DSN & click on the "Create" button. You can change DSN as per your configuration. I am connecting to the default DSN, "QuickBooks Data," in this example.

The connection was made with QuickBooks through QuickBooks Data DSN & you can see the list of tables windows the list of tables available.

Select the table you want to add to the QlikSense Desktop & click the "Add data" button. In this example, I am selecting the Customer table.

Adding data is in progress.

The customer table data is added to the QlikSense Desktop. Now, You can perform any operation on table data using QlikSense Desktop.

You can view table data by double-clicking the "Customer" table icon.

Please click the "Load data" button on the QlikSense Desktop.

Loading data is in progress.

The data is loaded successfully. Click on the "Edit the sheet" button to add data to the sheet.

The empty sheet is open in the QlikSense Desktop. Click on the "Fields" icon to add table fields to the sheet.

In this example, I have added the Customer table's FullName, CompanyName, TermsRefFullName, Phone, and Email fields on the sheet. Please click on the "Done" button to finish editing the sheet.

QlikSense Desktop displays data on the sheet.

Now, You can perform any operation on table data using QlikSense Desktop.

Also, Refer:

How to Auto Add relationship Between two tables in QlikSense Desktop using QODBC

Problem Description:

We have a setup connecting a PHP application to QuickBooks with QODBC. This works correctly using odbc_connect.

However, if we try to connect using PDO instead, we get 500 Internal Errors. See the code below:

try{
$conn = new PDO ("odbc:QuickBooks Data QRemote", "", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "SELECT * FROM Customer";
#Perform the query

$conn->beginTransaction();
$stmt = $conn->prepare($query);
$stmt->execute();
$conn->commit();
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($res);

die(json_encode(array('outcome' => true)));
}
catch(PDOException $ex){
die(json_encode(array('outcome' => false, 'message' => 'Unable to connect')));
}

Solution:

To resolve this issue, Please download & install QODBC's latest version, V323 or above, by clicking here.

There is a known issue with the PDO connection when connecting to QRemote using PDO; it does not return the result if you have a lot of records on the table or are fetching all columns (i.e., Select * from TableName).

So instead of selecting all columns using wildcard character *, you need to specify a limited column name & execute the query.

For Example:

Select ListID, FullName, CompanyName, Email, and Balance from the Customer

Select TxnID, CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, Subtotal, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount FROM InvoiceLine

If your table contains a lot of data, please use the date filter in your query below.

For Example:

Select TxnID, CustomerRefFullName, TxnDate, RefNumber, TermsRefFullName, Subtotal, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount FROM InvoiceLine TxnDate >= {d'2017-06-01'} and TxnDate <= {d'2017-06-30'}

After installing the latest QODBC version, you should able to run the PDO script.

Please use below sample script.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>QODBC PHP Script To Display SQL Results</title>
</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">
<table border="0" border="0" bgcolor="lightgreen" bordercolor="black" cellpadding="0" cellspacing="0">
<tr>
<td>
<table border="2" bordercolor="black" bgcolor="white" cellpadding="5" cellspacing="0">
<caption align="top">QODBC PHP Script To Display SQL Results</caption>
<?php
$conn = new PDO ("odbc:QuickBooks Data QRemote", "", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$query = "SELECT TxnID,CustomerRefFullName,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,TermsRefFullName,DueDate,Subtotal,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount FROM InvoiceLine";
$stmt = $conn->prepare($query);
$stmt->execute();
$colcount = $stmt->columnCount();
foreach ($stmt->fetchall(PDO::FETCH_BOTH) as $row)
{
  echo '<tr>';
  for($i = 0; $i < $colcount;  $i++ ) {
		echo '<td>';
		print_r($row["$i"]);
		echo '</td>';
  }
  echo '</tr>';
}
?>
</table>
</td>
</tr>
</table>
</body>
</html>

Problem Description:

How to use BarTender with QODBC?

Solution:

Please follow the below steps for using BarTender with QODBC.

Please start BarTender & create a new document by clicking "Start a new BarTender document...".

Select the "Blank Template" option & click "Finish." You can use "Existing BarTender Document" if you have an existing BarTender document & want to use it.

The new BarTender document is open. Click the "Database" icon to add ODBC connection to QuickBooks.

On the Database Setup Wizard page, select "ODBC Connection" & click "Next."

Select "QuickBooks Data" data source and click "Next."

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

Now, please select the table which you want to use with BarTender.

Double-click on the table name to add it to BarTender & click "Finish." In this example, I have added a Customer table.

You will notice the "Loading Columns" message by clicking "Finish."

Database connection properties are stored in the document. Now, Click "Record Browser" for browsing records.

The Record Browser shows customer table records. Click "OK" to return to the document.

The customer table data is added to the document. Click "Data Sources" for the list of available table fields.

We need to add control to display table data in the BarTender document. In this example, I am adding a single-line text object.

The "Text Object" is added to the document.

Now drag any table field in the object which you want to show. I have added the FullName field to the Text Object in this example.

You will notice that Customer FullName is loaded into the BarTender document.

On clicking the "Next" icon, it will show the following record details.

QuickBooks Data is added to BarTender using QODBC. You can make changes to the application as per your requirement.

Problem Description:

How to use dBase Plus with QODBC?

Solution:

Please follow the below steps for using dBase Plus with QODBC.

Please start dBase Plus & create a new form by clicking "New Form."

Select the "Designer" option in the form, creating a window.

The form window is open.

Now go to the "ADO Access" tab on the Component palette & Drop an "ADODatabase."

The ADODatabase is added to the form. Configure the Database connection using the "Setting" icon in front of the "connectionString" label from the ADODatabase property window.

In the Build Connection String window, Select the "Connection statement:" option & click "Data Link Properties."

In the Data Link Properties window, Select the "Connection" tab & choose the "Use data source name" option & select "QuickBooks Data" from the drop-down box and click "Test Connection."

Please Note: If you have a 64-bit application, you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote," as shown in the screenshot below.
Please refer: To how to configure QRemote.

You will notice the "Test connection succeeded" popup message on a successful database connection; click "OK" to close the popup message.

Click "OK" to finish the configuration.

The database connection is added. Please set the "Active" property to "True."

The ADODatabase "Active" property is set to "True." Now go to the "ADO Access" tab on the Component palette & Drop an "ADOTable."

In this example, I have used the Customer table. In this example, I have used the Customer table. In this example, I have used the Customer table. Now, Please input the Table Name from the "tableName" property label from the ADOTable property window. The adorable is added to the form.

The table name is added. Please set the "Active" property to "True."

Now, Please refresh the dBase Plus connection by switching between the "Navigator" & "Form" windows & you will see the Customer table field added in the "ADOTable."

In the Fields tool window, drag a table field to the form. In this example, I have added the "Name" field of the Customer table.

QuickBooks Data is added to dBase Plus using QODBC. You can make changes in the application as per your requirement & run the application.

Problem Description:

I am using QODBC to access QuickBooks Data. I have linked all QuickBooks tables using QRemote DSN.

I want to use the Customer table to be Optimizer OFF and the rest of the tables with Optimizer ON.

How can I achieve this?

Solution:

It would be best if you had the Customer table with Optimizer OFF and the rest of the tables to be Optimizer ON. It would be best if you used a separate DSN with Optimizer Off for the Customer table.

You need to create a new QODBC DSN through Configure QODBC Data Source.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

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" option and click on the "Finish" button.

Name your new DSN as "QuickBooks Data-OPT-OFF."

Switch to the "Optimizer" tab/window and uncheck the "Use Optimizer" option & Click "Apply" followed by "OK" to save the DSN configuration.

New DSN created. Close the ODBC Manager by clicking "OK."

After this, You need to create a new QRemote DSN through Configure QODBC Data Source 64-bit.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit.

Note: If you are using a 32-bit application & using QRemote, then you need to open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source & create a new QRemote DSN from 32-bit ODBC Manager.

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 "QRemote for QuickBooks" option and click on the "Finish" button.

Name your new DSN as "QRemote-OPT-OFF."

·         Server: IP Address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

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

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

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

Click "OK" to save the DSN configuration.

Start QuickBooks login to Company File as Admin. Start QuickBooks login to Company File as Admin. New DSN created. Close the ODBC Manager by clicking "OK."

Switch to your MS-Access application. Right-click on Linked tables and select "Linked Table Manager." I have linked all tables in the MS Access Database using the "QuickBooks Data" DSN with the Optimizer ON option.

From the list of tables, select Customer table & enable the "Always prompt for a new location" option, and Click "OK."

From the DSN list, select the newly created DSN from "Machine Data Source," Instead of using "QuickBooks Data 64-bit QRemote", Select "QRemote-OPT-OFF."

The linked table has been refreshed.

Customer table DSN is changed to "QRemote-OPT-OFF" from "QuickBooks Data 64-bit QRemote". Close "Linked Table Manager" by clicking the "Close" button.

This way, MS Access will create a new Connection to QuickBooks Data-OPT-OFF (Optimizer Off - when accessing the Customer table). For the rest of the tables, MS Access will use QuickBooks Data (Optimizer On).

Problem Description:

I'm having trouble finding where an Item's serial numbers are stored in the QuickBooks database. I have gone through your table schema online here http://doc.qodbc.com/qodbc/usa/, but I cannot find where the serial numbers for a given inventory item are stored.

Can you please advise?

Solution:

You can find SerialNumber or LotNumber from the Transaction module you have assigned.

For Example, if You have assigned SerialNumber or LotNumber in the Invoice, then you need to refer InvoiceLine table.

Below is the list of table & column which contains the serial number.

SerialNumber Details:

LotNumber Details:

Problem Description:

I am using QODBC for creating Invoices in QuickBooks. I am using CustomField in QuickBooks.

It works fine if I insert null strings into the two custom fields like this.

INSERT INTO InvoiceLine ( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache ) VALUES ( '8000001B-1436399889', 'TW Markup,' 15, 0.26, '20000-1051544872', 1 )

INSERT INTO Invoice ( CustomerRefListID, ARAccountRefListID, RefNumber, CustomFieldDBInvoice, CustomFieldFinalBuyer, Memo, TemplateRefListID ) VALUES ( '80000CFB-1334284223', '3A0000-1121110183', '22111', '', '', 'Import Date: 11/23/2017', '80000021-1483576709' )

But if I include any characters in the text string for either custom field, like this, I get the attached error.

INSERT INTO InvoiceLine ( InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache ) VALUES ( '8000001B-1436399889', 'TW Markup,' 15, 0.26, '20000-1051544872', 1 )

INSERT INTO Invoice ( CustomerRefListID, ARAccountRefListID, RefNumber, CustomFieldDBInvoice, CustomFieldFinalBuyer, Memo, TemplateRefListID ) VALUES ( '80000CFB-1334284223', '3A0000-1121110183', '22111', 'x,' 'x,' 'Import Date: 11/23/2017', '80000021-1483576709' )

Below is the error in the QODBC & QuickBooks SDK logs.

Error: 3180 - There was an error when saving a data extension named "Final Buyer."QuickBooks error message: Attribute with the specified name already exists.

20171123.123634 E 11176 DataExtStorage::DoAdd Error saving: data extension named "Final Buyer."QuickBooks error message: Attribute with the specified name already exists.

Solution:

You will face this issue when you set the default value in the Custom Fields.

The default strings take precedence over the new values set in the insert statement & issue happens.

If you edit the custom fields in the Invoice from QuickBooks Application, it will ask to confirm the edit.

You must answer 'No' to the next question about having this "new information appear next time."

If you answer 'Yes,' it will set the defaults in the customer record and prevent text strings from being written to those fields in the Invoice.

Problem Description:

We have installed the QODBC and can read the tables in the QODBC Test Tool. However, after doing the initial read as an admin to QuickBooks, we cannot read the tables as the QuickBooks QODBC user.

Our CFO is uncomfortable with leaving the admin user logged in on another machine for this purpose.

Please, can you show me how to move forward?

Solution:

I think you have selected Admin user in the Integrated Application Preference at the time of certificate acceptance in QuickBooks.

Selecting an Admin user in the certificate acceptance process in QuickBooks is unnecessary. You need to log in as Admin in your company file & try to connect with your application. You need to select the User Name in the certificate and accept the application integration certificate. You may remove the certificate or change the user associated with the auto login.

Follow the steps below for using another user account on the integrated application configuration.

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

Switch to the Company Preferences tab, Select "FLEXquarters QODBC," and Click "Remove."

2. log in to your company file as Admin, Connect with your application, and Accept the Certificate. You need to select the User Name at thcertificateertificate and accept the process you want to certificate. If you authorize QODBC for the first time, select the appropriate user from the list.

You need to accept the certificate for each Windows User & for each appliccertificate you want to use with QODBC.

Problem Description:

Our company uses QuickBooks 2018 contractor edition, which allows only one user simultaneously.

Yesterday, I set up QODBC with an admin account. Our finance team leader always uses this account.

Therefore, the QODBC driver can't connect to QuickBooks today. It has 80040437. The user you try to log in with is already logged in from another machine.

The team leader accesses from her work machine, and QODBC is running on a QuickBooks Server machine.

Could you advise how we can solve this problem?

Solution:

Scenario: QuickBooks is running, and a QuickBooks user is logged in to QuickBooks Company File:

When you connect to QuickBooks using your Application through QODBC, at that time, QODBC will connect to QuickBooks using QuickBooks SDK & log in to QuickBooks with the "User," which is currently logged in to Company File. The user specified in the Integrated Application Preference is ignored.

Scenario: QuickBooks is not running, and you have configured QODBC to auto-login to QuickBooks:

When you connect to QuickBooks using your Application through QODBC at that time, QODBC will connect to QuickBooks using QuickBooks SDK & start QuickBooks in the background with & log in to QuickBooks with the "User" which you have selected in the Integrated Application Preference at the time of certificate acceptance in QuickBooks.

So when the same user has already login to QuickBooks Application & you try to connect to QuickBooks using your Application through QODBC with the same user, you will face the above issue.

You are trying to log in to QuickBooks Application using the same user from two locations (i.e., 1st from QuickBooks Application in the foreground & 2nd through QODBC in the background.) which is impossible.

You can ask the user (who is also associated with the QODBC Integrated application) to log off from QuickBooks.

OR

You can use another user account on the integrated application configuration.

Follow the steps below for using another user account on the integrated application configuration.

Change Integrated Application access user from QuickBooks UI via Edit Menu -> Preferences -> Integrated Applications -> Company Preferences Tab.

Switch to the Company Preferences tab, Select "FLEXquarters QODBC," and Click "Properties."

Integrated Application shows Access rights for the User.

You can change Access rights for the User by selecting another user account from the dropdown list & clicking "OK," and closing Integrated Application Preference & checking again.

If you are connecting for the first time, you will get the below option at the time of connection. You need to select the User Name at the time of certificate and accept the process by which you want to use it.

You need to accept the certificate for each Windows User & for each application you want to use with QODBC.

Problem Description:

I am trying to set up the QODBC Driver on a machine running Windows Server 2016. Previously, I have had it set up and working fine on my local Windows 10 machine, but I have been tasked with moving it. I have followed the instructions as I did previously. However, when I go to the configure ODBC connection (32-bit or 64-bit), switch to System DSN, and then choose "Add," the option for QRemote for QuickBooks is not there. I only have the options seen in the attached screenshot. Why is it not showing up? I can see the QRemote running as usual in the task area, and everything there looks normal when I load it.

Solution:

The .Net Framework 2.0 or above is required for using QRemote DSN.

Normally, QODBC will auto-detect if .Net Framework is installed and, if not, will prompt you for installation.

Please install QODBC again, and when prompted to install .Net Framework, please complete the installation. At the time of QODBC installation, you will get the below message for downloading the .Net Framework. Please click on "Download & install this feature" to download & install the .Net Framework.

On Windows Server, You can enable .Net Framework through Server Manager.

In Server Manager, click Manage and select Add Roles and Features to start the Add Roles and Features Wizard. On the Select installation type screen, select Role-based or feature-based installation. Select the target server. On the Select features screen, check the box next to .Net Framework 3.5 Features.

The .Net Framework download is in process.

The .Net Framework installation is in process.

The .Net Framework installation is finished. Please click "Close" & restart the machine.

Please reinstall QODBC & check again. QRemote DSN will now be available for use.

As of Date: 2007-07-25

QuickBooks can handle a maximum of 2 billion transactions. The maximum number of transactions is limited more by your computer's disk space and memory than by QuickBooks.

Each list in QuickBooks has a maximum number of items it can contain, as shown in the following table:

Note: Some of the items mentioned in the chart above are not available in earlier versions of QuickBooks.

You can see the total number of customers, items, and names in your company data file by pressing Ctrl+1 in QuickBooks to open the Product Information screen.

Practical limitations

QuickBooks is typically designed for small businesses with 20 or fewer employees and annual revenue of fewer than two million dollars. The ideal use of QuickBooks is to keep at least two years of complex transactions in a company data file so that you can run comparative reports and have prior-year project information.

The rate of growth of QuickBooks company data files varies significantly from company to company. How quickly a file grows depends on the number of transactions, the amount of information entered per transaction, and the number of "links" per transaction. Since businesses track different information, there is no "average" or "typical" data file size.

For example, someone who enters 500 1-line invoices per month might find their data file smaller than another person who enters 100 5-line invoices per month. In comparison, someone who usually receives five separate payments per invoice would have a larger file than someone who receives only one payment per invoice.

To estimate if QuickBooks is right for your small business, take the average number of monthly transactions (remember, an invoice, payment, and deposit would be three separate transactions, and a bill and bill payment would count as two) and multiply by 2 KB to determine how much the data file will grow each month.

For example, if your company enters an average of 300 transactions per month, the data file would grow approximately 600 KB per month (300 x 2 KB = 600 KB) or 7200 KB per year (600 KB x 12 = 7200 KB). If the annual data file size is less than 15,000 KB, then QuickBooks should be more than sufficient for the company. (Present QuickBooks users can check the size of their QuickBooks file by pressing Command+1 on the keyboard.)

Note: The 2 KB multiplier does not include list information (which will grow as new names are added to the file) and is only an approximation that may not be a suitable estimate for all QuickBooks company data files.

If the performance of QuickBooks slows down, or you decide that you do not want to keep detailed transactions for prior years, you can condense a QuickBooks company data file. For QuickBooks 2007 - 2006 users, condensing the company data files is known as "Cleaning up data." For more information on cleaning up the company data file, refer to the article Cleaning up data. For QuickBooks 2005 users, condensing the company file is known as "archiving and condensing data." For more information on archiving and condensing the company file, refer to the article Archiving and condensing data.

QuickBooks Enterprise Solutions can handle a maximum of 2 billion transactions. The maximum number of transactions is limited more by your computer's disk space and memory than by QuickBooks.

Each list in QuickBooks Enterprise Solutions has a maximum number of items it can contain, as shown in the following table:

As of Date: 2007-03-29

For QuickBooks users within the USA
With Microsoft's release of Windows Vista, we wanted to ensure that all our users are informed about using QuickBooks on Windows Vista. First and foremost, USA versions of QuickBooks 2006 and earlier are not supported on Windows Vista. To use QODBC on Windows Vista, you must run QuickBooks 2007 Release 5 or higher (use Ctrl-1 while in QuickBooks to check your release level). R5 can be found on Intuit's website under QuickBooks Product Updates at: http://www.quickbooks.com/support/index/ndxw_16_update.html.

For QuickBooks users outside of the USA
For non-USA versions of QuickBooks, this will mean that users will not be able to run QuickBooks and QODBC on Windows Vista until they upgrade to QuickBooks 2008 (or 2008/09 or higher) and QODBC v9. Because of the SDK release's limitations in QuickBooks outside the USA, QODBC cannot be used under the Microsoft Vista operating system unless QuickBooks 2008 or later is used.

You can view the QuickBooks announcements made regarding Windows Vista outside the USA for your region at:
Australia/New Zealand/Singapore: http://www.quicken.com.au/support/vistaUpdate.aspx
United Kingdom (UK): http://www.quickbooks.co.uk/store/en/quickbooks/vista_faq.jsp?pd=vista
Canada: http://support.intuit.ca/quickbooks/vista_faqs.php?pd=vista

QODBC v7.00.00.214
To use QODBC under Windows Vista, you must also update to v7.00.00.214 (or higher)! QODBC Version 7.00.00.214 contains all the pieces necessary to be Windows Vista compliant. The main changes were to move the ODBCINST.INI to "C:\Documents and Settings\All Users\Application Data\QODBC Driver for QuickBooks\ODBCINST.INI." All that is used for the global settings for QODBC. All users should be able to read and write from there.

The QODBCLOG.TXT Log file has been moved to C:\Documents and Settings\useraname\Application Data\QODBC Driver for QuickBooks (per user).

The new default for the QODBC optimizer file is "%UserProfile%\QODBC Driver for QuickBooks\Optimizer." New users will get that. Old users can change their path to that. With the %UserProfile% in the path, the optimizer .opt files will be saved in the user's folder (C:\Documents and Settings\useraname\Application Data\QODBC Driver for QuickBooks).

Changes were also made to disable the Test Connection and Optimizer sync buttons when EditQDSN is started in elevated mode. (The Intuit qbXML SDK cannot be called from an elevated application under Vista) A help link has been added to explain why.

And lastly, a helper .exe called FQQBVSAV.EXE was added to allow a non-admin user to save settings to the System DSN after prompting for elevation.

IMPORTANT NOTE: To see how to install QODBC and connect to QuickBooks 2007 for the first time using Microsoft Windows Vista, click here: How do I install and connect QODBC using Microsoft Vista?

QuickBooks qbXML SDK/Windows Vista Compatibility and QODBC
QODBC v7.00.00.214 (or higher) now works correctly with USA editions of QuickBooks 2007 R5 (or higher) and QuickBooks Enterprise Solutions 7.0 R5 (or higher) with the following caveats:

QODBC v9.00.00.253 (or higher) now also works correctly with all non-USA editions of QuickBooks 2008 (2008/09 or higher) and QuickBooks Enterprise Solutions 8.0 with the following caveats:

• The Vista user account must have its User Account Control (UAC) set to On (as recommended by Microsoft).
• QuickBooks and the QODBC application accessing the SDK should be run with standard user permissions (NOT elevated to run as administrator).
• QuickBooks Web Connector 1.0 and QuickBooks Remote Data Sharing 3.0 are not supported on Vista; new versions of each that work on Vista will be released soon.

For further information about Microsoft Windows Vista, QuickBooks, and the QuickBooks qbXML SDK, see this article on the IDN website.

New Features and Integrated Services Solve Small Businesses' Most Important Problems

MOUNTAIN VIEW, Calif.--(BUSINESS WIRE)--Intuit Inc. (Nasdaq: INTU) today announced the availability of QuickBooks® 2007. The new line of products and services focuses on what matters most to small businesses: making day-to-day tasks even easier to complete while adding deeper functionality for specialized and growing companies.

QuickBooks 2007 includes powerful new tools and services that help small businesses succeed in activities beyond accounting. For the first time, Intuit is offering customers integrated tools to market their business and attract new customers, as well as new services that extend QuickBooks' functionality - from accepting credit cards to managing timesheets and running payroll. Combined, these create a robust ecosystem of distinctive solutions to help existing and new-to-the-world businesses be successful.

"No one serves small business and accountants better than Intuit," said Brad Smith, senior vice president of Intuit's Small Business Division. "For nearly 15 years, we have remained laser-focused on ease, continuously improving the market-leading small business management platform. At the same time, we have developed a powerful mix of integrated services that our customers asked us for to help better connect them with their customers, vendors, partners, and employees."

Solutions Beyond Accounting

QuickBooks offers more than 25 products and services beyond core accounting functionality. From QuickBooks Merchant Service for Web Stores to Intuit Small Business Payroll services, customers can leverage their existing QuickBooks data to manage better and market their companies. QuickBooks also offers easy solutions to process payments, such as the QuickBooks Credit Card Processing Kit and the new QuickBooks Time Tracker to easily collect and manage employee timesheets on the Web.

The latest addition to Intuit's comprehensive small business ecosystem is a series of popular marketing tools from Google. The new integrated online tools include Google maps, Google AdWordsT, and the QuickBooks Product Listing Service, designed to solve another critical small business concern - attracting new customers online.

Customers can easily find more than 400 add-on applications that solve specific needs, from sales force automation to eBay integration, through the QuickBooks Solutions Marketplace at: http://www.marketplace.intuit.com.

"Small businesses are increasingly interested in moving from piecemeal solutions to more integrated approaches to company operations," said Merle Sandler, senior research analyst at IDC. "But this can be tricky because small firms also want resources supporting company growth but not being too expensive or difficult to use. With that in mind, Intuit's strategy of extending beyond basic accounting to build a valuable network of available capabilities will resonate with both small businesses and the accountants that serve them."

Focusing on What Matters Most - Ease

QuickBooks 2007 features numerous enhancements that simplify users' most frequent activities, helping the approximate 3.7 million small businesses using the software be more efficient:

• Easier Setup: To help new users set up their income and expense accounts more accurately, QuickBooks 2007 features a simplified recommended Chart of Accounts. Optional accounts to suit specific business needs can easily be added. The latest version offers users the 30 most commonly-used reports for their business type - reducing the likelihood of incorrectly categorizing expenses down the road.
• Easier Payroll: The nearly 1 million businesses that use Intuit Small Business Payroll Services can now efficiently run payroll for all their employees at once rather than one at a time. A new dashboard displays the impact of payroll across the business, showing clearly what the company needs to pay employees and the government and what forms they need to file. And a new "review, confirm and submit" process gives users the confidence their payroll is correct and ready to go.
• Easy Data Sharing with Accountants: It is now easier than ever for accountants to change their clients' company files. At the same time, the business owner continues to work on their day-to-day business transactions without interruption. Previously, if a company needed to send files to an accountant, the business would have to stop entering data into QuickBooks to ensure version control. While the files were with the accountant, the business could not process financial information, including invoices, bills, or payroll. With the new Dividing Date functionality, QuickBooks allows accountants to edit and review all information before a specified date while allowing the company to continue business as usual. When the accountant is done with the adjustment, the business owner can quickly review and import those changes directly into QuickBooks.
• Easier Customization: Small businesses rely on forms to communicate with customers, suppliers, and partners. On average, a small business uses 168 forms per month, including invoices, letters, and purchase orders. With QuickBooks 2007, small businesses no longer have to print their customized document, walk to the printer and look at the hard copy to see if the form meets their needs. New for this year, QuickBooks includes an auto-preview feature that makes it easy to customize layouts with color schemes, logos, and more, while showing users their changes as they are made. The software also consolidates the most commonly-used customization options all on one screen.

New "Right for Me" Solutions to Solve Specific Needs

As part of Intuit's Right for Me strategy to offer QuickBooks versions for companies of different sizes and industries with specific needs, the QuickBooks 2007 line adds new capabilities for specialized businesses and growing companies:

• Enhanced Features for Product-based Businesses: Inventory management is critical to the success of product-based businesses, but to keep an accurate record of total inventory, they must often convert products from one unit of measure to another. For example, a company that purchases a product by the case of a supplier may sell the same effect by the individual bottle to its customers. Available in select QuickBooks 2007 offerings, the new Unit of Measure feature removes the hassle of manual conversion and the risk of costly errors by automatically calculating the transformation from one unit to another in the appropriate forms, including invoices, purchase orders, and inventory reports.

"To meet the needs of large retailers, we were spending two to three hours a day manually calculating and converting quantities. We have different shipping requirements for each retailer. Any error in shipping too many or too few of any one item results in shipping error fees" said Tom Mitchell, CFO of HBH Pet Products in Springville, Utah. "QuickBooks gives us that peace of mind to know that we have shipped out what our customers want when they want it."

In addition, an improved Shipping Manager enables multi-parcel high-value shipping with FedEx, and UPS handles third-party billing. It can print labels right from QuickBooks on popular thermal printers.

• Online Time Tracking for Professional Services Firms: Businesses can now collect and manage employee and contractor timesheets on the Web with the new QuickBooks Time Tracker service, available by subscription. Freeing managers and employees from manually entering data into QuickBooks from hand-written timesheets, Time Tracker quickly downloads data into QuickBooks saving hours of tedious work and reducing errors that can cause incorrect billing.

"Time Tracker saves us so much time when entering employee timesheets," said Leticia Zaragoza of K Page Kistler, PC in Virginia Beach, Va. "Plus, I have total control when managing these entries for billing."

• After-the-Fact Payroll: Approximately 28 percent of the 250,000 accountants who work with QuickBooks perform after-the-fact payroll services, entering payroll data from paychecks that clients have prepared themselves. To save accountants considerable time and effort, QuickBooks Enhanced Payroll for Accountants features a new After-the-Fact Payroll spreadsheet-style data entry tool that transforms this traditionally time-intensive task into a simple process.
• Increased List Limits for Growing Businesses: Mid-market businesses using QuickBooks Enterprise Solutions 7.0 can add a hundred thousand or more inventory items, customers, and vendors as they grow - an increase of more than 400 percent.

Pricing and Availability

QuickBooks Premier - including industry-specific editions for accountants, contractors, manufacturers and wholesalers, nonprofits, professional service firms, and retailers - is priced at USD$399.95.

QuickBooks Pro retails for USD$199.95, and QuickBooks' entry-level product, QuickBooks: Simple Start Edition, is USD$99.95. A new three-user value pack for QuickBooks Pro is USD$ 499.95, and a five-user value pack for QuickBooks Premier is USD$1,499.95.

QuickBooks 2007 products are currently available direct from Intuit at: http://www.quickbooks.com. They will be available starting on October 11, 2006, at fine retailers everywhere, including Amazon.com, Best Buy, CDW, Circuit City, CompUSA, Costco.com, Fry's Electronics, Office Depot, OfficeMax, Sam's Club, and Staples.

For mid-market companies, QuickBooks Enterprise Solutions 7.0 starts at USD$3,000 for a five-seat license and is available for up to 20 simultaneous users. The QuickBooks Enterprise price includes a 12-month full-service plan that entitles businesses to 24/7 technical support from a dedicated support engineer, product upgrades, data recovery, reporting services, interactive training tools, and automatic renewals. QuickBooks Enterprise can be purchased directly from Intuit by calling 866-379-6635 (+1 866-379-6635).

About Intuit Inc.

Intuit Inc. is a leading business and financial management solution provider for small and mid-sized businesses, consumers, and accounting professionals. Its flagship products and services, including QuickBooks®, Quicken®, and TurboTax® software, simplify small business management, payroll processing, personal finance, and tax preparation and filing. ProSeries® and Lacerte® are Intuit's leading tax preparation software suites for professional accountants.

Founded in 1983, Intuit had annual revenue of $2.3 billion in its fiscal year 2006. The company has nearly 7,500 employees with principal offices in 13 states across the United States and in Canada and the United Kingdom. More information can be found at: http://www.intuit.com.

Intuit, the Intuit logo, and QuickBooks, among others, are registered trademarks and registered service marks of Intuit Inc. in the United States and other countries.

As of Date: 2006-07-13

QuickBooks SDK 6.0 Feature List  

QuickBooks SDK 6.0, scheduled to be released in the fall of 2006, will include a number of new features requested by IDN member developers. Of course, QBSDK 6.0 will also support all parts of our previous versions of the QuickBooks SDK, so software built using QBSDK 5.0 or earlier can be easily migrated to QBSDK 6.0.

Integration with Additional QuickBooks Products 

QuickBooks SDK 6.0 is expected to include the following new features for use with U.S. editions of QuickBooks 2007 and QuickBooks Enterprise Solutions 7.0:   

Enhanced Data Access -- Transactions

• Support for BillPaymentCheckMod
• Support for CheckMod
• Support for CreditCardChargeMod
• Support for CreditCardCreditMod
• Ability to create Invoices (InvoiceAdd) from Sales Orders
• Support for JournalEntryMod
• Support for ReceivePaymentMod
• Support for TimeTrackingMod
• Ability to Query, Add and Delete Vehicle Mileage transactions
• Ability to set and query the "IsToBeEmailed" field on Invoices & Estimates (in Query, Add, and Mod operations)
• Support for "Other," "Other1," and "Other2" fields in all query, add & mod transactions
• Change TimeTracking to use BillableStatus enum in place of IsBilled & IsBillable (to enable indicating that time added has already been billed)

Enhanced Data Access -- Lists

• Support for AccountMod
• Ability to Query, Add, Delete, and Modify Vehicle list elements.
• Ability to query and modify the 5th address line (Notes) 
• Support for creating special accounts (e.g., accounts payable,  accounts receivable, CostOfGoodsSold, UndepositedFunds, etc.) via a SpecialAccountAdd request
• Support for creating special items (FinanceCharge, ReimbursableExpenseGroup, ReimbursableExpenseSubtotal) via a SpecialItemAdd request

Credit Card Transactions  (additional info available )

• Accept only "masked" credit card numbers in SalesReceiptAdd, ReceivePaymentAdd, & ARRefundCreditCardAdd transactions
• Enable identification of credit card present ("swipe") transactions
• Provide a ClientTransactionID field for future use by the QBMS reconciliation feature

Documentation

The QuickBooks SDK Documentation Suite is being improved with QBSDK 6.0. In particular, we expect to include the following:

• A  new Onscreen Reference (OSR) that simplifies the lookup for supported requests and fields for a specified QuickBooks edition or set of editions and also has complete documentation
• Improved QBFC documentation
• A  QuickBooks Web Connector manual

As of Date: 2006-07-13

QuickBooks SDK 5.0 Feature List  

QuickBooks SDK 5.0, available to IDN members for download, includes a number of new features requested by IDN member developers. Of course, QBSDK 4.0 also supports all features of our previous versions of the QuickBooks SDK, so software built using QBSDK 4.0 or earlier can be easily migrated to QBSDK 5.0.

Integration with Additional QuickBooks Products

QuickBooks SDK 5.0 can be used to develop software that integrates with any or all of a long list of QuickBooks products, including the following, which is new for QBSDK 5.0:

• QuickBooks Simple Start Edition (2006 and later)
• UK Editions of QuickBooks (QuickBooks Pro and QuickBooks Accountant, 2003 and later)
• QuickBooks Merchant Service (for credit card processing). QBMS can be used with QuickBooks or independently; QBMS transactions can be downloaded into QuickBooks, where they can be reconciled.

QuickBooks SDK 5.0 includes the following new features (unless otherwise noted, features unique to QBSDK 5.0 can be used only with U.S. editions of QuickBooks 2006* and QuickBooks Enterprise Solutions 6.0*):   

Credit Card Transactions 

• Support for credit card refunds (ARRefundCreditCard Add/Query)
• Support for credit card payment data in SalesReceiptAdd and ReceivePaymentAdd requests (using QuickBooks 2005 R5 and later)

Performance Improvements (additional info available)

• New iterator functionality for queries that makes it easy to walk through large amounts of returned query data in “chunk” sizes that you specify.
• Enable significantly improved performance when adding custom fields online items using InvoiceAddRq.
• Improved filtering on RefNum.

Additional Data Access

• Support for BuildAssembly: BuildAssemblyAdd/Mod/Query
• Ability to modify sales receipts: SalesReceiptMod
• Ability to specify which QuickBooks editions your application supports using a new QBAuthPreferences method (PutAuthFlags).

Support for Web-Based Applications

• The QuickBooks Web Connector provides a simplified mechanism for web server-based applications to communicate with QuickBooks via the QuickBooks SDK. This mechanism can be used with any version of QuickBooks that supports the QBSDK. (It can also work with QuickBooks POS via the QBPOS SDK.)

Events and Time Modified Support for Custom Fields and Private Data Extensions

• Events are generated for custom fields and private data extensions. Notice that data written to the custom field (via UI or SDK) or personal data extensions cause the Time Modified value for the parent object to be updated. (for more information, click here)

Improved Documentation

• The QuickBooks SDK Documentation Suite has been significantly improved with QBSDK 5.0.

* Notes on QuickBooks 2006 and QuickBooks Enterprise Solutions 6.0 :

With the release of QuickBooks 2006 and QuickBooks Enterprise Solutions 6.0, the file-based structure used for the company file in earlier versions of QuickBooks has been replaced with an industry-standard database. This is expected to enable future enhancements regarding storage, performance, and improved multi-user interactions. The QuickBooks SDK will continue to provide the only programmatic interface to QuickBooks; integrated applications written using the QuickBooks SDK will not need to change. 

The QuickBooks Enterprise Solutions 6.0 product includes a QODBC Driver for QuickBooks; this will enable read-only access to the QuickBooks data using standard SQL queries, which QODBC converts to QuickBooks SDK queries.

As of Date: 2006-12-16

IMPORTANT NOTE: To see how to install QODBC and connect to QuickBooks for the first time using Microsoft Vista, click here: How do I install and connect QODBC using Microsoft Vista? 

With Microsoft's release of Windows Vista, we wanted to ensure that our developers are informed about Intuit's plans for versions of QuickBooks before QuickBooks 2007 on vista. First and foremost, USA versions of QuickBooks 2006 and earlier will not be supported on Windows Vista.

Thanks to the Windows Vista compatibility "shims" from Microsoft, these versions of QuickBooks will successfully run, and many functions will behave normally. The user will be alerted that they are running in an unsupported configuration with wording similar to the following:

"When running QuickBooks 2006 or earlier on Windows Vista, you will likely experience instability and unreliable behavior, even though these problems may not appear immediately." 

One area we know will not work is that SDK-based applications, such as QODBC, will fail in BeginSession with an internal error due to an inability for shimmed applications to see each other or even other un-shimmed applications on vista. If you have a QODBC customer contemplating an upgrade to view, tell them they must update to v7.00.00.214 (or later)!

QODBC Version 7.00.00.207 contained what we had hoped the final piece would be vista compliant. The main changes were to move the ODBCINST.INI to "C:\Documents and Settings\All Users\Application Data\QODBC Driver for QuickBooks\ODBCINST.INI." All users should be able to read and write from there. All that is used for the global settings for QODBC.

The QODBCLOG.TXT Log file has been moved to C:\Documents and Settings\useraname\Application Data\QODBC Driver for QuickBooks (per user).

The new default for the optimizer file is "%UserProfile%\QODBC Driver for QuickBooks\Optimizer." New users will get that. Old users can change their path to that. With the %UserProfile% in the way, the optimizer .opt files will be saved in the user's folder (C:\Documents and Settings\useraname\Application Data\QODBC Driver for QuickBooks).

The last changes were to disable the Test Connection and Optimizer sync buttons when EditQDSN is started in elevated mode. (The Intuit qbXML SDK cannot be called from a formal application under vista) A help link has been added to explain why.

And lastly, a helper .exe called FQQBVSAV.EXE was added to allow a non-admin user to save settings to the System DSN after prompting for elevation.

Full vista compliance was scheduled for R4 since Intuit needed final vista bits to make the final preparations for QuickBooks '07. R5 will be slipstreamed to QuickBooks '07 users by Intuit before vista is available to the public. R5 can be found on Intuit's website under QuickBooks Product Updates at: http://www.quickbooks.com/support/index/ndxw_16_update.html.

QuickBooks qbXML SDK/Windows Vista Compatibility and QODBC

On the released version of Windows Vista, applications built with any version of the QuickBooks SDK (like QODBC) should work correctly with QuickBooks 2007 R5 and QuickBooks Enterprise Solutions 7.0 R5 (the latest available update releases of each), with the following caveats:

• The Vista user account must have its User Account Control (UAC) set to On (as recommended by Microsoft).
• QuickBooks and the QODBC application accessing the SDK should be run with standard user permissions (NOT elevated to run as administrator).
• Subscribing to custom menu events (UI extension events) does not work correctly in the current release.
• QuickBooks Web Connector 1.0 and QuickBooks Remote Data Sharing 3.0 are not supported on vista; new versions of each that work on view will be released soon.

Additionally, QuickBooks Point of Sale 6.0 will work on Windows Vista. However, if you are upgrading the operating system of a computer with Point of Sale software installed, you may need to reinstall Point of Sale to ensure it will launch correctly.

Please note that QuickBooks 2006 and earlier, as well as QuickBooks Point of Sale 5.0 and earlier, may not work correctly on Vista and that Intuit will not support those products on view.

For further information about Microsoft Windows Vista, QuickBooks, and the QuickBooks SDK, see this article on the IDN website.

Because of the SDK release's limitations in QuickBooks outside the USA, QODBC cannot be used under the Microsoft Vista operating system. Note: For QuickBooks users outside of the USA, this will mean that users will not be able to run QuickBooks and QODBC on Vista until further notice. We recommend that you keep using Windows XP in the meantime and hope that Intuit will rectify this issue in 2008.

You can view the QuickBooks announcements made regarding vista outside the USA at:

Australia/New Zealand/Singapore:
http://www.quicken.com.au/support/vistaUpdate.aspx
 
United Kingdom (UK):
http://www.quickbooks.co.uk/store/en/quickbooks/vista_faq.jsp?pd=vista
 
Canada:
http://support.intuit.ca/quickbooks/vista_faqs.php?pd=vista

QuickBooks SDK 4.0 Feature List  

QuickBooks SDK 4.0 includes a number of new features requested by IDN member developers. Of course, SDK 4.0 also supports all parts of our previous versions of the QuickBooks SDK, so software built using SDK 3.0 can be easily migrated to SDK 4.0.

QuickBooks SDK 4.0 can be used to develop software that integrates with a long list of QuickBooks Financial Software products. Features new to SDK 4.0 can be used with U.S. editions of QuickBooks 2005 and QuickBooks Enterprise Solutions 5.0 and/or with QuickBooks Online Edition.   

New Features Applicable to Multiple Types of Lists or Transactions:

• Ability to limit response data returned from Add, Modify, and Query operations to specified field(s) or aggregates. This could eliminate time spent handling unwanted data and decrease memory requirements. (additional info available here and here)
• Ability to get an approximate count of items returned in response to a query, with or without the returned objects.
• A new generic transaction query that returns data common to all transaction types, such as TxnID, style, time created, time modified, ListID, account referenced, etc. (The SDK equivalent of QuickBooks' "Advanced Find" function.)(additional info available)
• Significant performance improvements for single RefNumber transaction queries, speeding searches for specific transactions.

Accounts Payable Transactions (additional info available)

• Ability to add and modify Item Receipts.
• Ability to create a bill or an Item Receipt from a Purchase Order.
• Ability to link lines in new Bills or Item Receipts with lines in Purchase Orders.

Additional Data Integration

• Ability to add, modify, query, and delete Price Levels, which will be used by default in sales transactions added by the SDK unless a specific rate is supplied in a line item. (additional info available)
• Ability to use a "PaidStatus" filter with the ChargeQuery request.

Events and User Interface Integration

• Ability to specify that an application does not receive subscribed-to events generated by itself.
• The ability for UI Extension events to contain context data from the current QuickBooks form.
• Ability to use ListDisplayAdd/Mod requests to display Account and Item forms in QuickBooks.
• Ability to pre-fill specific data in TxnDisplayAdd requests, enabling an application to have QuickBooks enter a particular customer, vendor, etc., when opening a new transaction form.
• Ability to launch QuickBooks in the foreground (i.e., showing its User Interface) with a specific company file.
• Ability to filter transactions based on pay status (which enables applications to find which transactions are payable for a given customer).

Enhancements to the Integrated Application Approval Process (additional info available)

• Allow an application to request read-only access to a QuickBooks company file and notify the user that the application will only have read access to the data.
• Allow the user to set up unattended mode as part of the authorization dialog.
• Allow an application to require access to personal data during the application authorization process to avoid user errors.

QuickBooks Online Edition (QBOE)

• The ability of desktop applications to access QBOE using the same COM interface used with desktop QuickBooks reducesthe effort required to port an application to QBOE. (additional info available here and here)
• SDK support for discount, sales tax, and shipping information

• Entity and date filters for all transaction queries (i.e., InvoiceQuery)

For additional details on QBSDK 4.0 (including changes between QBSDK 3.0 and 4.0), see the QBSDK 4.0 Release Notes.

Next: Read about the features introduced with SDK version 3.0.

Problem Description:

Is it possible to add a line to a check that already exists?

Here is an example from the QuickBooks Sample file. Below is a check for $500.00

Now I want to add additional items to the check, so the check now looks like this:

I said $200.00, and now the total is $700.00.

How do I do this in QODBC?

I know how to make a check with multiple lines using FQSaveToCache, but here I want to add a line to a statement that already exists.

Solution:

You need to add the field TxnID into the insert query. The field "TxnID" is available in the insert query only when the TxnID value points to an existing entity in QuickBooks.

The transaction was found by executing the below query:-

Here is an example of adding one line item to an existing Check, "2432F-1639575481," in our test company file.

For Adding Expense Line in existing check:

INSERT INTO CheckExpenseLine (TxnID, ExpenseLineAccountRefListID, ExpenseLineAmount, ExpenseLineMemo, ExpenseLineCustomerRefListID, ExpenseLineBillableStatus) VALUES ('2432F-1639575481', '3C0000-933270542', 200.00, 'Add 2nd line to check using QODBC', '800000B0-1197755238', 'Billable')

For Adding Item Line in existing check:

INSERT INTO CheckItemLine (TxnID, AccountRefListID, PayeeEntityRefListID, ItemLineItemRefListID, ItemLineDesc, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus) VALUES ('2432F-1639575481', '20000-933270541', '630000-1071521572', '3C0000-1071513137', 'Building permit,' 100.00, 100.00, '250000-933272658', 'Billable')

Please notice that all values in the query come from our test company file. Please replace them using your value when running it on your end. You can add other line item columns to this query according to your request.

Note: The FQSaveToCache does support existing Check query.

Problem Description:

My clients give me a backup copy of their QuickBooks file. I don't have QuickBooks installed on my computer but would like to bring the data into Excel. Can I use QODBC without having QuickBooks installed?

Solution:

No, sorry! QODBC uses QuickBooks SDK to access QuickBooks data & QuickBooks SDK is installed with QuickBooks application. So QuickBooks application requires for accessing QuickBooks data using QODBC.

Another option is you can QRemote to connect to QuickBooks located on the remote machine.

Please Refer Accessing QuickBooks Data Remotely using QODBC & QRemote for more detail.

Problem Description:

Does anybody know which table the Online Banking Aliases data is stored in QuickBooks?

To see what I'm talking about, go to the "Additional Info" tab of the "Edit Vendor" window and click on "Manage Aliases."

Solution:

I am sorry to inform you that the Online Banking Aliases are currently not exposed in Intuit's qbXML SDK, so they can't be seen using QODBC. Online Banking is an add-on that requires exclusive access to the QuickBooks company file.

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

Please refer to How to export QuickBooks data not available in QODBC.

Problem Description:

We have been using QODBC without problems with our current QuickBooks version for months. Suddenly QODBC has stopped working on all our computers.

I have tried a few different QODBC versions, including the latest, but none work.

QODBC users are faced with a QODBC message that "QODBC is no longer licensed for this Reckon version."

Reinstalling and/or upgrading to v322 doesn't help. Some users are still on v321, and the same message is coming up.

Solution:

QODBC Read Only edition was free with Reckon Pro/Premier/Enterprise until Oct 26, 2017.

The contract for free use by Reckon Accounts users ended/terminated on the above date.

To continue using QODBC for Reckon Accounts, Please purchase a QODBC License.

You can purchase a QODBC License for Reckon Accounts by Clicking here or Visiting http://qodbc.com/order.htm and then clicking "Asia Pacific"

Or contact your local dealer (Australia/New Zealand/Southeast Asia). Visit http://qodbc.com/contact.htm.

Problem Description:

Is it possible to retrieve an employee's emergency contact information? If so, from what table?

Solution:

Employee Emergency Contact Information is added in QODBC V320. If you are using QODBC V319 or an earlier QODBC version, you need to download & install the latest QODBC version to access Employee Emergency Contact Information.

You can download & install QODBC's latest version by clicking here.

It would be best if you referred to the fields Employee table for Emergency Contact information.

1. EmergencyContactsPrimaryContactName

2. EmergencyContactsPrimaryContactValue

3. EmergencyContactsPrimaryContactRelation

4. EmergencyContactsSecondaryContactName

5. EmergencyContactsSecondaryContactValue

6. EmergencyContactsSecondaryContactRelation

Problem Description:

I'm trying to install the QODBC on our SQL Server, and I followed all steps in your documentation on How to create a Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote.

But when I try to save my Linked server in SQL Studio, I have an error message:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the OLE DB provider "MSDASQL" data source object for linked server "QREMOTE."

OLE DB provider "MSDASQL" for linked server "QREMOTE" returned the message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified." (Microsoft SQL Server, Error: 7303)

Can you help me with that?

Solution:

Please make sure you are creating Linked Server on SQL Server where QODBC is installed. If you start the linked server from another machine where SQL Server Management Studio is installed but QODBC is not installed. You may face this error. 

You can verify it by closing SQL Server Management Studio (SSMS) & reopening it and at the time of connecting to the SQL Server option. Please remove the server name & insert "." (i.e., dot) & try to connect.

If you can connect, then SQL Server is on the local machine; otherwise, SQL Server is on another device.

Problem Description:

Is it possible to register an item's arrival without the purchase order?

If yes, can I insert the rows in the "ItemReceiptItemLine" table using the reference to Bill?

Solution:

Receiving Item does not require a Purchase Order. You can receive it directly using the BillItemLine & ItemReceiptItemLine tables.

If you want to reference the Receive Item with Bill, you need to create a Bill using the BillItemLine table.

Please refer to How to create Bills using QODBC for more details.

You can Receive Items without a purchase order by inserting records in the "ItemReceiptItemLine" table.

Examples of Creating Item Receipts using QODBC

First, please create a new MS Access database & link the ItemReceiptItemLine table.

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

If you have 64-bit MS Office, please refer to How to Use QODBC with MS Access 2016 / 365 / 2013 64-bit for more details.

After linking the table, we need to execute the query in the MS Access query editor:
MS Access >> create tab >> Query Design

It should pop up for you to choose the table. Please click the "close" button.

Now, please click on "SQL" from the left top corner below the File menu.

Create an Item Receipt with 2 Lines

     Here we create an Item Receipt with two lines using 2 ItemReceiptItemLine INSERT commands (with the FQSaveToCache caching flag set true), which is automatically designed to pull in all cached ItemReceiptItemLine records and save immediately.

First Line

INSERT INTO ItemReceiptItemLine (VendorRefListID, RefNumber, Memo, ItemLineItemRefListID, ItemLineDesc, ItemLineQuantity, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus ,FQSaveToCache) values ('B0000-933272656', '1', 'Received items (bill to follow),' '110000-933272656', 'Cabinets: Cabinet Pulls,' 2,50,100,' 7D0000-1071526869', 'Billable,' 1)

Second Line

INSERT INTO ItemReceiptItemLine (VendorRefListID, RefNumber, Memo, ItemLineItemRefListID, ItemLineDesc, ItemLineQuantity, ItemLineCost, ItemLineAmount, ItemLineCustomerRefListID, ItemLineBillableStatus,FQSaveToCache) values ('A60000-1197760912', '1', 'Received items (bill to follow),' '130000-933272656', 'Hardware: Doorknobs Std,' 3,60,180,' 940000-1071506775', 'Billable,' 0)

Result in QuickBooks

Related Data Location Information

VendorRefListID Find ListID from Vendor Table

ItemLineItemRefListID Find ListID from Item Table

ItemLineCustomerRefListID Find ListID from Customer Table

Problem Description 1:

Does sp_report support "Like" in the parameter?

For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters AccountFullName Like '%Chase Credit Card%,' SummarizeRowsBy = 'TotalOnly.'

Solution 1:

You can use like in sp_report as below:

For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters SummarizeRowsBy = 'TotalOnly' where AccountFullName Like '%Chase Credit Card%.'

Problem Description 2:

On some sp_reports like OpenInvoices or SalesByCustomerDetail, I don't see where I can put the parameter for the sales rep.

Which one is the parameter for the Sales Rep?

Solution 2:

The SalesRep filter in OpenInvoices and SalesByCustomerDetail report is not available through the Intuit SDK, so they are not available through QODBC.

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

But, You can use the available column name in the sp_report by specifying with a where clause for filtering report data as below.

For Example, the SalesRep column is available in the OpenInvoices and SalesByCustomerDetail report, So you can use it in the sp_report query for filtering report data as below:

sp_report OpenInvoices show TxnType_Title, Date_Title, RefNumber_Title, PONumber_Title, Terms_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, PONumber, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today' where SalesRep = 'John Doe.'

sp_report SalesByCustomerDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Name_Title, Item_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Item, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate' where SalesRep = 'John Doe.'

Problem Description:

How to Auto Add relationship Between two tables in Crystal Report using QODBC?

Solution:

A relationship can be extracted using the ODBC function "SQLForeignKeys" or "SP_FOREIGNKEYS."

Usage: SP_FOREIGNKEYS tablename tablename

Instruction: Returns a record set of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Crystal reports application automatically uses the relationship method and sprovides a user-friendly graphic interface to view the relationships between two or more tables.

To Auto Add relationship Between two tables in Crystal Report, Please create a new report in Crystal Report & connect to QuickBooks Data through QODBC. Please refer to How to use QODBC with Crystal Reports for connection detail.

I am assuming that you have referred above link & connected to QuickBooks from Crystal Report using QODBC.

The connection was made with QuickBooks through "QuickBooks Data" DSN & you can see the list of tables and windows list of tables available.

Select the table you want to add to the Crystal Report & click the ">" button. In this example, I am selecting the Customer & Invoice table.

The Customer & Invoice table data is added to the Crystal Report. Please click the "Next" button for link tables.

Please select the "By Key" option & Crystal Report will automatically link tables according to the Primary Key & Foreign Key relationship between the two tables of QODBC.

You can see that the Invoice table's "CustomerRefListID" is related to the Customer table's "listed" field.

Suppose you select the "By Name" option. In that case, Crystal Reports will not consider the Primary Key & Foreign Key relationship between two tables of QODBC & it will link according to a similar column name.
For Example Invoice table's ClassRefListID with the Customer table's "listed."

The table fields are now available for selection in your report. Now, You can perform any operation on table data using Crystal Reports.

In this example, I have added the Customer table's Phone, Email, and Balance fields & Invoice table's CustomerRefFullName, RefNumber, and SubTotal fields in the report.

Problem Description:

I am using QODBC & trying to modify data in QuickBooks. In a few tables, I can't insert/update records.

I am getting a "[QODBC] Not supported" error when inserting records.

Also, I have noticed "Error Modify not supported on this table" in QODBC Logs when trying to update records.

Solution:

You may get a "[QODBC] Not supported" error if you try to insert the record in the Header/Master table directly without inserting the form in the Child/Detail table.

For Example, You may get a "[QODBC] Not supported" error if you try to insert a record directly Invoice/Bill/SalesOrder/PurchaseOrder, etc... table without inserting a document, the child table InvoiceLine/BillExpenseLine/BillItemLine/SalesOrderLine/PurchaseOrderLine.

OR

You may get the "[QODBC] Not supported" error or "Error Modify not supported on this table" in QODBC Logs If you trying to insert/update the record in the Read-Only table.

Below is the list of Read-Only which does not support insert/update operations. 

To know which tables/fields support insert/update. Please visit QODBC Data Layouts.

Click on the region of the QuickBooks edition you are using. Click "ALPHABETICAL LISTING OF ALL TABLES" under the "TABLE OVERVIEW" label & find the TableName for which you want to check the schema, and click the "Detail" link.

You will find QUERYABLE, UPDATEABLE, INSERTABLE, and REQUIRED ON INSERT columns against each field.

This will help you determine which fields are allowed in select, update, and insert queries.

Problem Description:

When pushing data from the access database to QuickBooks, the QODBC message indicates waiting on QuickBooks, and both Access and QuickBooks freeze (Not Responding). This is happening with a new user on this PC. Other users can log in, which usually works, but this user cannot push data from Access to QuickBooks. We have reviewed permissions for the user in QuickBooks and Active Directory, and they are all the same as working users. Is any special permission needed for this to work? This issue is urgent. Please advise.

Solution:

You may face QuickBooks and Access and QuickBooks freeze (Not Responding) issues when accessing the table for the first time because QODBC is building an optimizer for that table in the background.

Please refer to How Optimizer Works for more details.

Please Rebuild the optimizer file & check again. Refer: Getting the limited number of records, or a Few of the documents is Missing in the QODBC Table result Rebuild Optimizer file section.

Please also enable the QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options. Refer: To how to view details on what QODBC is performing for more information.

After building the Optimizer file, when you run the query, QODBC will check for any changes to QuickBooks Data & update it in the Optimizer file. So you will not face a Not Responding issue in QuickBooks & your application.

Problem Description:

I am using MS Excel to access QuickBooks Data through QODBC. I have Estimate & some of the Estimates are converted to QuickBooks. I want to know how many Estimates are converted to Invoice & what is conversion time.

I need Estimate RefNumber, Date, Customer, Value, Invoice RefNumber, Date, Value, and Days to Convert

How Can I find an Estimate for Invoice conversion time?

Solution:

Please click here to download the Sample Excel file.

You can find the Estimate for Invoice conversion time in the below steps.

Please start Microsoft Excel & open a new Workbook. An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "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.

It will take some time to get the DSN list. And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source, "QuickBooks Data," with a sample company file open. And check to Use the Query Wizard to create/edit queries.). :

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

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

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

After you grant permission for QODBC, you will be guided by Query Wizard. In Choose Columns window, Please click "Cancel" to write the query in Microsoft Query.

Please click "Yes" to write the query in Microsoft Query.

In Add Tables windows list of tables available, Please click "Close" to Open the MS Query editor.

Click the "SQL" button to write SQL Statement.

We will use the below query to find the Estimate to Invoice conversion time & paste it into the "SQL Statement" box and click "OK."

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount as EstimateValue, Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo," InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "OK" to continue the execution of the query.

The result is available in the Microsoft Query editor window.

Please select "File" >> "Return Data to Microsoft Excel" to export the result to Excel.

Please select "Existing Worksheet" and click "OK" to export the result to Excel.

The result is available in an Excel sheet. As you see column alias is not working.

We will add an alias by editing the query by clicking the "Data" & "Connection" buttons.

Please select the query & click the "Properties..." button.

Please navigate to the "Definition" tab & paste the below query in the "Command Text" box and click "OK."

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount as EstimateValue, Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo," InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "Close" to close the workbook connection window.

The result is available in an Excel sheet with a column alias.

Note: Case When a statement is not working MS Query. Alternative to Case When information is an IIF statement, But IIF statements are not supported in QODBC.

Also, Refer:

Troubleshooting - Cannot use alias in MS Query

Problem Description:

We don't have an operational problem, just a SQL/technical question -- we are currently pulling invoices like this:

SELECT * FROM Invoice WHERE RefNumber ='221206' (works great)

But it does NOT tell which QuickBooks user CREATED/ENTERED the Invoice/SalesOrder. (We do NOT use Sales Reps, we want to know the actual user that typed in the order).

Where can we find this information? Can I use the TxnID from the Invoice or SalesLine in another table to determine who entered that Invoice or line item?

Solution:

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

I'm sorry to inform you that the QuickBooks user details who Created/Entered the Invoice/SalesOrder are unavailable through the Intuit SDK, so they are not available through QODBC.

As a workaround, you can create the custom field with the name (QBUser) in the customer center & you can use it on Invoice/SalesOrder. You need to make the custom field in customer & define this field in the Invoice template.

Once you define the custom field in Invoice/SalesOrder, You can store QBUser details on the custom field, which is also available in QODBC Invoice/InvoiceLine table with the name CustomField+(Field name you created). If you made the custom field with the QBUser in QODBC, you could find the column "CustomFieldQBUser."

You can get the custom field in QODBC after defining custom fields in QuickBooks.

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

Problem Description:

QB Desktop Enterprise 2017 Manufacturing & Wholesale R5_88 Successfully installed QODBC using Optimizer. Can query every table with QODBC Test Tool and MS Access, except:

UnitOfMeasureSet, UnitOfMeasureSetRelatedUnit, UnitOfMeasureSetDefaultUnit

Any query of these tables hard crashes QuickBooks!

When I execute the query:
Select * from UnitOfMeasureSet

Within 5 seconds of executing the query, QuickBooks crashed:

There is no entry in the QODBC log.

Can find below entries in the QuickBooks SDK log:

20170730.171449 I 7784 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 7196).
20170730.171449 I 7784 SpecVersion Current version of qbXML in use: 13.0
20170730.171449 I 7784 QBSDKMsgSetHandler QUERY: Unit Of Measure
20170730.171450 E 7784 StorageInterface *** UNKNOWN EXCEPTION THROWN WHEN PROCESSING THE QUERY REQUEST ***. This is an indication of a possible internal QuickBooks error.

Solution:

When you face this error, Please refer to QuickBooks SDK logs.

Please note the error:

20170730.171449 I 7784 QBSDKMsgSetHandler QUERY: Unit Of Measure
20170730.171450 E 7784 StorageInterface *** UNKNOWN EXCEPTION THROWN WHEN PROCESSING THE QUERY REQUEST ***. This is an indication of a possible internal QuickBooks error.

When you see error *** UNKNOWN EXCEPTION THROWN WHEN PROCESSING THE QUERY REQUEST *** in QuickBooks SDK logs. You should test the same on another company file to see whether the issue is related to the company file.

If you are still getting the same error in QuickBooks SDK logs with other company files, then some internal error might occur in processing the requests. To resolve this error, you need to get in touch with Intuit.

You can get in touch with Intuit using the below links:

Enterprise Support

QuickBooks Customer Service & Support

Contact Us - Intuit

Problem Description:

QODBC crashes QuickBooks Desktop 2017 after completing all SQL transactions. Here are the details.

1. SQL command is sent to the QODBC connector

2. QODBC connector opens up a QuickBooks instance in the background on the server. i.e., View through Task Manager QBW32.exe

3. QODBC connector executes all SQL statements successfully. i.e., SELECT and INSERT statements

4. QODBC goes into a listening mode for about 30 seconds. i.e., SLQFreeStmt

5. QODBC sends the final command to close all connections. i.e., SQLDisconnect

6. Upon the SQLDisconnect command, the QODBC connector successfully disconnects from SQL.

7. Upon the SQLDisconnect command, the QODBC connector crashes QuickBooks Desktop 2017 in an attempt to close the QuickBooks instance in the background. i.e., QBW32.exe sends a critical error that it hit.

8. We must manually close the critical error box to send another SQL command to the QODBC connector. Very cumbersome to our process.

Solution:

The issue is related to Microsoft .NET Framework.

QuickBooks 2017 installs Microsoft .NET Framework 4.6. If your machine is already updated to 4.7, you may face this issue.

To resolve this issue, You need to uninstall QuickBooks 2017 and Microsoft .NET Framework 4.7 & Reinstall QuickBooks 2017, which installs .NET Framework 4.6. After that, you can upgrade from Microsoft .NET Framework 4.6 to Microsoft .NET Framework 4.7.

This will fix the unexpected crash errors on the QuickBooks connector closing QuickBooks.

Problem Description:

I am using QODBC to export QuickBooks Data into MS Access or Excel and getting a certificate error.

When I tried to connect to QuickBooks using MS Access or Excel, A message just started today saying, "An application with an invalid certificate is requesting access to the following QuickBooks File. It speaks to go to preferences - which I did, and I don't know what to do to get rid of this error and to access my tables using QODBC. What do I need to do to resolve this issue?

Solution:

2023-08-24: Please refer to https://qodbc.com/links/3074

Note: A new Office 2016 shall be released soon, which will fix this issue. 

Refer to Office 2016 - July 2017 updates and MSIT TEST CodeSign CA. Also, Check the MS Office Updates page to know whether or not Microsoft has released a new version of Office https://support.office.com/en-us/article/What-s-new-in-Office-365-95c8d81d-08ba-42c1-914f-bca4603e1426?ui=en-US&rs=en-US&ad=US&fromAR=1  section - Complete list of all updates.

Update 2017-07-29: Microsoft has released an update for MS Office 365/2016 refer to July 28, 2017, Version 1707 (Build 8326.2059). This update contains the fix for the invalid certificate error. "Fix an issue with how some program files are signed, causing anti-virus programs to flag those files as well as problems protecting or accessing data under Windows Information Protection (WIP)."

Please update MS Office to the latest version.

If you are still facing the issue, please follow the steps below.

End of Update 2017-07-29

Please check the digital signature in MS Access.

Open the task manager, right-clicks on msaccess.exe or access.exe, and click on Open File Location.

OR

If the error occurs in MS Excel, please check the digital signature in MS Excel.

Open task manager, right-click on msexcel.exe or excel.exe, and click on Open File Location.

Follow the screenshots below using MS Excel in place of MS Access!

You will be navigated to the Access EXE location.

Once you have located the executable (EXE) location, you need to right-click on "msaccess.exe" or "access.exe" and click on "Properties."

Navigate to the "Digital Signatures" tab & click on "Details."

You will notice "Details" has a red icon and says, "A certificate chain processed, but terminated in a root certificate which the trusted provider does not trust." You need to install the latest certificate.

Please click on "View Certificate" to install the certificate.

Please click on "Install Certificate" to install the certificate.

Please follow the installation steps below. Select store location "Local Machine" & click "Next."

Select "Automatically select the certificate store based on the type of the certificate" & click "Next."

Please finish the installation by clicking on "Finish." After installation, please Restart the machine and Test Connection.

You will be back to the Certificate Window. Please install the certificate again. This time you will install it under "Trusted Publishers."

Please click on "Install Certificate" to install a certificate.

Please follow the installation steps below. Select store location "Local Machine" & click "Next."

Select "Place all certificates in the following store" & click "Browse...".

Select "Trusted Publishers" from the list of folders/stores.

Please finish the installation by clicking on "Finish." After installation, please Restart the machine and Test Connection.

Update - 2017-07-28: If you are still facing the issue, follow the steps below.

You are navigated to Access/Excel EXE location. Once you have located the MS Access/Excel executable (EXE) location, you need to right-click on "msaccess.exe" or "access.exe"  or "excel.exe" and click on "Properties." Navigate to the "Digital Signatures" tab & click on "Details."

You will notice "Details" has a red icon and says, "A certificate chain processed, but terminated in a root certificate which the trusted provider does not trust." You need to install the latest certificate.

Please click on "View Certificate" to install a certificate.

Switch to the "Certificate Path" window.

After switching to the "Certificate Path" window, click "View Certificate." Click "Install Certificate"

Please follow the installation steps below. Select store location "Local Machine" & click "Next." (if you do not get the welcome screen, you might directly get the "Certificate Store" step.

Select "Place all certificates in the following store" & click "Browse...".

Select "Trusted Root Certification Authorities" from the list of folders/stores.

Please finish the installation by clicking on "Finish." If prompted to accept the certificate, click "Yes."

Start QuickBooks, and log in to Company File. Switch Company File to single-user mode.

From the menus, Click "Edit" -- "Preference," -- "Integrated Applications," -- "Company Preferences."

From the list of Applications, Enable/Check/Tick the "Allow access" against all the FLEXquarters QODBC.

Click OK

Please start MS Access/Excel and test the connectivity. If you are facing MS Access/Excel Crash, Please Click here and follow the steps from the article.

End of  Updates 2017-07-28

If you are still facing the issue, try updating Windows update to the latest. Restart the machine and Test Connection.

If you still face the issue, try Reinstalling MS Office or contacting Microsoft support.

Note: Windows 7 doesn't support SHA256 signatures by default. You need to apply a windows update.

Refer:

Microsoft security advisory: Availability of SHA-2 code signing support for Windows 7 and Windows Server 2008 R2: March 10, 2015

Microsoft Security Advisory 2949927

You cannot run an application that is signed with an SHA-256 certificate on a computer that is running Windows Vista SP2 or Windows Server 2008 SP2

SHA2 and Windows

Problem Description:

I am using QODBC to fetch QuickBooks Data in SQL Server. I have created Linked Server in SQL Server.

We are receiving the following error when trying to fetch data.

The SQL is:
SELECT * FROM [QREMOTE].[QODBC].[Company]

The error is:
Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for the linked server "QREMOTE" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Line 4
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "QREMOTE." A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Solution:

You have used the wrong SQL statement. It would be best if you used LinkedServerName & table name in the query.

For Example:
Select * from YourLinkedServerName...TableName
SELECT * FROM [QREMOTE]...[Company]

Problem Description:

I am using QODBC to access QuickBooks Data. I am using the QODBC optimizer, and I want to know more about How Optimizer Works.

I am able to connect to QODBC but when I fetch any table it takes a lot of time.

I am able to fetch records from a few tables but for other tables, QODBC is just processing and not returning any records.

Solution:

The optimizer brings some data to a local cache to increase query retrieval performance. Below is the default setting of QODBC Optimizer.

The QODBC Optimizer is designed to improve performance when accessing QuickBooks data by caching results locally. This article explains how the optimizer works by default, step-by-step.

When you run a query through QODBC (for example, SELECT * FROM Invoice), QODBC communicates with the QuickBooks SDK to retrieve the requested data. Since the QuickBooks company file is not a traditional database, but rather a flat file system, data retrieval and conversion are handled in a specialized way to optimize speed and reliability.

Here is how the optimizer works with the default configuration.

Step-by-Step Process

Step 1: Query Execution

You execute a query such as:

Step 2: Check for Optimizer Data

QODBC checks the Optimizer Table to see if data for the requested table (in this example, Invoice) already exists.

Step 3: First-Time Data Retrieval

Assuming there are 50,000 records in the Invoice table.

If this is the first time accessing the table:

• QODBC retrieves all records from QuickBooks, including data from related child tables (for example, Invoice, InvoiceLine, and InvoiceLinkedTxn).

• The QuickBooks SDK returns data in XML format, in batches of 500 records per request.

• QODBC converts this XML data into a table format for use by your application.

Note: Since this is a full data retrieval, it may take some time. Please do not close or terminate the process during this initial load.

Step 4: Data Storage in Optimizer

After retrieving the data:

• QODBC saves the data into the Optimizer File.

• The optimizer records the timestamp of the data retrieval.

Step 5: Data Returned to Application

Once the optimizer is updated, QODBC reads from the optimizer file and returns the results to your application.

Step 6: Subsequent Queries

The next time you execute the same query (e.g., SELECT * FROM Invoice):

1. QODBC again checks the Optimizer Table for Invoice.

2. It finds that the data already exists and retrieves the last synchronization timestamp.

3. QODBC requests only the changes (added, modified, or deleted records) since the last timestamp using the QuickBooks SDK.

Step 7: Delta Update

Because QODBC now retrieves only changed data, the process is much faster.
For example:

• Out of 50,000 total records, only 60 have changed (40 modified, 10 added, 10 deleted).

• QODBC processes only those 60 records instead of the full 50,000.

Step 8: Optimizer Update

QODBC applies the delta changes to the optimizer file to keep it up to date.

Step 9: Return Updated Data

Finally, QODBC reads the updated data from the optimizer file and returns the complete result set to the calling application

Key Points to Remember

• The first query on any table will take longer because QODBC must retrieve and cache all records.

• Subsequent queries are significantly faster, as only new or changed data is fetched.

• Do not interrupt the first-time data load process to avoid incomplete optimizer data.

Question:

I am fetching the top 10 records from Invoice (or any other table), and QODBC is still reading all the records from QuickBooks.

Answer:

If you are not using the optimizer or if you are querying the table for the first time, QODBC will have to build the optimizer before returning the records.

Even if you have requested the top 10, QODBC has to fetch all the records from QuickBooks to show the result matching the criteria.

Problem Description:

I am using QODBC to access QuickBooks Data. I have linked all QuickBooks tables using QODBC.

I want the Customer table to be Optimizer OFF and the rest to be Optimizer ON.

How can I achieve this?

Solution:

It would be best if you had the Customer table to be Optimizer OFF and the rest of the tables to be Optimizer ON. I suggest Using a separate DSN with Optimizer Off for the Customer table.

You need to create a a new QODBC DSN through Configure QODBC Data Source.

You can open Configure QODBC Data Source from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

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" option and click on the "Finish" button.

Name your new DSN as "QuickBooks Data-OPT-OFF."

Switch to the "Optimizer" tab/window and uncheck the "Use Optimizer" option & Click "Apply" followed by "OK" to save the DSN configuration.

Start QuickBooks login to Company File as Admin. Start QuickBooks login to Company File as Admin. Start QuickBooks login to Company File as Admin. New DSN created. Close the ODBC Manager by clicking "OK."

Switch to your MS-Access application. Right-click on Linked tables and select "Linked Table Manager". Right-click on Linked tables and select "Linked Table Manager". Right-click on Linked tables and select "Linked Table Manager". I have linked all tables in the MS Access Database using the "QuickBooks Data" DSN with the Optimizer ON option. 

From the list of tables, select Customer table & enable the "Always prompt for a new location" option, and Click "OK."

From the DSN list, select the newly created DSN from "Machine Data Source," Instead of using "QuickBooks Data," Select "QuickBooks Data-OPT-OFF."

The linked table has been refreshed.

Customer table DSN is changed to "QuickBooks Data-OPT-OFF" from "QuickBooks Data." Close "Linked Table Manager" by clicking the "Close" button.

This way, MS Access will create a new Connection to QuickBooks Data-OPT-OFF (Optimizer Off - when accessing the Customer table),. For the rest of the tables, MS Access will use QuickBooks Data (Optimizer On).

Problem Description:

I am attempting to extract data from a client's hosted QuickBooks Enterprise system. I need every transaction in the system as we are transitioning to a different approach. I have removed all of the records from the "Transactions" table, but they don't appear as "balance." I think the amount column should net to zero - as the credits (negative) Amount should earn against the debits (positive) amounts.

Solution:

In the Transaction table - the total of Credit (negative) will not match he sum of Debit because the transaction table has additional rows for the subtotal.

You have to exclude all such records/rows having AccountRefListID/AccountRefFullName as empty. You can exclude blank AccountRefListID/AccountRefFullName by clicking the column filter, unchecking "Blanks," & click "OK."

You may have to also format the Amount column to Number (show two decimal values) to avoid any round-off issue in excel.

You can format cells by right-clicking on the "Amount" column & click "Format Cells..".

After applying the above changes, you will notice that the Amount appears in balance.

Problem Description:

I am using Python script to access QuickBooks Data. I can select QuickBooks Data using Python without any issues.

However, I can successfully insert data into QuickBooks using Python with static values.

But, I am getting the below error when I use parameterized insert statement. See the code below:

[HY004] [Microsoft][ODBC Driver Manager] SQL data type out of range (0) (SQLBindParameter)

import pyodbc

stmt = pyodbc.connect("DSN=QuickBooks Data QRemote", autocommit=True)

CustomerRefListID = input("CustomerRefListID: ")
ClassRefFullName = input("ClassRefFullName: ")
TemplateRefListID = input("TemplateRefListID: ")
RefNumber = input("RefNumber: ")
CreditMemoLineItemRefListID = input("CreditMemoLineItemRefListID: ")
CreditMemoLineDesc = input("CreditMemoLineDesc: ")
Rate = input("CreditMemoLineRate: ")
Quantity = input("CreditMemoLineQuantity: ")
Amount = input("CreditMemoLineAmount: ")


P1 = CustomerRefListID
P2 = ClassRefFullName
P3 = TemplateRefListID
P4 = RefNumber
P5 = CreditMemoLineItemRefListID
P6 = CreditMemoLineDesc
P7= Rate
P8= Quantity
P9= Amount

cursor = stmt.cursor()

sql = "INSERT INTO CreditMemoLine (CustomerRefListID,ClassRefFullName, TemplateRefListID, RefNumber,  CreditMemoLineItemRefListID, CreditMemoLineDesc, CreditMemoLineRate,CreditMemoLineQuantity, CreditMemoLineAmount) values (?,?,?,?,?,?,?,?,?)"

cursor.execute(sql,(P1),(P2),(P3),(P4),(P5),(P6),(P7),(P8),(P9))

cursor.close()
stmt.close()

Solution:

For the parameterized query, you may have to apply an encoding to the variable values.

Please refer to the sample script.

Download Sample

import pyodbc
from decimal import *

stmt = pyodbc.connect("DSN=QuickBooks Data QRemote", autocommit=True)

CustomerRefListID = input("CustomerRefListID: ")
ClassRefFullName = input("ClassRefFullName: ")
TemplateRefListID = input("TemplateRefListID: ")
RefNumber = input("RefNumber: ")
CreditMemoLineItemRefListID = input("CreditMemoLineItemRefListID: ")
CreditMemoLineDesc = input("CreditMemoLineDesc: ")
Rate = input("CreditMemoLineRate: ")
Quantity = input("CreditMemoLineQuantity: ")
Amount = input("CreditMemoLineAmount: ")

encoding = 'utf-8'
P1 = CustomerRefListID.encode(encoding)
P2 = ClassRefFullName.encode(encoding)
P3 = TemplateRefListID.encode(encoding)
P4 = RefNumber.encode(encoding)
P5 = CreditMemoLineItemRefListID.encode(encoding)
P6 = CreditMemoLineDesc.encode(encoding)
P7= Decimal(Rate)
P8= Decimal(Quantity)
P9= Decimal(Amount)

cursor = stmt.cursor()

sql = "INSERT INTO CreditMemoLine (CustomerRefListID,ClassRefFullName, TemplateRefListID, RefNumber,  CreditMemoLineItemRefListID, CreditMemoLineDesc, CreditMemoLineRate,CreditMemoLineQuantity, CreditMemoLineAmount) values (?,?,?,?,?,?,?,?,?)"

cursor.execute(sql,(P1),(P2),(P3),(P4),(P5),(P6),(P7),(P8),(P9))

cursor.close()
stmt.close()

After changing the script, you can run the parameterized script without any issues.

CreditMemo was created in QuickBooks.

Problem Description:

I am using sp_report through QODBC & I have noticed that using QuickBooks UI, we can add multiple TxnFilterTypes like 'Invoice,' 'CreditMemo,' and 'SalesReciept,' etc.

How can I add the same using sp_report through QODBC?

Solution:

You can use multiple TxnFilterTypes as below:

TxnFilterTypes='Check', 'Invoice,' 'CreditMemo'

For Example:

sp_report ARAgingDetail show Text, Blank, TxnType, Date, RefNumber, PONumber, Name, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today,' TxnFilterTypes='Check,' 'Invoice,' 'CreditMemo,' AgingAsOf = 'Today.'

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into MS Excel Report.

The attached invoice is similar to what we need to export to MS Excel. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Excel format using QODBC.

You can do it through VBA in MS Excel.

The first step is to download Invoice Template in MS Excel.

You can download the template by clicking "New," & you can find the template under "Office.com Templates." Click on "Invoices" to see the list of Invoice templates.

You can download the template by selecting the template as per your requirement & click on the "Download" button to download the template. I have downloaded the "Service invoice (Garamond Gray Design)" template in this example.

The invoice Template is available with sample data. You can modify the template as per your requirement.

I have made changes to the template. I have changed some existing captions & added ITEM & ITEM DESCRIPTION. I have removed company information, logo, CUSTOMER ID, SALESPERSON, JOB, and LINE TOTAL.

We will fill out this information dynamically by VBA code. We will add a button asking for the RefNumber of Invoice by entering RefNumber VBA code will fetch Invoice detail for particular RefNumber & fill details in Invoice template.

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

You can add a button from the Developer tab in MS Excel. If the Developer tab is unavailable, follow the steps below to display it.

Click the "File" tab, and click "Options."

Click the "Customize Ribbon" category. In the "Main" Tabs list, select the "Developer" checkbox, and then click "OK."

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

A Command Button is added. I have renamed it to "Enter Invoice #." Double-click on the command button to write code.

Write the below code for export QuickBooks Invoices into MS Excel Report format.

Application Source Code:

Private Sub CommandButton1_Click()

ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C3").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("A18:A38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("B18:B38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("C18:C38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("D18:D38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("E18:E38").ClearContents
ActiveWorkbook.Sheets("ServiceInvoice").Range("F18:F38").ClearContents

Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
Dim InvNo As String

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

InvNo = InputBox("Enter RefNumber:", "InvNo")
If (InvNo = "") Then
    Exit Sub
End If
sSQL = "SELECT TxnID,CustomerRefFullName,TxnDate,RefNumber,BillAddressAddr1,BillAddressAddr2,BillAddressCity,BillAddressState,BillAddressPostalCode,ShipAddressAddr1,ShipAddressAddr2,ShipAddressCity,ShipAddressState,ShipAddressPostalCode,TermsRefFullName,DueDate,Subtotal,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount FROM InvoiceLine where RefNumber = '" & FnRef(InvNo) & "'"


Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.activeconnection = oConnection
oRecordset.Open sSQL

ActiveWorkbook.Sheets("ServiceInvoice").Activate
ActiveWorkbook.Sheets("ServiceInvoice").Range("F3").Value = oRecordset.Fields("TxnDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F4").Value = oRecordset.Fields("RefNumber")
ActiveWorkbook.Sheets("ServiceInvoice").Range("A3").Value = oRecordset.Fields("CustomerRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B4").Value = oRecordset.Fields("BillAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B5").Value = oRecordset.Fields("BillAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B6").Value = oRecordset.Fields("BillAddressCity") + " " + oRecordset.Fields("BillAddressState") + " " + oRecordset.Fields("BillAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B9").Value = oRecordset.Fields("ShipAddressAddr1")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B10").Value = oRecordset.Fields("ShipAddressAddr2")
ActiveWorkbook.Sheets("ServiceInvoice").Range("B11").Value = oRecordset.Fields("ShipAddressCity") + " " + oRecordset.Fields("ShipAddressState") + " " + oRecordset.Fields("ShipAddressPostalCode")
ActiveWorkbook.Sheets("ServiceInvoice").Range("D15").Value = oRecordset.Fields("TermsRefFullName")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F15").Value = oRecordset.Fields("DueDate")
ActiveWorkbook.Sheets("ServiceInvoice").Range("F39").Value = oRecordset.Fields("SubTotal")

Dim count As Integer
count = 0
Do While Not oRecordset.EOF
    ActiveWorkbook.Sheets("ServiceInvoice").Range("B" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineItemRefFullName")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("C" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineDesc")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("D" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineQuantity")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("E" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineRate")
    ActiveWorkbook.Sheets("ServiceInvoice").Range("F" + Trim(Str(18 + count))).Value = oRecordset.Fields("InvoiceLineAmount")
    count = count + 1
    oRecordset.MoveNext
Loop

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

End Sub

We need to add the below function for passing the RefNumber parameter value in the query.

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function


Function FnRef(MyRef As String) As String

MyRef = Nz(MyRef, "")
FnRef = MyRef
End Function

You can check functionality by clicking on the "Enter Invoice #" button.

MS Excel will prompt for RefNumber, as shown in the below sample. Enter a RefNumber value and click "OK."

The report is available for particular RefNumber.

You can print the report by clicking "Print." The Invoices in MS Excel report format are shown below.

Please click here to download Sample Report.

Problem Description:

I am running the QODBC report "CustomSummary" and comparing it to what should be the identical report in QuickBooks. I am encountering an issue where the Balance Sheet accounts report zero amounts (vs. the values I see in QuickBooks). In contrast, the Income Statement accounts report the correct amount.

Could you advise if there is a parameter I need to change when running the stored procedure?

The procedure I am running is as follows:

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All'

I have set up the below parameter QuickBooks UI.

Solution:

You are facing a mismatch issue because QuickBooks SDK is passing the parameter AccountFilterType='IncomeAndExpense' by default in the report query & you have removed the Account Filter parameter from QuickBooks UI.

There is no option to remove the default account filter from the report query.

This is a limitation of the QuickBooks SDK that it does not support removing the default account filter.

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

We have raised enhancement request QBWG-47383 at Intuit's end to support removing the default account filter through QuickBooks SDK.

As a workaround, You need to follow the below procedure.

Run the report twice & export it in two separate Excel sheets.

Refer: How to create sp_reports using Microsoft Excel

1st using the filter AccountFilterType= 'BalanceSheet' (returning all rows and columns)

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'BalanceSheet'

2nd, use the filter AccountFilterType='IncomeAndExpense' (returning all rows and columns).

sp_report CustomSummary show Label, Amount parameters DateMacro = 'LastYear', SummarizeRowsBy = 'Account', SummarizeColumnsBy = 'Month', Calendar = 'FiscalYear', ReturnRows = 'All', ReturnColumns = 'All',AccountFilterType= 'IncomeAndExpense'

Then join the two resulting datasets for the amount fields & update based on the logic of the 1st dataset amount minus the 2nd dataset amount (as the IncomeAndExpense amounts need to have their signs reversed). This produces the same result as running the Custom Summary report out of the UI with no AccountType filter.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Microsoft Access.

The attached invoice is similar to what we need to export to Access. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into MS Access report format using QODBC.

The first step is to create a new MS Access database & link the InvoiceLine table to MS Access.

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

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

Then Click "Create" to create a blank database.

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

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

Under the Get External Data window, you will get two options. We will use "Link to the data source by creating a linked table":

After selecting the access method and clicking OK, you will get a Select Data Source window.

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

For 64-bit MS Office, change QRemote DSN to QuickBooks Data 64-bit QRemote is the default DSN created when installing QODBC.

Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

The linked table was added to MS Access.

Now, We will design a new report from Create->Report Wizard

The Report Wizard shows showing Linked table & available fields.

Select the fields you want to show in the report & click "Next."

Report Wizard showing selected fields. Click "Next" for more configuration.

You can sort records in ascending or descending order up to four fields & click "Next."

Select report Layout & click "Next."

Insert the report name & select "Modify the report's design," & click "Finish" to design a report.

The report design window is opened & Drag the table field into the report as per your requirement & design the information.

Select table field assigns a group to table fields by clicking the "Add a Group" button.

The select field for Group. In this example, I have selected "TxnID" all group fields are grouped for a particular TxnID.

The Group is created. Click "More."

Select "With a header section" & "With a footer section" to enable Group Header & Footer.

If you want to print one invoice per page (i.e., Only one invoice on a single page), click on the group footer for TxnID & set the Force New Page property box drop-down arrow, and select Before Section.

The report is designed. You can add labels & assign a group to table fields. You can view the information by clicking on "View."

The actual report will look as below in View mode.

You can select "Print Preview" mode to see all Invoice reports.

The Invoices in MS Access report format are shown below.

Please click here to download Sample Report.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into Crystal Reports.

The attached invoice is similar to what we need to export to Crystal Report. It doesn't have to be exact but needs to be able to show the descriptions, etc.

Solution:

You can export QuickBooks Invoices into Crystal Report format using QODBC.

The first step is to create a new report.

The Database Expert Window will be open. Expand the "Create New Connection" Folder:

Expand the "ODBC (RDO)" Folder:

The following screen will appear. Select "QuickBooks Data" DSN and Click "Next," finally click "Finish" on this screen:

There is no UserID and Password required for connecting QuickBooks Data using QODBC. So you can click "Finish" directly.

A list of QuickBooks tables will appear and is ready for selection:

Crystal Report will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. In this example, I am selecting the InvoiceLine table.

For the table added to Crystal Report, click "Next" to add table fields in the report.

The Report Wizard shows showing table & available fields.

Now, Select the field which you want to show in the report by clicking the ">" button & click "Next."

Add group information to the report. We will group data by selecting the TxnID field and, clicking the ">" button, then clicking "Finish."

The report designer will show selected fields.

Now, We will add Customer details, Billing & Shipping details, TxnDate, RefNumber, Terms, and DueDate in the Group Header section. We will add InvoiceLine information (i.e., Item, Description, Quantity, Rate & Amount field) in the detail section. We will add SubTotal in the Group Footer section.

You can add the caption in report data using Text Object from Menu Insert >> Text Object.

In this example, I have added the below Text Objects.

You can draw the box in the report using Menu Insert >> Box.

In this example, I have drawn a box around the "Ship To" details

You can draw a line in the report using Line from Menu Insert >> Line.

I have drawn the below line in the "Ship To" Box.

Same way, I have drawn Box & Line on other report details.

If you want to print one invoice per page (i.e., Only one invoice on a single page), then select "Section Expert" & click on the Group Footer, enable the "New Page After" drop-down arrow, and click "OK."

You can view the report by clicking on the "Preview" tab.

The actual report will look like the below sample in Preview mode.

You can add the RefNumber parameter in the report. So you can see the invoice for a particular Invoice#. You can add the RefNumber parameter in the information to limit the records or view a single invoice.

You can add parameters by right-clicking on "Parameter Fields" & click "New."

Insert the parameter name, Choose "Static" as a list of values & choose "RefNumber" in the value field and click "OK."

Add a parameter to the report by dragging and dropping.

We need to link the RefNumber parameter with the RefNumber value in the report.

For linking, Right-click on "RefNumber" & click "Select Expert...".

In the select expert window, select "is equal to" & "{?RefNumber}" parameter & click "OK."

When you run the report, the crystal report will prompt for RefNumber, as shown in the sample below. Enter a RefNumber value and click "OK."

The report available for particular RefNumber.

Please click here to download Sample Report.

Problem Description:

I am using QuickBooks Enterprise & I want to use QODBC read-only edition for QuickBooks Enterprise.

How can I install QODBC read-only edition for QuickBooks Enterprise Edition?

Solution:

You can download QODBC by clicking here

After downloading QODBC, Install QODBC on your machine.

Refer: QODBC Installation Steps

After installation of QODBC, You can verify QODBC installation from QuickBooks Enterprise:

File >> Utilities >> Configure ODBC

If QODBC is appropriately installed, then QODBC Setup Screen will open. Navigate to the "About" tab & you will see QODBC with a 30-day evaluation.

QODBC is installed on a new machine. It starts with a 30-day evaluation (All features are active). QODBC evaluation version will turn to the Read Only edition for QuickBooks enterprise edition – automatically after 30 days.

After 30 days, you will see "QODBC Read Only 2017 All Regions" in the "About" tab.

Sample Application:

Please click here for downloading Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using VB.NET.

Run the application & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

Imports System.Data.Odbc
Imports System.Drawing

Public Class _Default
    Inherits Page
     Private Shared _cn As OdbcConnection

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        lblErrorMessage.Visible = False
    End Sub
    Protected Sub btnConnect_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing) Then
                    lblConnectionStatus.Text = "Disconnecting...."
                    _cn.Close()
                    _cn.Dispose()
                    btnConnect.Text = "Connect"
                    lblConnectionStatus.Text = "Not Connected"
                    lblConnectionStatus.ForeColor = Color.DarkRed
                End If
            Else
                If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                    lblConnectionStatus.Text = "Connecting...."
                    _cn = New OdbcConnection(String.Format("DSN={0}", cboListOfDSN.Text))
                    _cn.ConnectionTimeout = 60
                    _cn.Open()
                    btnConnect.Text = "Disconnect"
                    lblConnectionStatus.Text = "Connected"
                    lblConnectionStatus.ForeColor = Color.Green
                End If
            End If
        Catch ex As Exception
            btnConnect.Text = "Errorred"
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
        
    End Sub

    Protected Sub btnInsertInvoiceDescription_Click(ByVal sender As Object, ByVal e As EventArgs)

        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
            btnConnect.Text = "Connect"
            lblConnectionStatus.Text = "NotConnected"
            lblConnectionStatus.ForeColor = Color.DarkRed
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            lblErrorMessage.Text = "Invoice Refer Number is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            lblErrorMessage.Text = "New Line Description is required"
            lblErrorMessage.Visible = True
            'MessageBox.Show("New Line Description is required")
            Return
        End If

        InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text)
        DisplayInvoiceInGrid(txtDInvoiceNo.Text)
    End Sub

    Protected Sub btnInvoiceInsertItem_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
                btnConnect.Text = "Connect"
                lblConnectionStatus.Text = "NotConnected"
                lblConnectionStatus.ForeColor = Color.DarkRed
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                'MessageBox.Show("Invoice Refer Number is required")
                lblErrorMessage.Text = "Invoice Refer Number is required"
                lblErrorMessage.Visible = True
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                'MessageBox.Show("New Line Item Full Name is required")
                lblErrorMessage.Text = "New Line Item Full Name is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIQuantity.Text)) Then

                'MessageBox.Show("Invoice Refer Quantiy is required")

                lblErrorMessage.Text = "Invoice Refer Quantiy is required"
                lblErrorMessage.Visible = True
                Return
            End If

            If (String.IsNullOrEmpty(txtIRate.Text)) Then

                'MessageBox.Show("New Line Rate is required")
                lblErrorMessage.Text = "New Line Rate is required"
                lblErrorMessage.Visible = True
                Return
            End If
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, Integer.Parse(txtIQuantity.Text), Integer.Parse(txtIRate.Text), txtIDescription.Text)
            DisplayInvoiceInGrid(txtIInvoiceNo.Text)

        Catch ex As Exception
            'MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
            lblErrorMessage.Text = String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace)
            lblErrorMessage.Visible = True
        End Try
    End Sub
    Private Sub InsertInvoiceLineItem(invoiceRefNumber As String, itemFullName As String, quanity As Int16, rate As Int16, description As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then

            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty
        End If
        QBdrdr.Close()


        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub InsertInvoiceLineDescriptionOnly(invoiceRefNumber As String, newItemDescription As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String
        query = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then
            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty

        End If
        QBdrdr.Close()

        If (String.IsNullOrEmpty(txnID)) Then
            'MessageBox.Show("No Invoice Found")
            lblErrorMessage.Text = "No Invoice Found"
            lblErrorMessage.Visible = True
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub DisplayInvoiceInGrid(invoiceRefNumber As String)
        Dim query As String = String.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)
        ProcessQuery(query)
    End Sub
    Private Sub ProcessQuery(query As String)
        Dim cmd = New OdbcCommand(query, _cn)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        Dim myTable As DataTable = New DataTable()
        myTable.Load(reader)
        grvData.AutoGenerateColumns = True
        grvData.DataSource = myTable
        grvData.DataBind()
    End Sub


End Class

Keywords: sample .net, sample, .net, Dot Net, VB.Net

Sample Application:

Please click here for downloading Sample Code.

Please refer below steps for using an application for Inserting InvoiceLine to existing Invoice using VB.NET.

, You can change DSN as per your configuration. In this example, I am connecting to default DSN QuickBooks Data. Run the application, select DSN & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line which will add a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing  Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Full Item Name, Quantity, Rate & Description which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

Imports System.Data
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data.Odbc
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Windows.Forms
Imports System.Windows.Forms.VisualStyles

Public Class Form1

    Dim _cn As OdbcConnection
    Private Sub btnConnect_Click(sender As Object, e As EventArgs) Handles btnConnect.Click
        Try
            Application.DoEvents()
            If (btnConnect.Text.Equals("Disconnect")) Then
                If (_cn IsNot Nothing OrElse IsDBNull(_cn)) Then
                    lblConnectionStatus.Text = "Disconnecting...."
                    _cn.Close()
                    _cn.Dispose()
                    btnConnect.Text = "Connect"
                    lblConnectionStatus.Text = "Not Connected"
                    lblConnectionStatus.ForeColor = Color.DarkRed
                End If
            Else
                If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                    lblConnectionStatus.Text = "Connecting...."
                    _cn = New OdbcConnection(String.Format("DSN={0}", cboListOfDSN.Text))
                    _cn.ConnectionTimeout = 60
                    _cn.Open()
                    btnConnect.Text = "Disconnect"
                    lblConnectionStatus.Text = "Connected"
                    lblConnectionStatus.ForeColor = Color.Green
                End If
            End If
            Application.DoEvents()
        Catch ex As Exception
            Application.DoEvents()
            btnConnect.Text = "Errorred"
            MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
        End Try
    End Sub

    Private Sub button1_Click(sender As Object, e As EventArgs) Handles button1.Click
        Me.Close()
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ListOutDSN()
    End Sub

    Private Sub ListOutDSN()

        cboListOfDSN.Items.Clear()
        Dim myOdbcKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next

        myOdbcKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", False).GetValueNames()

        For Each item As Object In myOdbcKeys
            cboListOfDSN.Items.Add(item)
        Next


        If (cboListOfDSN.Items.Count > 0) Then
            For Each item As String In cboListOfDSN.Items
                If (item.Equals("QuickBooks Data")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data POS")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                If (item.Equals("QuickBooks Data Online")) Then
                    cboListOfDSN.Text = item
                    Return
                End If
                cboListOfDSN.SelectedIndex = 0
            Next
        End If
    End Sub



    Private Sub btnInsertInvoiceDescription_Click(sender As Object, e As EventArgs) Handles btnInsertInvoiceDescription.Click
        'If (IsNothing(_cn) Or _cn.State = ConnectionState.Closed) Then
        'btnConnect_Click(Nothing, Nothing)
        'End If
        If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
            btnConnect_Click(Nothing, Nothing)
        End If
        If (String.IsNullOrEmpty(txtDInvoiceNo.Text)) Then
            MessageBox.Show("Invoice Refer Number is required")
            Return
        End If
        If (String.IsNullOrEmpty(txtDDescription.Text)) Then
            MessageBox.Show("New Line Description is required")
            Return
        End If

        InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text)
        DisplayInvoiceInGrid(txtDInvoiceNo.Text)

    End Sub

    Private Sub InsertInvoiceLineDescriptionOnly(invoiceRefNumber As String, newItemDescription As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String
        query = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then
            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty

        End If
        QBdrdr.Close()

        If (String.IsNullOrEmpty(txnID)) Then
            MessageBox.Show("No Invoice Found")
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

    Private Sub DisplayInvoiceInGrid(invoiceRefNumber As String)
        Dim query As String = String.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)
        ProcessQuery(query)
    End Sub

    Private Sub ProcessQuery(query As String)
        Dim cmd = New OdbcCommand(query, _cn)
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        Dim myTable As DataTable = New DataTable()
        myTable.Load(reader)
        grvData.AutoGenerateColumns = True
        grvData.DataSource = myTable
    End Sub


    Private Sub btnInvoiceInsertItem_Click(sender As Object, e As EventArgs) Handles btnInvoiceInsertItem.Click

        Try

            If (_cn Is Nothing OrElse _cn.State = ConnectionState.Closed) Then
                btnConnect_Click(Nothing, Nothing)
            End If
            If (String.IsNullOrEmpty(txtIInvoiceNo.Text)) Then
                MessageBox.Show("Invoice Refer Number is required")
                Return
            End If
            If (String.IsNullOrEmpty(txtIItemFullName.Text)) Then

                MessageBox.Show("New Line Item Full Name is required")
                Return
            End If

            If (String.IsNullOrEmpty(txtIQuantity.Text)) Then

                MessageBox.Show("Invoice Refer Quantiy is required")
                Return
            End If

            If (String.IsNullOrEmpty(txtIRate.Text)) Then

                MessageBox.Show("New Line Rate is required")
                Return
            End If
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, Integer.Parse(txtIQuantity.Text), Integer.Parse(txtIRate.Text), txtIDescription.Text)
            DisplayInvoiceInGrid(txtIInvoiceNo.Text)

        Catch ex As Exception
            MessageBox.Show(String.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace))
        End Try

    End Sub

    Private Sub InsertInvoiceLineItem(invoiceRefNumber As String, itemFullName As String, quanity As Int16, rate As Int16, description As String)
        Dim QBdrdr As OdbcDataReader
        Dim txnID As String
        Dim query As String = String.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber)

        Dim QBEmployeecmd As OdbcCommand = New OdbcCommand(query, _cn)

        QBEmployeecmd.CommandType = CommandType.Text

        QBdrdr = QBEmployeecmd.ExecuteReader()


        If (QBdrdr.HasRows = True) Then

            txnID = QBdrdr("txnid").ToString()
        Else
            txnID = String.Empty
        End If
        QBdrdr.Close()


        If (String.IsNullOrEmpty(txnID)) Then
            MessageBox.Show("No Invoice Found")
        Else
            query = String.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description)
            Dim QBEmployeecmd2 As OdbcCommand = New OdbcCommand(query, _cn)

            QBEmployeecmd2.CommandType = CommandType.Text
            QBEmployeecmd2.ExecuteNonQuery()

        End If
    End Sub

End Class

Keywords: sample .net, sample, .net, Dot Net, VB.Net

Typical Profit & Loss Budget Overview By Class Report (for the current Fiscal Year to Date) in QuickBooks

Note: The QuickBooks Sample files set the QuickBooks date to December 15, 2021. The current Fiscal Year shown above is January 1 through December 15, 2021.

Report Displayed in QODBC

Open Microsoft Query in Excel

Note: Make sure you have installed Microsoft Query Add-on first. To learn how to install Microsoft Query, please refer to Microsoft Office with MS Query and QODBC.

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.

Please start Microsoft Excel & Create a blank workbook.

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

Create a New Microsoft Query in Excel

Select the DSN you want to use to connect to the QuickBooks file. (Here, I choose the default data source with a sample company file opened.)

Note: Uncheck option "Use Query Wizard to Created/edit Queries" since we do not need to import any QuickBooks tables here.

Close the "Add Tables" window by clicking the "Close" button.

Press the "SQL" button to input your sp_report query as below. Here we take a ProfitAndLossBudgetOverview report as an example:

sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2021, BudgetCriterion = 'AccountsAndClasses', SummarizeBudgetColumnsBy = 'Class', SummarizeBudgetRowsBy = 'Account'

Press an OK button to get results in Microsoft Query:

Return Records to Excel Spreadsheet

Select Menu File -> Return Data to Microsoft Office Excel to return records to Excel Spreadsheet.

Choose how you want to import data into your workbook & click "OK."

Data exported in the workbook.

Note: QuickBooks does not support creating a budget by class or other criteria for the Balance Sheet. So it is not available through QODBC reports (i.e., BalanceSheetBudgetOverview & BalanceSheetBudgetVsActual).

Refer: Can you create a budget for balance sheet items by class?

How to identify exported report data relation.

Please refer below screenshot for exported report data relation.

Also, Refer

How to use the QuickBooks Reporting Engine with QODBC Online

QODBC Report Parameter & Schema details.

Problem Description:

I am using QRemote to access QuickBooks Data & I want to add security when accessing my QuickBooks Data through QRemote.

How can I use Encryption in QRemote?

Solution 1:

QRemoteServer has an Encryption feature that will send/receive data between the client & server in an encrypted format. So you can use this feature.

The default Encryption Key/Password is set to "QODBC#QRemote$1". If you want to change your Encryption Key/Password, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN.

By default, the Encryption feature in QRemote is disabled. 

So if you want to use Encryption, You can set up Encryption in QRemoteServer & QRemote Client.

First, We will turn on encryption in the QRemoteServer.

You can find the QRemote server running in the System Tray. Double-click the QRemoteServer icon from the system tray to maximize the QRemoteServer window.

If QRemoteServer is not running, then you can start QRemoteServer from: Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC.

QRemoteServer is running with the default configuration.

Navigate to File >> Setting

The setting window will open. Navigate to the "Encryption" tab.

Select the "On" option & enter your desired encryption key in the box and click the "Apply" button to save the configuration. Note: By Default Encryption Key/Password is set to "QODBC#QRemote$1"

QRemoteServer configuration is saved. We need to restart QRemoteServer to load the new configuration.

To exit/quit QRemoteServer, go to File >> Exit.

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

QRemoteServer is running.

Now we will set up Encryption in QRemote Client DSN.

You can open 32-bit QRemote Client DSN from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source

Note: If you have a 64-bit application, then You can open 64-bit QRemote Client DSN from Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit

32-bit ODBC manager is opened. Navigate to the "System DSN" tab & click on your DSN, and hit configure. I am configuring encryption in 32-bit default QRemote Client DSN (QuickBooks Data QRemote) in this tutorial.

Enter the Encryption Key/Password which you have set up in QRemoteServer. QRemote client will be open with Default Encryption Key/Password "QODBC#QRemote$1". In this example, I have used the default Encryption Key/Password "QODBC#QRemote$1".

By Default, Encryption is set to Off. To enable Encryption, Select the "On" option & click on "Test Connection."

The test connection is successful.

If your encryption key on the QRemote client is not matched with QRemote Server or the Encryption option (i.e., On/Off) is not matched with QRemote Server. You will receive the error "QRemote Client Encryption Key does not match with the one specified on QRemote Server."

If you don't want to use the Encryption key, please set the Encryption option "Off" on QRemote Client & QRemote Server.

Solution 2:

Delete the file "QRemoteServerConfig.ini" from the below-listed location:

C:\ProgramData\QODBC Driver for QuickBooks

After deleting the file "QRemoteServerConfig.ini," exit QRemote Server

If you are using QRemote Remotely or an SQL server with QRemote from another machine, perform the above steps on that machine.

Please do not change the default encryption option or setting, try connecting it again.

Problem Description:

I have a server where multiple users are logged in.

I want to run QRemoteServer for each Windows User.

Can I configure QRemoteServer user-wise? So QRemote can process user-wise requests.

Solution:

QRemoteServer has Windows user-wise configuration features. So you can use this feature for running QRemoteServer for each Windows User.

You need to configure QRemoteServer for each Windows user you want to use QRemoteServer.

You need to configure QRemoteServer on different IPs/Ports for each Windows. So conflict does not happen between any Windows Users.

For Example: If you have three Windows Users & you want to use QRemoteServer for each Windows User, then you need to configure QRemoteServer for each user.

In this tutorial, I demonstrate how to use QRemoteServer for three different Windows Users.

I have three Windows Users, as shown below.
1. Administrator
2. Jack
3. John

First, We need to log in with the User 1 (i.e., Administrator) account. User 1 will use the default configuration of QRemoteServer (i.e., Default IP & Port).

We will not change the configuration for User 1. We will change the configuration of User 2 & User 3.

We will exit QRemoteServer from User 1. So it will not create conflict in other users' QRemoteServer configuration.

You can find the QRemote Server running in the System Tray. Double-click on the QRemoteServer icon to open it

QRemoteServer is running with the default configuration.

To exit/quit QRemoteServer, go to File >> Exit.

Now we will log in with User 2 (i.e., Jack) & configure QRemoteServer. We will configure QRemoteServer on port 4501 for User 2.

Open QRemoteServer from System Tray & click on File >> Setting

Setting Window will open. Navigate to the "Advanced" tab & Enable the "Windows user-wise configuration" option & change the Port to the specified port & enter port no 4501 in the box and click the "Apply" button to save the configuration.

QRemoteServer configuration is saved. We need to restart QRemoteServer to load the new configuration.

To exit/quit QRemoteServer, go to File >> Exit.

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

QRemoteServer is running on Port 4501 for User 2 (i.e., Jack).

Now we will log in with User 3 (i.e., John) & configure QRemoteServer. We will configure QRemoteServer on port 4502 for User 3.

Open QRemoteServer from System Tray & click on File >> Setting

Setting Window will open. Navigate to the "Advanced" tab & Enable the "Windows user-wise configuration" option & change Port to the specified port & enter port no 4502 in the box and click the "Apply" button to save the configuration.

QRemoteServer configuration is saved. We need to restart QRemoteServer to load the new configuration.

To exit/quit QRemoteServer, go to File >> Exit.

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

QRemoteServer runs on Port 4502 for User 3 (i.e., John).

By repeating the above process, you can configure QRemoteServer for multiple users.

Also, refer to the following:

Getting Error Unable to Bind to Specified IP Address

Listening failed. QRemote Server shuts down in 15 Sec.

Problem Description:

I could link all tables to an MS Access DB from QuickBooks Database.

But I got the following error when I tried to open the ItemAssembliesCanBuild table.

ERROR [00000] [QODBC] Error: 3120 - Object "X" specified in the request cannot be found.

Solution:

ItemAssembliesCanBuild Query does require ItemInventoryAssemblyRef(ListID or FullName) and TxnDate in the WHERE Clause to perform the query in QuickBooks SDK.

You will get this error when you execute a query directly without specifying ItemInventoryAssemblyRef(ListID or FullName) and TxnDate.

So it would be best to execute the query with ItemInventoryAssemblyRef(ListID or FullName) and TxnDate through a custom query.

For Example:
SELECT * FROM ItemAssembliesCanBuild where ItemInventoryAssemblyRefFullName='Inventory Door Kit' and TxnDate={d'2017-12-15'}

It would be best if you wrote the query in MS Access from MS Access >> create tab >> Query Design.

Select Pass-Through >> Write query >> Execute the query using the Run button.

Select DSN & execute the query.

Query executed successfully & result available.

Problem Description: 

I have changed the setting in QODBC Setup Screen from Open mode to the same as the company file, but my Excel still does not takes the changes and needs the company file to be in multi-user mode.

When I refresh the Excel sheet, I get below error:

Solution:

MS Excel is storing connection string & due to this, you are facing this error.

You need to update the connection string in Excel to solve this issue.

Please open the MS Excel file and go to the "Data" tab >> Click on "Connection."

Now select connection & Click on "Properties."

Navigate to the Definition tab & You will find the Connection String box. You will notice that the connection string contains OpenMode=M, which will try to open the company file Multiuser mode.

You need to change OpenMode=M; to OpenMode=F; in the connection string box & click "OK."

By changing it to OpenMode=F, the Excel connection will follow company file mode, whatever is selected in QuickBooks.

Problem Description:

We updated to the latest QuickBooks 2017 Patch, officially known as Release R5P. After running seemingly OK for two days, QODBC is *extremely* sluggish, to the point of being unusable.

Suspecting possible optimizer cache issues, I removed the optimizer files and started rebuilding them via a manual call to rebuild InvoiceLine. After getting through 3000 invoices, QODBC just sat there, going no further. After 15 minutes, I ended up killing off the process. The QuickBooks desktop process took up 60% of the server's CPU time, so I had to reboot everything.

I then ran a Rebuild on the database and am attempting another rebuild to see if that helps. Reloading the optimizer data for tables line Item works very quickly. Are there any other suggestions for getting this working correctly again?

Solution:

After upgrading to R5, if you are facing slowness compared to R4, this may be because QBSDK or QuickBooks R5 is performing slowly.

To resolve this issue,

1)Check for QuickBooks Releases - See if any newer release is available.

Update to the latest release of QuickBooks.

Update:

Intuit released a hotfix ("ULIP" update, off cycle) on April 11, 2017. Please update the QuickBooks application to the latest release. After the update, You should see the release as being R5_84.

2)Contact Intuit Support.

You can get in touch with Intuit using the below link & report the issue.

Intuit Support

Intuit Community Support

If you are still facing the issue.

3)Downgrade to QuickBooks 2017 R4 update.

Before you downgrade QuickBooks 2017 to R4, please raise a support ticket with Intuit and see if any other options are available.
Since there is no way to roll back just one update, You need to uninstall QuickBooks & reinstall QuickBooks up to the R4 update. You can use your R5 updated company file in R4. You will receive the warning to update QuickBooks y pressing "NO" in the warning box. You can use R5 updated company file in R5.

Also, You will receive a popup regarding QuickBooks Update. It would be best if you kept saying NO to the update until R6 comes out.

Problem Description:

We would wish to inactive items that have not sold after a certain date. Is this possible? If so, any help would be appreciated.

Solution:

1) Get the list of Sold Items by exporting the SalesByItemSummary report for a certain period in MS Excel as below

For Example:
sp_report SalesByItemSummary show Text, Label, Quantity, Amount, Percent, AveragePrice parameters DateFrom = {d '2016-06-01'}, DateTo = {d '2016-12-31'}

Please refer to How to create sp_reports using Microsoft Excel for exporting reports into excel.

By running the above query, you will get ItemType + Parent Item Name in the Text column & ItemName + description in the Label column.

We will split ItemName from the description using the "Text to Columns" option. We will select the "Label" column & click on "Text to Columns."

Choose "Delimited" and click "Next."

Select "Other" and write "(" (i.e., Opening Brace) and click "Next." We are splitting data into different columns from Opening Brace.

You can see split data from the Data preview. Now click "finish."

Click "OK" to replace destination cell contents.

Data is Split into two columns.

Now we will add a new column & use the formula for trimming whitespace from the Child Item name & removing the prefix "Total" from the Parent Item name.

=TRIM(SUBSTITUTE(C2,"Total","",1))

The formula is applied to all rows. Now we have furnished the Item Name.

2) Now We have the Item Name & Item type for sold items. Now we need to export Item tables depending on Item Type (i.e., ItemInventory, ItemNonInventory, ItemService, etc.).

In this example, We will export the ItemInventory table in sheet2 & we will make inactive ItemInventory which is unsold for a certain period.

Please refer to Using QuickBooks Data with Excel for exporting the ItemInventory table into excel.

The ItemInventory table is exported to sheet2.

Now we will go to sheet 3 & use vlookup formula for getting a list of unsold Items.
=IF(IFERROR(VLOOKUP(TRIM(Sheet2!E1), Sheet1!B: B,1, FALSE), "ERROR")=" ERROR," Sheet2!E1,"")

The formula is applied to all rows. Now we have the name of unsold ItemInventory.

3) Now, We have the name of unsold ItemInventory. You can make it inactive by running the below update query.

Update ItemInventory set IsActive = False where Name = 'Cabinet Pulls'

You can also use VBA to make all unsold Items inactive. Please refer to Using QuickBooks Data with VBA and write a VBA script per your requirement.

Result in QuickBooks.

The sYou can repeat the above steps for Item Type like ItemNonInventory, ItemService, etc...

Problem Description:

For months I've been trying to get a Sales journal report. All I seem to get is either too detailed (by items) or too summary (sum by customer). I need a list of invoices by class.

A report by class showing CLASS, Inv #, Inv DATE, CUSTOMER NAME, Inv TOTAL AMT).

All I seem to get is either too detailed (by items) or too summary (sum by customer).

I haven't been able to figure out how. I'm extracting this info from a GL data dump, but I have to use lots of code and work around.

Is there such a report?

Solution:

You can refer SalesByCustomerDetail for the required details. Please refer to the sample query for the same.

For Example:

sp_report SalesByCustomerDetail show Text, Blank, TxnType, Date, RefNumber, Memo, Name, Item, Class, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate.'

Also, There are other Sales reports available you can refer below-listed reports:

SalesByCustomerSummary, SalesByItemDetail, SalesByItemSummary, SalesByRepDetail, SalesByRepSummary

Also Refer: How to use the QuickBooks Reporting Engine with QODBC

You can find most of the QuickBooks report using the above link. You can change report parameters as per your requirement.

Also, Refer QODBC table & report schema site to get details about each report column and supported parameters.

This article shows how you can easily integrate the QODBC driver for QuickBooks into Layer2 Cloud Connector Solution.

1. Open Connection Manager by clicking on "Start Connection Manager."

Connection Manager opened.

2. Add a new connection by right-clicking on "Connection Manager" & click on "Create New Connection."

3. Enter the Connection Name & click on "Save Changes."

4. To connect to QuickBooks, the data entity must be configured in the Layer2 Cloud Connector Connection Manager. Select "Odbc Data Provider" as a Data Provider & insert DSN=QuickBooks Data QRemote; OLE DB Services=-2; as a Connection String and click on "Verify Connection String."

Connection Verified.

Please Note: If you have a 64-bit application, you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote," as shown in the screenshot below.
Please refer: How to configure QRemote

5. Enter SQL Statement and click on "Verify Select Statement."

Select Statement Verified.

6. Enter Primary Key and click on "Verify Primary Key."

Primary Key Verified. Click on "Save Changes" to save the connection property.

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using the application for Inserting InvoiceLine to existing Invoice using C#.NET.

Run the application & click on the "Connect" button.

The application should get connected to QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the existing Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, Full Item Name, Quantity, Rate & Description, which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    private static OdbcConnection _cn;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            //ListOutDSN();
            lblErrorMessage.Visible = false;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {

        }
    }
      protected void btnConnet_Click(object sender, EventArgs e)
    {
        try
        {
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }

        }
        catch (Exception ex)
        {
            btnConnect.Text = "Errorred";
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));            
        }
    }
    protected void btnInsertInvoiceDescription_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtDInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtDDescription.Text))
            {
                lblErrorMessage.Text = "New Line Description is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Description is required");
                return;
            }
            InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text);
            DisplayInvoiceInGrid(txtDInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }
    protected void btnInvoiceInsertItem_Click(object sender, EventArgs e)
    {
        try
        {
            if (_cn == null || _cn.State == ConnectionState.Closed)
            {
                btnConnet_Click(null, null);
                btnConnect.Text = "Connect";
                lblConnectionStatus.Text = "NotConnected";
                lblConnectionStatus.ForeColor = Color.DarkRed;
            }
            if (string.IsNullOrEmpty(txtIInvoiceNo.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Number is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Number is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIItemFullName.Text))
            {
                lblErrorMessage.Text = "New Line Item Full Name is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Item Full Name is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIQuantity.Text))
            {
                lblErrorMessage.Text = "Invoice Refer Quantiy is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("Invoice Refer Quantiy is required");
                return;
            }
            if (string.IsNullOrEmpty(txtIRate.Text))
            {
                lblErrorMessage.Text = "New Line Rate is required";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("New Line Rate is required");
                return;
            }
            InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, int.Parse(txtIQuantity.Text), int.Parse(txtIRate.Text), txtIDescription.Text);
            DisplayInvoiceInGrid(txtIInvoiceNo.Text);
        }
        catch (Exception ex)
        {
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;
            //MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
        }
    }

        private void InsertInvoiceLineDescriptionOnly(string invoiceRefNumber, string newItemDescription)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                    lblErrorMessage.Text = "Insert Successfully(Description Only)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }

        }


        private void InsertInvoiceLineItem(string invoiceRefNumber, string itemFullName, int quanity, int rate, string description)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();
                    lblErrorMessage.Text = "Insert Successfully (Inventory/Service)";
                    lblErrorMessage.Visible = true;
                }
            }
            else
            {
                lblErrorMessage.Text = "No Invoice Found";
                lblErrorMessage.Visible = true;
                //MessageBox.Show("No Invoice Found");
            }
        }


        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);
            //OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            //adapter.Fill(dataSet);
            OdbcDataReader reader = cmd.ExecuteReader();
            //if (reader.HasRows)
            {
                DataTable myTable = new DataTable();
                myTable.Load(reader);
                grvData.AutoGenerateColumns = true;
                grvData.DataSource = myTable;
                grvData.DataBind();

            }

        }

        private void DisplayInvoiceInGrid(string invoiceRefNumber)
        {

            string query = string.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            ProcessQuery(query);
        }
    }

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

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using the application for Inserting InvoiceLine to existing Invoice using C#.NET.

Run the application, select DSN & click on the "Connect" button. You can change DSN as per your configuration. You can change DSN as per your configuration. In this example, I am connecting to default DSN QuickBooks Data.

An application connected with QuickBooks.

The application has two functionality:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Item Full Name, Quantity, Rate & Description which you want to enter and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace QODBC_Invoice_Insert_Sample
{
    public partial class Form1 : Form
    {
        private OdbcConnection _cn;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                ListOutDSN();
                Application.DoEvents();
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
            
        }

        private void ListOutDSN()
        {
            cboListOfDSN.Items.Clear();

            var myODBCKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();

            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            myODBCKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();
            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            //end of adding DSN to list

            if (cboListOfDSN.Items.Count > 0)
            {
                foreach (string item in cboListOfDSN.Items)
                {
                    if (item == "QuickBooks Data")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data POS")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data Online")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                }
                cboListOfDSN.SelectedIndex = 0;
            }



        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {

                Application.DoEvents();
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }
            Application.DoEvents();
            }
            catch (Exception ex)
            {
                Application.DoEvents();
                btnConnect.Text = "Errorred";
                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }

        }

        private void btnInsertInvoiceDescription_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtDInvoiceNo.Text))
                {
                    MessageBox.Show("Invoice Refer Number is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtDDescription.Text))
                {
                    MessageBox.Show("New Line Description is required");
                    return;
                }
                InsertInvoiceLineDescriptionOnly(txtDInvoiceNo.Text, txtDDescription.Text);
                DisplayInvoiceInGrid(txtDInvoiceNo.Text);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
           
           
        }

        private void DisplayInvoiceInGrid(string invoiceRefNumber)
        {

            string query = string.Format("select RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount  from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            ProcessQuery(query);
        }

        private void InsertInvoiceLineDescriptionOnly(string invoiceRefNumber, string newItemDescription)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineDesc) values('{0}','{1}') ", txnID, newItemDescription);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                }
            }
            else
            {
                MessageBox.Show("No Invoice Found");
            }

        }

        private void btnInvoiceInsertItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtIInvoiceNo.Text))
                {
                    MessageBox.Show("Invoice Refer Number is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIItemFullName.Text))
                {
                    MessageBox.Show("New Line Item Full Name is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIQuantity.Text))
                {
                    MessageBox.Show("Invoice Refer Quantiy is required");
                    return;
                }
                if (string.IsNullOrEmpty(txtIRate.Text))
                {
                    MessageBox.Show("New Line Rate is required");
                    return;
                }
                InsertInvoiceLineItem(txtIInvoiceNo.Text, txtIItemFullName.Text, int.Parse(txtIQuantity.Text), int.Parse(txtIRate.Text), txtIDescription.Text);
                DisplayInvoiceInGrid(txtIInvoiceNo.Text);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
           
        }

        private void InsertInvoiceLineItem(string invoiceRefNumber, string itemFullName, int quanity, int rate, string description)
        {
            OdbcDataReader QBdrdr;
            string txnID = string.Empty;
            string query = string.Format("select txnid from InvoiceLine where RefNumber='{0}'", invoiceRefNumber);
            //select the invoice and get the txnid from the existing Invoice.
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;

                QBdrdr = QBEmployeecmd.ExecuteReader();


                if (QBdrdr.HasRows == true)
                {
                    txnID = QBdrdr["txnid"].ToString();

                }
                else
                {
                    txnID = string.Empty;
                }
                QBdrdr.Close();

            }
            if (!string.IsNullOrEmpty(txnID))
            {
                query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();

                }
            }
            else
            {
                MessageBox.Show("No Invoice Found");
            }
        }

        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);
            //OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataSet dataSet = new DataSet();
            //adapter.Fill(dataSet);
            OdbcDataReader reader = cmd.ExecuteReader();
            //if (reader.HasRows)
            {
                DataTable myTable = new DataTable();
                myTable.Load(reader);
                grvData.AutoGenerateColumns = true;
                grvData.DataSource = myTable;
                
            }

        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (_cn != null && _cn.State != ConnectionState.Closed)
            {
                _cn.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

    }
}

Keywords: sample .net, sample, .net, Dot Net, C#,C#.Net

Sample Application:

Please click here to download Sample Code.

Please refer below steps for using the application for Inserting InvoiceLine to existing Invoice using PHP.

Run application.

The application has two functionality:

1. Append the existing Invoice with a new Description Line, adding a new Description Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice & description which you want to enter and click on the "Insert New Invoice Line (Description Only)" button.

New Description Line is added to the existing Invoice.

Result in QuickBooks.

2. Append the existing Invoice with a new ItemInventory/ItemService Line, adding a new ItemInventory/ItemService Line to the current Invoice.

You need to insert the RefNumber (i.e., Invoice#) of the existing Invoice, the Full Item Name, Quantity, Rate & Description which you want to enter, and click on the "Insert New Invoice Line (Inventory/Service)" button.

New Item Line is added to the existing Invoice.

Result in QuickBooks.

Application Source Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>QODBC PHP Script To Display SQL Results</title>

</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">


<table>
	<tr>
		<td>
			Append existing Invoice with a new Description line
			<form name="frmDescriptionLine" id="frmDescriptionLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtInvoiceNo" id="txtInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter New Item Description</td>
					<td><input type="text" name="txtDescription" id="txtDescription"style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnDescriptionLine" id="btnDescriptionLine" value="Insert New Invoice Line (Description Only)" onclick="InsertRecord()"/></td>
				</tr>
				</table>
			</form>
		</td>		
		<td>
			Append existing Invoice with a new  ItemInventory/ItemService Line
			<form name="frmItemLine" id="frmItemLine" method="post">
			<table>
				<tr>
					<td>Enter Invoice No. (RefNumber)</td>
					<td><input type="text" name="txtItemInvoiceNo" id="txtItemInvoiceNo" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Enter Item FullName</td>
					<td><input type="text" name="txtItemRefDescription" id="txtItemRefDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td>Quantity</td>
					<td><input type="text" name="txtItemQuantity" id="txtItemQuantity" value="1"/></td>					
				</tr>
				<tr>
				<td>Rate</td>
				<td> <input type="text" name="txtItemRate" id="txtItemRate" value="1"/></td>
				</tr>
				<tr>
					<td>Enter Description</td>
					<td><input type="text" name="txtItemDescription" id="txtItemDescription" style="width:250px;"/></td>
				</tr>
				<tr>
					<td colspan="2"><input type="submit" name="btnItemLine" id="btnItemLine" value="Insert Invoice Line (Inventory/Service)" /></td>
				</tr>	
				</table>
			</form>
		</td>
	</tr>
</table>


    
</body>
</html>



<?php
if(isset($_POST['btnDescriptionLine']))
{

	$invoiceNo= $_POST['txtInvoiceNo'];
	$invoiceDescr = $_POST['txtDescription'];	
	if($invoiceNo == "" || $invoiceDescr == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Invoice No. and Invoice Line Description both.")';
		echo '</script>';
	}
	else
	{
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);
		//print("                        $sFieldName\n");
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		//print("\n");
		}				
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineDesc) values('$sFieldValue','$invoiceDescr')";
		$oResult = odbc_exec($oConnect, $sSQL);
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount  FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
		
		
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("                    <tr>\n");
			print("                        <td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


if(isset($_POST['btnItemLine']))
{
	$invoiceItemNo= $_POST['txtItemInvoiceNo'];
	$invoiceItemRef = $_POST['txtItemRefDescription'];
	$invoiceItemQuantity = $_POST['txtItemQuantity'];
	$invoiceItemRate = $_POST['txtItemRate'];
	$invoiceItemDescr = $_POST['txtItemDescription'];	
	if($invoiceItemNo == "" || $invoiceItemDescr == "" || $invoiceItemRef == "" || $invoiceItemQuantity =="" || $invoiceItemRate =="" )
	{
		echo '<script language="javascript">';
		echo 'alert("Fill the Details properly")';
		echo '</script>';
	}
	else
	{		
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks Data QRemote", "", "");
		$sSQL = "select txnid from InvoiceLine where RefNumber='$invoiceItemNo'";
		$oResult = odbc_exec($oConnect, $sSQL);
		//echo $oResult;
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		while ($lFldCnt < $lFieldCount) {
		$lFldCnt++;
        $sFieldName = odbc_field_name($oResult, $lFldCnt);		
		}		
		$lRecCnt = 0;
		while(odbc_fetch_row($oResult)) {
		$lRecCnt++;		
		//print("$lRecCnt");
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);		
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
			$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
			If ($sFieldValue == "") {
				print("\n");
			}
			else {
				//print("$sFieldValue\n");
			}
		}
		print("\n");
		}		
		
		$sSQL = "Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('$sFieldValue','$invoiceItemRef',$invoiceItemQuantity,$invoiceItemRate,'$invoiceItemDescr')";
		/*if($oResult = odbc_exec($oConnect, $sSQL)){
			echo '<script language="javascript">';
			echo 'alert("Success")';
			echo '</script>';
		}
		else
		{
			echo $oResult; exit();
		}*/		
		//print($oResult);
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$sSQL = "SELECT RefNumber,CustomerRefFullName,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineRate,InvoiceLineQuantity,InvoiceLineAmount FROM InvoiceLine Where txnid='$sFieldValue'";
		//$sSQL = "SELECT * FROM InvoiceLine Where txnid='$sFieldValue'";
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt= $lFldCnt+1;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("<tr>\n");
			print("<td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);
				//echo("Invoice No: " .  $invoiceNo. "<br />\n");
				//echo("Invoice Desc: " . $invoiceDescr. "<br />\n");	
			}
}


?>	

Problem Description:

In QuickBooks enterprise, the company information form shows me the company's business#.

In a custom report, the field is displayed as blank.

In VBDEMO, the field shows as blank.

I double-checked my odbc setup, which allows personal data to be accessed.

Solution:

The company information is on the Company table, i.e.

Select * from the company

The information you see is what is allowed by Intuit's qbXML SDK. Specific columns are only available in their respective country versions of QuickBooks, while "specialized to country" company information may not appear. '

Problem Description:

I am trying to query the Customer table. The Title Bar of the Access Application says Not responding. Still, it never opens for me in the datasheet view, either double-clicking the table or running a query - no error message is ever returned, but the screen maintains an hourglass.

I am getting the below error in QODBC logs:

2016-07-19 13:55:05 QODBC Ver: 16.00.00.313 *********************************************************************************************************************
service: False
SQL Statement: SELECT * FROM Customer
Error Getting XML in BuildAndLoadXMLForNextRecord

Solution:

Please enable the QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options.

After enabling the status panel to execute the query again, if you see "Waiting for QuickBooks," it means QuickBooks is taking the 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 at.

Also, You may face this issue If your company file contains many records in CustomerShipToAddress.

Sometimes, because of the extensive record sets being used, the Iterator or Chunk Size in the Advanced Tab in the QODBC Setup Screen should be setup up to accommodate the most extensive returned record set. The default is 500, and it can be set to any value up to 100,000.

To resolve this issue, Please download & install QODBC's latest version, V320 or above, by clicking here.

After downloading the latest QODBC version, please change the iterator chunk size to 2/10/50/100 on QODBC Setup Screen->Advanced tab.

After changing the chunk size, please query the customer table again. You should able to get records in the Customer table.

Problem Description:

We upgraded to QuickBooks Enterprise 15, and now we cannot get QReportBuilder to refresh data.

The error is

Error message: Could not create ResultSet from DataSource definition.

Could not connect to Database. Error reported was:

Database API-specific error

Your Database has returned the following error code and description to Alpha Five.

Consult your database documentation for further information.

160 - 'Specified driver could not be loaded due to system error 182: (QODBC Driver for QuickBooks, C:\Windows\system32\fqqb32.dll).

SQL State is: IM003'

Solution:

You might face this issue if ssleay32.dll & libeay32.dll (Open SSL files) are corrupted or old.

To resolve this issue, You need to rename libeay32.dll & ssleay32.dll files from the QReportBuilder installation folder (i.e., C:\Program Files (x86)\QReportBuilderV10).

To rename the file, right-click on the file name & click "Rename."

After renaming the file, download the updated DLL by clicking here.

After downloading the file, Extract the zip file by right-clicking on the OpenSSL-Dependency.zip file & click "Extract ALL...".

File Extracted.

After extracting the file, Please copy it from the extracted folder & paste it into the QReportBuilder installation folder (C:\Program Files (x86)\QReportBuilderV10\) & test the report again.

Problem Description:

Can I use Dropbox/Google Drive with QuickBooks & QODBC?

Solution:

No, You can't use Dropbox/Google Drive with QuickBooks & QODBC because Dropbox/Google Drive is NOT supported by QuickBooks and can cause data file corruption. No locking or sync takes place in QuickBooks data files unless you use the server version in QuickBooks Enterprise.

As part of the integration, application details are stored. Also, The integrated application info is saved.SDS, LGB files, and other user profile/registry places. SDS/.LGB file, which is next to Company File, it could be possible that the file is changed on another machine and causing this issue. Dropbox or such services will sync it and may cause loss of integrated application certificate or be marked as Not allowed. The user must always approve QODBC in QuickBooks's integrated application window.

Problem Description:

How do I run a Collections Report?

Solution:

A typical Collections Report in QuickBooks looks like this:

With QODBC, the same report can be generated using stored procedure reports like this:

sp_report CollectionsReport show Text, Blank, TxnType, Date, RefNumber, PONumber, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today,' AgingAsOf = 'ReportEndDate.'

Problem Description:

We have four machines using the ODBC driver with QuickBooks and Access 2003 that work perfectly fine. The odd machine is a slightly higher version of Access to Windows XP and gets hung up when processing a query. With QuickBooks help, I have tested the ODBC driver on the XP machine, and it runs a test query fine. It is only when Access 2003 enters into the mix that it does not work. All machines are pulling from the same database.

Any ideas would be great.

Solution:

This sounds like an "Out of Stack Space" error when running a Sub Procedure in Access 2003. When you run a Visual Basic Sub procedure that calls another procedure, you may receive the following error message:

Run-time error '28':
Out of stack space

CAUSE
This error message appears when one procedure in your module calls another procedure, and that procedure calls another procedure, and so on. The error message occurs in this case because when you call one sub-procedure from another procedure, the first procedure is not unloaded from memory until the system that it is called is finished. Because of this behavior, if you continue to call a procedure from another procedure, you may run out of stack space or memory allotted for the macro before the last procedure is called.

WORKAROUND
To work around this problem, call individual sub-procedures from one main procedure instead of calling one sub-procedure from another sub-procedure, and so on. For example, if you are calling sub-procedures, as in the following example

   Sub one()
      two
   End Sub

   Sub two()
      three
   End Sub

   Sub three()
      four
   End Sub
  
You can call these sub-procedures from the first procedure as in the following example:   

   Sub Main()
      one
      two
      three
      four
   End Sub 

Problem Description:

Can you give me some insights on optimizing an update query to the PriceLevelPerItem table?

I am trying to update all of my existing price levels, and I'm looping through each inventory item in a specific category and updating the item in the PriceLevelPerItem table. Here is my SQL update

UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 38.35 WHERE ListID = '8000000D-1177165668' AND PriceLevelPerItemItemRefListID = '800004B9-1175625788'

I believe the ListID and the ItemRefListID are both jumps in points, but my driver status counts through the records and then stops at the record to update and appears to hang in access and VBDemo. I have gotten it to update at times, but others go into limbo, and with 22,000 items to update, I am hoping you can help me make this work. 

Solution:

The Intuit qbXML SDK requires us to modify the whole PriceLevelPerItem table with every insert or update, so the more levels and items you have will take longer. The problem is that Intuit requires the entire list to be updated with every operation, not a single row like the rest of QuickBooks!

Problem Description:

I am running the same query on both returns, a big difference. The QODBC Test Tool app takes around 7 seconds, and my C# app takes 1:20 minutes.

Are there any settings I need to set to get the same results?

Solution:

Your C# app needs an STA (single-threaded-apartment) threading model. Multi-threading slows QODBC down as we need to re-establish a connection to QuickBooks every time the Thread ID changes.

Check your C# Express documentation or help files for thread management techniques.

See also the MSDN .NET Framework Developer Center:  http://msdn2.microsoft.com/en-us/library/system.stathreadattribute.aspx. 

Note: ADODB works when you loop through the resultant Recordset via Recordset.MoveNext(), an additional query is made through QODBC for each record. These other queries caused 50 minutes of runtime for a 7000 invoice query.

The first function below demonstrates the ADODB method that took ~1 hour. The second does the same thing with ODBC and takes less than a minute. 

[STAThread]
static void test1() {
    ADD. Connection con = New ADODB.Connection();
    con.Open("DSN=QuickBooks Data;OLE DB Services=-2", "", "", -1);
    string invoiceSQL =
                "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
                "FROM Invoice " +
                "WHERE TxnDate>{d'2006-04-02'}";
        ADODB.Recordset invoiceResult = new ADODB.Recordset();
        invoiceResult.Open(invoiceSQL, con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0);
        while(!invoiceResult.EOF) {
                Console.WriteLine("Invoice #" + invoiceResult.Fields["RefNumber"].Value.ToString());
                invoiceResult.MoveNext();
        }
        con.Close();
}


[STAThread]
static void test2() {
        OdbcConnection con = new OdbcConnection("DSN=QuickBooks Data");
        con.Open();
        OdbcDataAdapter dAdapter = new OdbcDataAdapter(
                "SELECT CustomerRefFullName, RefNumber, TxnDate, BalanceRemaining, AppliedAmount, Memo " +
                "FROM Invoice " +
                "WHERE TxnDate>{d'2006-04-02'}", con);
        DataTable result = new DataTable();
        adapter.Fill(result);
        DataTableReader reader = new DataTableReader(result);
        while(reader.Read()){
                Console.WriteLine("Invoice #: " + reader.GetString(1));
        }
        con.Close();
}

Problem Description:

I am trying to create Linked Server on SQL Server.

QODBC Test Tool connects successfully to QREMOTE on QuickBooks Server.

Setting up the Linked server - I get this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "SQLNCLI10" for the linked server "QREMOTELINK" reported an error. Authentication failed.

Cannot initialize the OLE DB provider "SQLNCLI10" data source object for linked server "QREMOTELINK."

OLE DB provider "SQLNCLI10" for linked server "QREMOTELINK" returned the message "Invalid authorization specification." (Microsoft SQL Server, Error: 7399)

So, I'm out of ideas.

Please tell me how to get rid of this issue. 

Solution:

The message "Invalid authorization specification" means that the linked server settings to connect to the server are incorrect.

Please verify the below listed linked server property & test again.

1. Please ensure that you have selected the correct provider, Microsoft OLE DB Provider, for ODBC Drivers, instead of any other providers.

2. In the security tab, please make sure that you have selected Be made without using a security context option.

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

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

Problem Description:

How to tell what version of QODBC I am using?

Solution:

To find out which version of QODBC you are using, please follow the below steps:

1. Click Start -> All Programs -> locate the QODBC Driver for use with QuickBooks.

2. Click on the QODBC Setup Screen & Navigate to the About tab.

You can find QODBC Version details from Text like Version 16.00.00.318, where 16 is the major QODBC version & 318 is the build number.

In the middle of the About Tab window, you will see a hyperlink that says Check Website for Updates.

You can click on this to check details about QODBC's latest Released Version & you can download QODBC's latest released version by clicking on the Click to Run QODBC Installer version hyperlink.

Also, You can check the README file of the latest QODBC version by clicking on the QODBC README file with list of the latest changes hyperlink.

Also, Refer:
How do I know which version of QuickBooks my QODBC license works with?

QODBC enables access to various important data sources in business intelligence tools such as Microstrategy. In this article, We will show how to connect to QuickBooks data, import data with the UI or query, and create a dashboard with several data visualizations.

The steps below show how to access data from the QODBC for QuickBooks in Microstrategy.

On the dashboard, click the plus icon to add Data by clicking "New Data..".

Click on Database.

Select Generic.

Select "Build Query" as the Import option & click "Next."

On the dashboard, click "Add.." to add a connection to QuickBooks.

In the dialog that is displayed, the following information is required:

• DSN: Select DSN associated with QODBC & QuickBooks from the list. The default DSN is QuickBooks Data.
• Version: Select the "Generic DBMS" option in the menu.
• User: Enter the username you want to create in the Microstrategy database connection. In this example, I have used QODBC.
• Password: Enter the password you want to create in the Microstrategy database connection. In this example, I have used QODBC.
• Data Source Name: Enter the Data Source Name you want to create in the Microstrategy database connection. In this example, I have used Test.

Note: QODBC or QuickBooks do not require the username and password, but it is mandatory in Microstrategy, and thus it is added here.