[QODBC-Desktop] How to Use QODBC with Microsoft Access 2007
Posted by Rajendra Dewani (QODBC Support) on 23 August 2012 10:05 AM
How to Use QODBC with Microsoft Access 2007
Creating a Table in Microsoft Access
Open MS Access 2007, create a blank database.
Click on "Create" button. Then you get a Microsoft Office Access blank database.
The newly created database shall have one table by default, you could extract QuickBooks data to this table or create another table to do this.
Build a connection to QuickBooks via External Data->More->ODBC Database
In the "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 the table is also in full sync with QuickBooks.
Here in this an example, we have selected Link to the data source by creating a linked table.
Click OK button, then you will see Select Data Source window
Please Select "QuickBooks Data" or the one you may have created for your QuickBooks File.
Please make sure the DSN you choose is available. You can check it by pressing "Test Connection" button in QODBC Setup Screen->General 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. Multiple Selections is available.
After the extraction, you will notice that there is a new table created. It can be viewed in the All Tables list.
Double click the extracted table (In this example, its table Customer), QODBC will connect to QuickBooks and will read all records from QuickBooks and show it in MS Access.
And you can also query tables using MS Access via Create->Query Design
This will create a query (default name "Query1") in your database, please add the table you want to query in "Show Table" Window
Here is the design view of a query:
And please click View->SQL View option to check the SQL Statement of this query.
And this is the SQL View of a query, you can edit your query using SQL Statement