Knowledgebase: Microsoft Products
[QODBC-Desktop] Using QuickBooks Data with Microsoft Access 2016 / 365 / 2013 32-bit
Posted by Rajendra Dewani (QODBC Support) on 12 November 2012 02:22 PM

How to use QODBC with Microsoft Access 2016 / 365 / 2013 / 2019 32-bit

How to use QODBC with Microsoft Access 2016 / 365 / 2013 / 2019 64-Bit (Minimum requirement: QuickBooks 2022 64-Bit and QODBC 2023)

 

QuickBooks 2022 and above are 64-Bit applications and include both a 32-Bit and a 64-Bit QuickBooks SDK interface.

QODBC 2023 now includes a 64-Bit driver and the 32-bit driver, so you can use 64-bit or 32-bit applications to connect to QuickBooks Data directly. QRemote is no longer required to connect 64-bit applications with our 32-bit driver and is now used mainly for remote connection situations.

 

Note: If you are using MS Office 64-Bit and QuickBooks 2021 or older, please refer to Configure QODBC to Work With 64-bit MS Access.

 

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 QB 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 at the External Data menu:

OR

Build a connection via External Data -> New Data Source -> From other sources -> ODBC Database :

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 link 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.)

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. QODBC will automatically update the modification to QuickBooks.

Note:  When you run 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.

Please refer: To how to use MS Access as Middleware with QODBC for using MS Access as Middleware.

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.

Also, Refer :
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2010 (Import Tables) 32-bit
Using QuickBooks Data with Access 2010(Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access
Microsoft Access 2003 and QODBC

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

(9 vote(s))
Helpful
Not helpful

Comments (2)
Melinda
02 June 2015 07:57 PM
My installation of FLEXquarters QODBC does not have the option "Yes, always allow access even if QuickBooks is not running." I only have "Yes, This Time." This is creating a problem in my linked tables where I have to manually refresh them every day. No VBA code I write is able to overcome this. Any ideas??
Jack
04 June 2015 02:13 PM
Hi Melinda,

I would like to inform you that certificate acceptance option depends on QuickBooks not depends on QODBC version. I would suggest you to disable "Allow QODBC to auto login" option from advanced tab of QODBC Setup Screen & test again.


Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" Tab>> select QuickBooks Data>> click "configure">> Advanced Tab uncheck "Allow QODBC to auto login"

If you are still facing the issue, you need to get in touch with Intuit.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).