Using QuickBooks data remotely via QODBC
QODBC has the ability to talk to a copy of QuickBooks running anywhere in the world over the Internet, or over a local area network. This feature is built-in to fully licensed versions of QODBC and requires no additional charge.
This feature can transfer data in an un-encrypted fashion (plain text), or you can setup 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 on the machine which runs QuickBooks (the SERVER), and the machine you want to use the data from (the remote CLIENT). It does not matter if the QuickBooks data file is located on the SERVER machine or another location, but best speed is attained if it is on the SERVER machine. QuickBooks on the SERVER Machine should be able to open QuickBooks company file. Once this is done, a few simply 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 functionality to make sure that QODBC can connect to QuickBooks 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 Driver by communicating data calls between the two worlds using TCP/IP (Internet) protocol.
QRemote has two components “QRemote Client Driver for QODBC” and “QRemote Server for QODBC”
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 using QODBC Driver talking to QuickBooks 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 “Event Logs window”
Configuring CLIENT Machine:
Select “Configure QODBC Data Source” from Windows Start Menu. (If your Application is 64-Bit, select “Configure QODBC 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.62.135
-
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
Verify the QRemote Client connects to server and the below listed logs are logged in the event window of QRemote Server at SERVER Machine:
Connection to Remote QuickBooks/QODBC 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 to register QODBC
Please keep QuickBooks running at SERVER Machine, Logged in as “Admin” under Single User Mode
QuickBooks 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 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 on your computer logged in as an Administrator and open your company file (or one of the sample company files that came with QuickBooks)
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.
From the Menu, Click “Connection”, Select “Add Connection”
From the Data Source list, select “QuickBooks Data QRemote” or the DSN you had configured that points to SERVER QRemote Server’s IP Address.
Step 3 – QuickBooks Integrated Applications
At SERVER Machine, QuickBooks will request for Accepting Certificate for Data Acesss.
When the driver starts, QuickBooks will ask permission for the driver to access your QuickBooks 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 is running on the desktop. There may be instances in which you want an integrated application designed to share data with QuickBooks to access QuickBooks 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 need not be running on the desktop for ODBC compliant applications to access the data files while set in this mode.
This will allow applications to integrate with QuickBooks data when QuickBooks is running on the desktop.
Step 4 – Testing Your Connection
CLIENT Machine:
After the Successfull 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 your 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 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 data and are ready to talk to your own company data.