[QODBC-Online] Using QuickBooks Online Data with Access 2013 (Linked Tables) 32-bit
Posted by Rajendra Dewani (QODBC Support) on 10 August 2015 01:48 PM
How to Use QODBC Online with Microsoft Access 2013 to Build Linked Table
Creating a Table in Microsoft Access:
Note: Microsoft Access is a product with many uses, and will allow you to use your QuickBooks Online data files in the same fashion as Access databases.
Open MS Access 2013, you can find it via Windows Start->All Programs->Microsoft 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 ODBC database button at External Data menu:
Under Get External Data window, you will get two options:
Import the source data into a new table in the current database: 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.
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 operation to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.
In this article, we will show you how to build the linked table in MS Access.
Build a Linked Table in MS Access
After you select access method and click OK, then you will get a Select Data Source window:
Select the QuickBooks Online Data 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 the example.)
For 64-bit MS Office change QRemote DSN to QuickBooks Online Data 64-bit is the default DSN created when installing QODBC Online.
Once the connection is built, Access will return a list of available tables in Select Tables window. Please, select 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, just double-click the linked server, then you will see the records:
The linked table is always in sync with QuickBooks Online 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: In the case where you run an application which is not fully ODBC compatible, often times they do support getting data from Access tables. To make this application work with QuickBooks Online 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, which you created, which was linked to QuickBooks Online tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.