[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, and create a blank database.
Click on the "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 modifications will not be uploaded unless you build another query.
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 operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.
We have selected a link to the data source in this example by creating a linked table.
Click the OK button. Then you will see the 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 the "Test Connection" button in QODBC Setup Screen->General Window.
Once the connection is built, Access will return a list of available tables in the Select Tables window. Multiple Selections are available. Please, select the tables you want to extract.
After the extraction, you will notice a new table has been created. It can be viewed in the All Tables list.
Double-click the extracted table (In this example, it's table Customer). QODBC will connect to QuickBooks, 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 the "Show Table" Window.
Here is the design view of a query:
And please click the 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.