Knowledgebase: Data & Table
[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 has the ability to talk to a copy of QuickBooks POS running anywhere in the world over the Internet, or over a local area network. This feature is built-in to 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 own encryption key to encrypt the data which is preferable for transfer over the open Internet.

To get this feature to work, simply install QODBC POS on the machine which runs QuickBooks POS (the SERVER), and the machine 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 best speed is attained if it is on the SERVER machine. QuickBooks POS on the SERVER Machine should be able to open QuickBooks POS company file. Once this is done, a few simple settings in the driver setup screen are all that is required to use this data remotely.

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

The tool we use to do 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 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 to.

 

Make a note of 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 Encryption Key empty/blank. In case you want to use encrypted data transfer between QRemote client and QRemote Server, 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 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 QRemote Server at SERVER Machine:

 

Connection to Remote QuickBooks POS/QODBC POS via a 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 VBDemo32

Step 1 - Start QuickBooks POS to register QODBC POS

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

QuickBooks POS is an accounting program which 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 VB Demo

Leaving your QuickBooks company file open, click on "Start" and "Programs" and locate QODBC Driver for QuickBooks program group.

 

Click on the VB Demo icon to launch the Visual Basic Demo program supplied with QODBC POS.

 

From the Menu, Click "Connection", Select "Add Connection"

 

From the Data Source list, select "QuickBooks POS Data QRemote" or the DSN you had configured that points to SERVER QRemote Server's IP Address.

 

"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 is running 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 "Tables","Query" & "Disconnect" buttons gets Highlighted.

 

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 type in another table like stock by entering "SELECT * FROM ITEMINVENTORY". Click on the "Query" button and the requested query results will appear! In case you're wondering, the * in the select statement means all columns in that Table. Click on the "Tables" button to see a list of all the available QuickBooks POS tables.

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 to your own 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).