Problem Description 1:

How to connect to QuickBooks Data with Power BI using QODBC Desktop?

Solutions: 

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

To change, please follow the steps below:

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

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

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

Start Microsoft PowerBI Desktop (32-bit).

Navigate to the "Get Data" option, and from the drop-down list, select "More."

A pop-up with the title "Get Data" should appear.

Select "Other" --> "ODBC" and click the "Connect" button.

Select the "QuickBooks Data" from the Data source name (DSN) list and click the "OK" button.

Note: If you are using QODBC version 19.0.0.333 or lower, use the "Advanced options">> Write the SQL statement for the table you want to fetch data from.

For example, "select * from customer" and click the "OK" button.

It would be best if you got the preview of the table. Click the "Load" button to continue.

Select the fields you want to display on the report. This will add the fields to the information and display the data.

If you are facing "Driver does not support this parameter," please switch QODBC, QRemote 32-Bit DSN, and QRemote 64-Bit DSN to ODBC Compatibility 3.8.

To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

If you are using Power BI 64-bit, you need to change to QODBC and QRemote 64-bit clients. To change compatibility in QODBC, follow the path below:
Start >> All Programs >> QODBC Driver for QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select DSN "QuickBooks Data" >> click "Configure”>> Switch to "Advanced" tab>> Navigate to "QODBC Compatibility">> change to "3.8"

For QODBC POS, select DSN "QuickBooks POS Data."

For QODBC Online, select DSN "QuickBooks Online Data."

To change compatibility in QRemote 32-bit, follow the path below:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >>Go to "System DSN" Tab>> select "QuickBooks Data QRemote" DSN >> click "Configure">> Switch to Advanced tab and change 'ODBC Compatibility' to '3.8' and click the Apply/OK button.

For QODBC POS, select DSN "QuickBooks POS Data QRemote."

For QODBC Online, select DSN "QuickBooks Online Data QRemote."

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

For QODBC POS, select DSN "QuickBooks POS Data 64-Bit QRemote."

For QODBC Online, select DSN "QuickBooks Online 64-Bit Data QRemote."

Try connecting again. It should fix the issue you are facing.
If you are using the older version of Power BI, please download the latest and try connecting with QODBC.

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 the 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 that 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 is working fine.

Solution:

This issue is related to the 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 the correct records.

For Example:

Select * from VehicleMileage UNOPTIMIZED

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

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 options for: "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 sources. Please select the "ODBC" data source.

In the Create new connection (ODBC) window, select "System DSN" & enable the "32-bit" option, 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 the 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 the 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, too.

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.

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

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

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

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

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

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

QODBC Bundled Edition for QuickBooks Enterprise Edition will not work with remote data sharing using QRemote; 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 Bundled Edition for QuickBooks Enterprise 2021 and earlier 32-bit editions will not work with 64-bit applications; 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 Bundled Edition for QuickBooks Enterprise 2022 and eariler 64-bit editions will work with 64-bit applications using the QODBC Driver (DSN = QuickBooks data), not QRemote. In these cases,  QRemote is not required, as the native 64-bit driver enables direct communication between the application and QuickBooks without the need for any intermediary layer.

QODBC Bundled 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 a 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 built 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 the Start Menu & click "Control Panel" from the result.

The Control Panel is opened; click "Programs 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 the 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 or 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 the 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 the 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 the MS Office 2016/365 setup folder.

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

Problem Description:

How to use M-Files with QODBC?

Solution:

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

1. Open M-Files Admin.

2. Expand the desired connection to the 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 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 the option "Use Query Wizard to Create/edit Queries" since we do not need to import any tables here.

Note: You need to select the QuickBooks Online Data DSN.

When using QuickBooks Online, the report loses 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 the 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 an 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 following the steps below:

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-clicking 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 using QODBC for exporting QuickBooks reports in MS Excel. I am using the BalanceSheetStandard report for specific date ranges as follows.

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 steps below 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 format below for providing the date value.

YYYY-MM-DD

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

The report data gets refreshed with the parameter date value.

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 application's. 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 to the link below 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 enable 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 the "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 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 the data.

You need to query the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine tables 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.

We have created seven full sheets (i.e., Sheet0 to Sheet6). Sheet 0 will be the input form, & Sheet1 to 5 will contain data from the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine tables for particular Customers, and Sheet 6 will combine data from Sheets 1 to 5. 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 the 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 Sheets 1 to 5.

