Knowledgebase: Setup
[QODBC-POS] Using QuickBooks POS data remotely via QODBC POS
Posted by Jack - QODBC Support on 10 November 2016 10:59 AM

QRemote Tutorial: Using QuickBooks POS data remotely via QODBC POS

QODBC POS can talk to a copy of QuickBooks POS running anywhere in the world over the Internet or a local area network. This feature is built-in into the licensed version and requires no additional charge.

This feature can transfer data in an unencrypted fashion (plain text), or you can set up your encryption key to encrypt the data, which is preferable for transfer over the open Internet.

To get this feature to work, install QODBC POS on the machine which runs QuickBooks POS (the SERVER) and the device you want to use the data from (the remote CLIENT). It does not matter if the QuickBooks POS data file is located on the SERVER machine or another location, but the best speed is attained if it is on the SERVER machine. QuickBooks POS on the SERVER Machine should be able to open the QuickBooks POS company file. Once this is done, a few simple settings in the driver setup screen are required to use this data remotely.

Before testing remote capabilities, be sure you have tested basic QODBC POS functionality to ensure that QODBC POS can connect to QuickBooks POS and obtain data with this article.

The tool we use to make this remote connection is called QRemote. QRemote works as a bridge between remote applications and the QODBC POS Driver by communicating data calls between the two worlds using TCP/IP (Internet) protocol. 

QRemote has two components, "QRemote Client Driver for QODBC POS" and "QRemote Server for QODBC POS."

QRemote Client Driver (CLIENT): Allows applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.

QRemote Server (SERVER): Processes requested instructions from QRemote Client to QuickBooks POS using QODBC Driver talking to QuickBooks POS application SDK. 

Configuring SERVER Machine: 

Start "QRemote Server for QODBC" from the Windows start menu.

 

Double-click the QRemote Server icon "Q" from the system tray.

 

QRemote Server Main screen shows the IP address and port setup for the QRemote CLIENT to connect.

 

Note the IP address and port number listed on the QRemote server.

 

Configuring CLIENT Machine: 

Select "Configure QODBC POS Data Source" from Windows Start Menu. (If your application is 64-bit, select "Configure QODBC POS Data Source 64-bit)."

 

Switch to "System DSN," Select the DSN "QuickBooks Data QRemote" Or "QRemote Data 32-bit". (For 64-bit DSN, "QuickBooks Data 64-bit QRemote" or "QRemote Data 64-bit")

 

 

Click Configure. This will bring QRemote Client DSN Configuration. Fill in the following information.

  • Data Source Name: Name of your DSN entry - Do Not Change

  • Server: Change the IP Address to the IP address displayed on QRemote Server at SERVER Machine, e.g., 192.168.1.19

  • Port: Port displayed on QRemote Server at SERVER Machine

  • Encryption Key: Keep default key "QODBC#QRemote$1"(In case you want to use encrypted data transfer between QRemote client and QRemoteServer, You can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN)  

  • Remote DSN: select the QODBC DSN from the list. ( Make sure QRemote Server at SERVER Machine is running). This is the QODBC DSN on the SERVER Machine

 

 

"QuickBooks POS Data QRemote." (For 64-bit DSN, "QuickBooks POS Data 64-bit")

 

Verify the QRemote Client connects to the server and the below-listed logs are logged in the event window of the QRemote Server at SERVER Machine:

 

Connection to Remote QuickBooks POS/QODBC POS via Applications (32-bit)

You may use any 32-bit Application like MS Access/Excel to connect to the Remote QODBC. In this example, we are using QODBC POS Test Tool.

Step 1 - Start QuickBooks POS to register QODBC POS

Please keep QuickBooks POS running on the SERVER Machine, Logged in as "Admin."

QuickBooks POS is an accounting program that must produce trusted numbers that accountants can rely on. For this reason, they have created an interface to the QuickBooks application which follows all of the business rules. This is what our product uses to access QuickBooks POS data.

To get access to that data, your application (using QODBC/QRemote) must register with QuickBooks so that security access can be granted. To do this, you first need to run QuickBooks POS on your computer, logged in as an Administrator, and open your company file.

 

Step 2 - Running QODBC POS Test Tool

Leaving your QuickBooks company file open, click on "Start" and "Programs," locate the "QODBC Driver for use with QuickBooks POS" program group, and click on "QODBC POS Test Tool" to launch.

 

Select "QuickBooks POS Data QRemote" from the DSN list or the DSN you had configured that points to SERVER QRemote Server's IP Address.

Click on "Connect"

 

"QuickBooks POS Data QRemote." (For 64-bit DSN, "QuickBooks POS Data 64-bit")

 

Step 3 - QuickBooks POS Integrated Applications

 

At SERVER Machine, QuickBooks will request for Accepting Certificate for Data Access.

When the driver starts, QuickBooks POS will ask permission for the driver to access your QuickBooks POS data. Answer "Yes, Always" to this, and the QODBC driver is added to the Integrated Application list.

This will allow applications to integrate with QuickBooks data when QuickBooks POS runs on the desktop. There may be instances in which you want an integrated application designed to share data with QuickBooks POS to access QuickBooks POS files automatically without user intervention. This might permit, for example, a particular application to run during the night when there is no other activity on the company file. Also, QuickBooks POS need not be running on the desktop for ODBC-compliant applications to access the data files while setting in this mode.

 

This will allow applications to integrate with QuickBooks POS data when QuickBooks POS is running on the desktop.

Step 4 - Testing Your Connection

CLIENT Machine:

After the successful connection, you may notice that the "Connect" button is altered to "Disconnect."

 

Now that you have successfully established a connection to your QuickBooks data, it's time to type in your desired SQL query; for example, you can use the default "SELECT * FROM CUSTOMER" or order in another table like stock by entering "SELECT * FROM ITEMINVENTORY."

Click the "Run" button, and the requested query results will appear! In case you're wondering, the * in the select statement means all columns in that Table.

Execute the "Sp_Tables" command to see a list of all the available QuickBooks POS tables.

 

Here you can see we have extracted the customer records. You can connect to your application using the same DSN name and export the records.

 

To disconnect, you can either select "connections" / "remove the connection" or Click the "disconnect" button. If you get an error, please search our FAQ Knowledgebase or post a support ticket to resolve the issue Click Here. If not, congratulations - you have created your first report from QuickBooks POS data and are ready to talk about your company data.

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).