[QODBC-Desktop] QODBC and Microsoft Access
Posted by Juliet on 05 January 2010 07:09 AM
QODBC and Microsoft Access
For MS Access 64 Bit, Please refer : How to Configure QODBC to Work With 64-bit MS Access
Creating a Table in Microsoft Access:
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 2010, and make sure you are using 32-bit version of MS Access:
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 a default blank table, Please close this default table:
You could directly extract QuickBooks records to this table. But in this article, we are going to show you how to build a linked table to access QuickBooks.
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 see 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 table is also in full sync with QuickBooks.
In this article, we will show you how to build 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 related DSN you setup 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 example.)
The first time you use MS Access to access QuickBooks, it will ask you to grant permssion 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 want to extract. Mutiple Selection is available.
After the Linking table is sucessfull, you will see linked tables been created in your MS Access file under "Tables":
You can view the property of these linked tables by right clicking the linked table->select linked table manager:
If you want to view QuickBooks records in this linked table, just double click the linked server, then you will see the records:
FYI, 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 linked table, QODBC will automatically update the modification to QuickBooks.
Also Refer :