Please click here to download the 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 a 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 details.

I am assuming that you have referred to the 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 tables.

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 steps below.

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 sources. Please select the "ODBC" data source.

In the Create new connection (ODBC) window, select "System DSN" & enable the "32-bit" option, 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 and 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 the 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 be able to run the PDO script.

Please use the 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 steps below 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 an 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 that 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 steps below 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 "Settings" 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" pop-up message on a successful database connection; click "OK" to close the pop-up 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 the Company File as Admin. Start QuickBooks login to the 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 the 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:

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 the 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 in the certificate and accept the process you want to certify. 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 application certificate you want to use with QODBC.

Problem Description:

Our company uses QuickBooks 2018 contractor edition, which allows only one user to use it 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 the 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" who is currently logged in to the 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" that you have selected in the Integrated Application Preference at the time of certificate acceptance in QuickBooks.

So when the same user has already logged in to the 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 the 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 following 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 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 configure an 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 following 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-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, the 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.

Problem Description:

I'm trying to install the QODBC on our SQL Server, and I followed all the 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 a 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:

How to Auto-Add a 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 provides a user-friendly graphic interface to view the relationships between two or more tables.

To Auto Add a 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 details.

I am assuming that you have referred to the 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 into the 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 are 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 those of 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:

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 when closing QuickBooks.

Problem Description:

I am using QODBC to access QuickBooks Data. I am using the QODBC optimizer, and I want to know more about how the optimizer works.

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

I can 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 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 the Company File as Admin. Start QuickBooks login to the Company File as Admin. Start QuickBooks login to the 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 the 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 using a 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 following error when I use a 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.

A credit memo was created in QuickBooks.

Problem Description:

I would like to know if QODBC will convert QuickBooks Pro Desktop invoices into an 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 it 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 the 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 using VBA code. We will add a button asking for the RefNumber of the Invoice. By entering the RefNumber, the VBA code will fetch the Invoice detail for a particular RefNumber & fill the details in the 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 code below to 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 sample below. Enter a RefNumber value and click "OK."

The report is available for a 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 the Sample Report.

Problem Description:

I am using QuickBooks Enterprise & I want to use the QODBC read-only edition for QuickBooks Enterprise.

How can I install the 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 the 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). The 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 to download the Sample Code.

Please refer to the steps below for using an application for inserting InvoiceLine to an existing Invoice using VB.NET.

You can change DSN as per your configuration. In this example, I am connecting to the default DSN QuickBooks Data. Run the application, select DSN & click on the "Connect" button.

The application is connected with QuickBooks.

The application has two functionalities:

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.

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

A 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

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 the System Tray & click on File >> Settings.

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 the System Tray & click on File > Settings.

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

This article shows how you can easily integrate the QODBC driver for QuickBooks into the 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 to: How to configure QRemote

5. Enter the 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.

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 a Linked Server on SQL Server.

QODBC Test Tool connects successfully to QREMOTE on the 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

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

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.

Data source added.

Double-click on the Data source name to see the list of available tables.

Click "Edit SQL" to write an SQL query.

Enter the query in the Editor.
In this example, I am fetching the Customer Name & Balance from the Customer table.
SELECT Name, Balance FROM Customer

Select the Data Access Mode that you want to use.
In this example, I have selected Import as an In-memory Dataset.

On the resulting page, click Select Visualization -> Bar Chart -> Clustered Bar Chart.

Refer: Oracle JDBC-ODBC Bridge

How to Use QODBC with NetBeans

QODBC enables access to various important data sources in business intelligence tools such as NetBeans. This article will show how to connect to QuickBooks data and import data with a query.

The steps below show how to access data from the QODBC for QuickBooks in NetBeans.

Open NetBeans IDE.

Create a new project using the menu File >> New Project.

Select Java Application & click "Next."

Enter Project Name & click "Finish."

A new Project will be created by clicking "Finish." Click the "Services" button to open the Service window.

You should see the Service window open.

In the Service window, click the Database node >> Drivers >> right-click on "JDBC-ODBC Bridge" and click "Connect Using.."

In the New Connection Wizard, enter the following connection properties & click "Test Connection":

