How to integrate a Microsoft Access database with QuickBooks?

by Guest Poster Priyanka Chouhan

Microsoft Access is a Database Management System, a part of Microsoft office suite of products. This application allows Users to utilize QuickBooks data files just like Access databases.

Steps to create a table in Microsoft Access with QuickBooks:

1. Run MS Access 2013 (Windows Start button ->All Programs ->Microsoft Office 2013)
2. The next window opens a default database named as Database1 in the box. This default name can be changed according to your need you can select a more relevant name.
3. Now, select “Create” button to create a blank database
4. When you are ready with the new database, Navigate to “External Data“
5. Create a connection by selecting External Data -> More -> ODBC Database, or you can directly select ODBC database option located at External Data menu
6. In the next step, a new window named Get External Data appears along with two options
   1. Import the source data into a new table in the current database: This option directly extracts tables into Access Database, which can be viewed and edited according to the needs. However all of these changes made in these tables will not be uploaded unless you build another query to process the same.
   2. Link to the data source by creating a linked table: This option also extracts the table like first one, but will continue updating it to QuickBooks as well; therefore, all the operations you perform on these tables will be directly uploaded to QuickBooks. The data available in the table also syncs with QuickBooks.

Here are the steps to build a linked table in MS Access:

• After selecting the access method according to you, select OK button and you will be prompted to a Select Data Source window
• Here choose the QuickBooks related DSN you setup with the QODBC driver. This DSN could be one of your pre-installed DSN names or the one you have created. If you are using MS Access with QuickBooks for the first time, it will ask you to grant permissions for this application
• Choose ‘Yes, Always’ option and click Continue then ‘Done’ in the next window
• Once you have built the connection successfully, Access will list all the available tables in Select Tables window. You can select tables according to your need and extract data. Here you are allowed to select multiple tables.
• If the Import and Linking works properly without any issues, you will be able to view the linked tables 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 and you will prompted to the records
• Theses linked tables are always in the sync with QuickBooks Data, therefore you will not able to add or delete any columns from the table. However, you can directly modify the data available in linked table and QODBC will automatically update the modification you have done to the QuickBooks.

Note: If you are working with the application, which is not fully compatible with ODBC, but getting data from Access tables and want to make it compatible with QuickBooks tables, then run Microsoft Access setup like above mentioned steps.

Author Bio: Priyanka Chouhan is a technical writer in Stellar Data Recovery with 5 years of experience and has written several articles on MS Access databases. In her spare time she loves reading and gardening.

 4,162 total views,  1 views today