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

How to Use QODBC with Microsoft Access 2016 / 365 / 2013 to Import QuickBooks Data using Import tables

Note: Microsoft Access is a product with many uses, and will allow you to use your QuickBooks data files in the same fashion as 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:

 

In the newly created database, you will find that a default table:

 

 

Close this table when there is no need to use it:  

 

Please Navigate to "External Data"

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

OR

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

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 import data from QuickBooks into MS Access.

Import Data from QuickBooks

After you select 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 the example.)

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

Select "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 Select Tables window. Please, select tables you would like to extract. Multiple Selection is available.

It may take some time for MS Access to extract those records:

If the Import works without any problems, you will get a window reporting successful:

You can see those imported tables under the "Tables" list:

Double Click a table to view Record under the table:

FYI, these import records are stored as MS Access tables, so you can change the schema rules of these tables. Please remember, these tables do not sync back to QuickBooks, thus, all the modification you do to these tables will NOT be updated into 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 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 tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

 

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

 

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


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
2003 and QODBC

(0 vote(s))
Helpful
Not helpful

Comments (4)
leo
18 December 2012 05:17 PM
these steps working great, except if the character is non english, it will display problem in the access. is there any solution for that?
Barry J. Hill
21 May 2013 02:44 PM
Is there a way to refresh the tables so you don't have to import the tables every time you want current information?
Mick
10 February 2015 04:44 PM
Hi Rajendra
I need to record data on a nightly basis using access 2013 and then be able to export it to quickbooks. Is this possible or would you suggest a different program to do this.
Thank you for any help!
Jack
23 February 2015 01:21 PM
Hi Mike,

I would like to inform you that you can do this by writing code as per your requirement. I kindly request you to please refer below mentioned article which contains sample VBA, ASP & vbs code:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2571/0/using-quickbooks-data-with-vba

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2204/76/sample-code-for-asp-c-qodbc-connection
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2356/57/qodbc-and-microsoft-visual-basic-and-ado

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2510


please refer below mentioned link to the sample VB.Net project:
http://support.flexquarters.com/supportfiles/Sample VB DOT Net.zip
or
http://support.flexquarters.com/supportfiles/SampleVBDOTNet.zip

You can refer above code & make changes according to your requirement.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).