• Driver Name: JDBC-ODBC Bridge.
• JDBC URL: JDBC:odbc: QuickBooks Data

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

Test Connection success. Click on "Finish" for the finished configuration.

Connection added. It shows the list of available tables.

Right-click on "Tables" & click "Execute Command..." to write an SQL query.

Enter the query in the Editor & click on the "Run SQL" icon.
In this example, I am fetching the Customer table records.
Select * from Customer.

Query results are available with a list of the Customers.

Note: The JDBC-ODBC bridge driver was removed in Java 8, preventing ODBC drivers from being usable in newer versions of Java or other Java-based applications.

Refer: Oracle JDBC-ODBC Bridge

How to Use QODBC with NetBeans

This article shows how you can easily integrate the QODBC driver for QuickBooks into your workflow in Talend.

Follow the steps below to connect to the DSN and create real-time data visualizations:
1. Open Talend ETL & create a new project by clicking on "Create..".

2. Enter Project Name & click "Finish."

3. To open the project, click "Open."

4. Add a new database connection to QuickBooks data: To add a new connection, expand the Metadata node, right-click the Db Connections node, and click Create connection.

5. In the resulting wizard, enter a name for the connection & click "Next."

6. On the next page, select Generic ODBC from the DB Type menu, enter the DataSource name "QuickBooks Data," & click "Check" to test the database 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 "Database connection successful" pop-up message on a successful database connection. Click "OK" to close the pop-up message & "Finish" to finish the configuration.

The database connection is added in the Talend ETL.

Now that your repository database connection has been established, right-click your Database Connection and select Retrieve Schema from the pop-up menu. This will display the Schema dialog. Step 1 of this dialog allows you to set your Filter Conditions and selection types.

This step will only specify the filtering rules for the objects to import. Once these options have been completed, hit Next to select the things you want to import.

Stage 2 allows you to choose objects. Select the objects you want to import using the checkboxes to the left of each node. Name Filter enables you to hide things from view. When completed, hit Next. Note that you may need to allow Talend time to retrieve information from the database before this button becomes available.

Stage 3 allows you to make some final amendments before you import. This is more useful when you are reviewing previously imported objects. For this tutorial, we'll leave these options with their default values—hit Finish to import the schema.

If the schema is not loading automatically, you can add the schema manually by clicking the "+" option & add the required columns.

You can refer to the QODBC Data Layouts site for details about different report parameters.

Navigate to the above link & click on the region of the QuickBooks edition you are using.

The table is added to the Talend ETL.

You can now create a simple Job that will read data from your database.

Create a new Job and drag one of the Table objects from the Repository to the Job Workspace. When you drop the object onto the Workspace, you can choose the Talend component that should be used. Choose the default, tDBInput (or the database input component for your database vendor).

 The customer table from the ODBC catalog will be used in the example shown here. 

Note. In this example, you're going to write the output of this table to the Talend Console, so you may want to choose a table with a small number of rows. Add a tLogRow component to your Job and connect the two components using Row->Main. You should now be able to run your Job and see data displayed in the Talend Console. Your completed Job and results should look similar to the screenshot below.

This article walks you through using the QODBC Driver for QuickBooks in TIBCO Spotfire. You will use the data import wizard to connect to a DSN (data source name) for QuickBooks and build on the sample visualizations to create a simple dashboard.

Follow the steps below to connect to the DSN and create real-time data visualizations:
1. Click File -> Add Data Tables.

2. Click Add -> Database.

3. In the Data Source Type menu, select ODBC Data Provider and click Configure.

4. Select the DSN for the QuickBooks Data.

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

Click "OK" to open the database.

Select the tables that you want to add to the dashboard. This example uses Customers. You can also specify an SQL query. The driver supports the standard SQL syntax. Enter the query in the Editor. I am fetching Customer Name & Balance from the Customer table in this example.
Select Name, Balance from Customer

Click "OK" to add the data table.

The table is added to Tibco Spotfire.

QuickBooks Data is available in Tibco Spotfire. You can visualize it as per your requirement. In this example, I have selected Pie Chart & Bar Chart.

Problem Description:

I am trying to link the ReceivePaymentToDeposit table through the linked table in MS Access. I can connect the ReceivePaymentToDeposit table. But I am getting an ODBC-Call failed error in MS Access when opening the ReceivePaymentToDeposit linked table.

