[QODBC-Desktop] How to use QODBC with Alteryx Designer

Problem Description:

I want to export the QuickBooks data using Alteryx Designer Studio.

How can I use Alteryx Designer with the QODBC?

Solution: 

Alteryx Designer Studio is a 64-Bit application. If you are using QuickBooks 2021 and eariler, you need to use QRemote 64-Bit and configure it to use ODBC Compatibility 3.8.

64 and 32-bit words are kept separate in the Microsoft Windows Operating System. QuickBooks 2021 and older versions are 32-bit applications, so a 32-bit ODBC driver is required to communicate with them.

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

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

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

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

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

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

QRemote has two components: “QRemote Client Driver for QODBC” and “ QRemote Server for QODBC”.

QRemote Client Driver: Allows 64-bit Applications to connect to the QRemote Server and sends the Request to the QRemote Server for Processing.

QRemote Server: Processes requested instruction from QRemote Client to QuickBooks using QODBC DSN Driver.

Setting Up a New QRemote Connection.

Start “QRemote Server for QODBC” from the start menu.

Double-click on the QRemote Server icon from the system tray.

QRemote Server Main screen will appear, that shows the IP address and Port allows the QRemote Client to connect.

Make a note of the IP address and port number listed on the QRemote Server "Event Logs " window.

Create a new DSN entry.

o Before creating A DSN entry, verify you have created a DSN entry for QODBC, which can connect to QuickBooks. 

Configure the default 64-bit DSN/or create a new 64-bit DSN

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks, and click on

"Configure QODBC Data Source 64-bit" 

Navigate to System DSN and select "QuickBooks Data 64-bit QRemote" DSN and click on Configure.

Note: "QuickBooks Data 64-bit QRemote" is the default DSN created when installing QODBC.

o Under the "QuickBooks Data 64-bit QRemote"- Client DSN configuration screen, please fill in the following information

· Data Source Name: Name of your DSN entry

· Server: IP address displayed on QRemote Server

· Port: Port displayed on QRemote Server

· Encryption Key: encryption key ( keep this empty )

For the Alteryx Designer application, you need to set compatibility 3.8 in the QRemote Client.

Select 3.8 from the list and click on "Ok".

Remote DSN: Select the QODBC DSN from the list. (Make sure QRemote Server is running)

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

Test connection is successful.

Open Alteryx Designer Studio and drag the input/output connection and drop it in the workflow screen.

Select the Input connection and click on "Connect a file or Database".

From the Data Source list, select "Generic ODBC connection".

From the Data Source name, select "QuickBooks Data 64-bit QRemote" DSN and click on "Ok".

Navigate to the Tables tab and select any table from the list, and click on the "Ok" button.

Click on the "Output Data" connection and select "Write to File or Database".

You can select any type of file connection from the screen.

Navigate to "Files" and select the "Microsoft Excel 1997-2003 .Xls."

In the dialog box, provide the file name and click on "Save".

Now, the input/output connection is ready.

Click on the "Run" button.

After running the workflow successfully, the customer data will be exported to the targeted MS Excel file.

Open your MS Excel file.

Tags: Dynamic IP Address, QRemote IP Address changes, Random QRemote IP Address