Problem Description:

I have QuickBooks POS 2015 (12.0) & I want to connect to QuickBooks POS using MS Access/Excel. I am unable to connect to QBPOS with Access and Excel 2016.

I am getting the below error:

Also, I am getting the error "Internal error 160000" in MS Access.

I can connect to QBPOS with QODBC POS Test Tool.

Is there an issue with the most recent version of MS Office?

Solution:

This is a known issue with QuickBooks POS SDK & Office 2016, so Office 2016 cannot connect to QuickBooks POS using QuickBooks POS Data DSN.

You can use QRemote DSN "QuickBooks POS Data QRemote" in MS Access/ Excel, which does not have this issue.

Please select QRemote DSN "QuickBooks POS Data QRemote" at the time of connection & you will be able to connect to QuickBooks POS 2015 (12.0) using Office 365 / 2016.

Also, Refer:

Using QuickBooks POS Data with Access 2016 / 365 / 2013

Using QuickBooks POS Data with Excel 2016 / 365 / 2013

Install QODBC POS

Please download the QODBC POS installer by clicking here

Right Click on the Installer and select "Run As Administrator."

Please click "Yes" in the UAC window to install QODBC POS.

Follow the Installation steps from the Installer by clicking the "Next" button till it finishes.

Installation completed.

Connect to QuickBooks POS via QODBC POS

First, Please open QuickBooks POS & load your company file:

Now, Please open QODBC POS Test Tool from Start Menu>> QODBC Driver for use with QuickBooks POS>> QODBC POS Test Tool :

Select DSN "QuickBooks POS Data" and click on "Connect."

QuickBooks POS will ask you to accept the Application certificate. Click the "Yes Always" button to allow QODBC POS Test Tool to access your QuickBooks POS company file.

QODBC POS Test Tool is connected to the QuickBooks POS company file.

Click on the "Run" button to obtain the customer data, or remove the default SQL Statement and enter your SQL statement.

Here is the customer data.

If you cannot connect, please change QuickBooks POS to run in multi-user mode. Click the "Switch Company File to Multi-User Mode" option in File Menu & try to connect using QODBC POS Test Tool.  

In QuickBooks POS references, you can see that QODBC POS has been included in the list of allowed applications:

You can open QuickBooks POS company preference from:
File >> Preferences >> Company

QODBC POS Test Tool is shown in the list of allowed applications:

In the same way, you should be able to use QODBC POS with your application(s).

Also, Refer:
QODBC POS could not access to QuickBooks POS Company File

Problem Description:

The QODBC Driver for QuickBooks POS runs differently under Microsoft Vista/W7 than other versions of Windows because of its new strict security settings.

If the Test connection is unsuccessful and shows Connecting to QuickBooks POS failed because of a Windows security issue, you have forced Administrator rights. The setup must be run without "Run As Administrator," and UAC must be turned on to Test the connection.

Solutions:

When you run the QODBC POS Setup screen from ODBC Manager, the Setup Screen runs as "elevated permissions," and QODBC POS runs as "elevated permissions."
QBPOSSDK & QuickBooks POS are running as "NON-elevated permissions" Thus, both cannot talk, and you will face this error.

Follow the below steps to resolve the issue:

Close QODBC POS Setup Screen, Close Data Source Administrator ( if Open).
Click Start -> All Programs -> QODBC Driver for use with QuickBooks POS -> QODBC POS Setup Screen.

Perform Test Connection
 

Data Source Administrator ( which by default runs as "Run as Administrator" ) If the above does not resolve the "Test Connection" Security issue, please follow the below steps.

Alternative

Use QODBC POS Test Tool or any other application like MS Access, Excel, or your custom application and connect to the DSN; it should get connected ( Even though  "Test Connection to QuickBooks" is not enabled or is having a Windows security issue. )

Refer: How to Install QODBC POS and Connect to QuickBooks POS

If QODBC POS Test Tool can connect, you should be able to use QODBC with your application(s).
You can find QODBC POS Test Tool at Windows Start->All Programs->QODBC Driver for use with QuickBooks POS>>QODBC POS Test Tool