Solution:

When you open the linked table from MS Access at that time, MS Access is passing queries like "SELECT "TxnID,"..., "UnusedCredits" FROM "ReceivePayment" where "TxnID" = ?" to QODBC.

QODBC passes one-by-one XML requests to QuickBooks SDK using the TxnID of the table, and QODBC gets all records one by one from QuickBooks SDK.

The "ReceivePaymentToDeposit" table does not support the TxnID filter in QuickBooks SDK & this issue happened.

Using the Pass-Through query in MS Access would be best as a workaround.

You can find pass through from: MS Access >> Create tab >> Query Design >>



Select Pass-Through >> Write query >> Execute query using Run button



Select DSN, click "OK," & execute the query.



You will get the result.

Problem Description 1:

I want to be able to access QuickBooks to extract inventory data in database format. I believe this is possible with QODBC. My issue is that I do not want this person to have access to everything in QuickBooks (customer credit card information, payroll, financial information, etc.). Can I limit their access to the data with a user profile? 

Solution 1:

You can set up access right in QuickBooks by creating users & provide him/their rights.

For Example:

Create user "Jack" and assign Inventory rights only.



When you connect to QuickBooks using QODBC for the first time, you need to log in as Admin in the QuickBooks company file & the certificate for a particular QuickBooks User (In this example, "Jack").





Integrated Application shows Access rights for the user.



If the user is restricted from accessing a particular module from QuickBooks UI, then they cannot access the same from QODBC.

Now log out of QuickBooks as Admin & log in as a user (In this example, "Jack").



Now we will connect using QODBC & try to access the ItemInventory table.



The user can access the ItemInventory table because the user has the right to access Inventory.

Now, we try to access the Invoice table.



No data returns. The user cannot access the Invoice table because the user has no rights to access the Invoice.

Now log out of QuickBooks as Jack & log in as Admin & try to access the Invoice table.



Admin has Full access. The Invoice table will return data.

QODBC will use QuickBooks logged-in user access rights. Integrated Application user rights will be overridden if the logged-in user is different.

When QuickBooks is not running, QODBC will use auto-login user rights.

For Example:

You have accepted the certificate for User A & User A has Inventory access only. User A can access only the Inventory data when User A is logged into QuickBooks.

User A can access other data if Admin or another user (who has different access rights) is logged into QuickBooks.

Problem Description 2:

My colleague and I would have read/write access, and other employees' access would be more "read-only." So, is it possible through QODBC? 

Solution 2:

You can set up access rights in QuickBooks by creating users & provide them "View Only" rights.



Note: When you set up "View Only" rights for the user, at that time, the user is limited to View Only in QuickBooks UI and through QODBC.

You can disable write permission from QODBC by enabling "Read-Only Access" options from:

Start>>All Programs>> QODBC Driver for use with QuickBooks>>QODBC Setup Screen>>Advanced Tab>> Enable "Read-Only Access"



Note: When you disable write permission from QODBC. It will be affected by all users accessing QuickBooks data through QODBC. So it is impossible to disable the written license for the particular user.

Open Visual FoxPro from the Start Menu:

FoxPro started:

Open "Command Window" from Windows Menu:

"Command Window" Opened. You can execute FoxPro commands from the "Command Window."

You need to write FoxPro commands in the "Command Window" & execute them by hitting the "Enter" key on the keyboard.

First, we will connect to QuickBooks from FoxPro using the SQLCONNECT command. Write the command below & hit the "Enter" key on the keyboard.

CN = SQLCONNECT('QuickBooks Data', '', '')

Now, we will execute the query to QuickBooks from FoxPro using the SQLEXEC command. Write the command below & hit the "Enter" key on the keyboard.

SQLEXEC(CN , "SELECT TOP 10 Name FROM Customer","csrCust")

Note: The above query is for testing purposes only. You can change the query as per your requirement.

Now, we display query results in FoxPro using the BROWSE command. Write the command below & hit the "Enter" key on the keyboard.

BROWSE

Query Result:

You can disconnect the connection to QuickBooks from FoxPro using the SQLDISCONNECT command. Write the command below & hit the "Enter" key on the keyboard.

SQLDISCONNECT(CN)

Download: QODBC 2024 Quick Start Guide (PDF)