If QODBC POS Test Tool can connect, you should be able to use QODBC with your application(s). There is no need to test the connection via QODBC POS Setup Screen once it has passed connect via QODBC POS Test Tool. You may ignore the Test Connection windows security message as Test Connection may fail if executed in a different context than a regular user.

Setup QODBC to use with MS Excel

Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:

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.

Office 2016:

Office 2013:

Office 2016:

Office 2013:

Create a blank workbook or can open an existing one. In this example, we are creating a new workbook:

An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "From Other Sources-> From Microsoft Query."

It will take some time to get the DSN list:

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to Use the Query Wizard to create/edit queries.).
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select the "Yes, always" option to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by Query Wizard. In Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And once a table is set, it is the default that all its columns are set. You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to set any query, please click the "Next" button:

This window allows you to sort your data. If you don't want to change the order, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, you can view and edit these records, but the data is static. The modification will not be uploaded into QuickBooks.

You can view the detailed connection properties by clicking the "connection" button in the Data menu:

In the Workbook Connection Window, click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query :

This method will lead you to the MS Query window, and you can view and edit data via SQL Statement there, and all modifications will be uploaded to QuickBooks.

Click the "SQL" button to view the SQL Statement of this query. The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, following selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button and from the list of values, select "Residential" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Open MS Access 2016/2013. You can find it via Windows Start->All Programs->Microsoft Office 2016/2013:

Office 2016:

Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

After  the new database is created, Navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button on the External Data menu:

Under the Get External Data window, you will get two options:

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its connectlinko to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as an example.)
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract data from. Multiple Selection is available.

If the Import/Linking works without any problems, you will see linked tables been created in the table list of MS Access:

If you want to view QuickBooks records in this linked table, double-click the linked server, then you will see the records:

The linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table, and QODBC will automatically update the modification to QuickBooks.

Note: When you run across an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Suppose you want to import a table in MS Access. In that case, you have to select Import the source data into a new table in the current database option instead of linking to the data source by creating a linked table & repeat the above steps: this option will directly extract tables into Access Database, you can view and edit these tables, but all modification will not be uploaded unless you build another query to do so.

Keywords: read/write with ms access, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

Instructions

64 and 32-bit words are kept separate in the Microsoft Windows Operating System. QuickBooks POS is a 32-bit application, so a 32-bit ODBC driver is required to communicate with it.

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. We call it QRemote. QRemote works as a bridge 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 POS live for the first time via the QODBC Driver.

QRemote has two components QRemote Client Driver for QODBC and QRemote Server for QODBC.

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

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

Setting Up a New QRemote Connection.

Start QRemote Server for QODBC POS from the start menu.

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

QRemote Server Main screen shows the IP address and Port allowing the QRemote Client to connect.

Note the IP address and Port number listed on the QRemote Server Event Logs window.

Create a new DSN entry

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

Create a new DSN entry/or configure the existing one

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

Configure QODBC POS Data Source 64-bit 

"QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC.

Navigate to System DSN. Click on Add Button

From the driver list, selects QRemote for QuickBooks POS and click Finish.

o Under the QRemote for QuickBooks POS Client DSN configuration screen, Please fill in the following information

o Data Source Name: Name of your DSN entry

o Server: IP address displayed on QRemote Server

o Port: Port displayed on QRemote Server

o Encryption Key: ( Optional, required only if you want to encrypt the data transfer, also needs to be configured at QRemoteServer)

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

In this example, we will be using existing (Default) DSN: QuickBooks POS Data 64-bit.  

Verify the QRemote Client connects to the server and that the below-listed logs are logged in the event window.

Connecting with 64-bit Applications

To allow 64-bit Application(s) to talk to QuickBooks POS via QODBC POS, Start the QRemote server and configure the QRemote Client DSN entry.

Connect using the standard DSN connection string. e.g., DSN=QuickBooks POS Data 64-bit

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.