Microsoft Office with MSQuery and QODBC

High bandwidth users, please see our flash video tutorial or Windows Media movie tutorial on Microsoft Office.

Setup QODBC to work with Microsoft Office

Before using any Microsoft Office 2000 applications, you must ensure that the Microsoft Query builder is installed.

Double Click on the Microsoft Office Setup, Click to update the installation, check the options to select the items you want installed and uncheck the options to clear the items you do not want installed, be sure to check the “Microsoft Query” option.

Click “Update Now” to install the components.

(Make sure you have QuickBooks and the Company file open “minimized” that you want to query)

Microsoft Query

This is a utility worth having a desktop shortcut to, especially for those who are not very familiar with creating SQL statements. The MS Query executable can be launched from the following directory: 

C:\Program Files\Microsoft Office\Office\MSQRY32.EXE

Select the option File / New, and you will see the following screen.

Highlight the data source you wish to use and click “OK”. The data source selected here is the Sample Serviced Based Business, which is currently opened.  (Uncheck the Query Wizard if checked, to create more in depth queries)

 

The Add Tables screen will then pop up, highlight the table you wish to query and the click “Add” and then “Close”.
MS Query should now show the table selected and with a scrollable list of all the fields available. If you double click on the asterisk in the list of fields available, it will populate the table with all of the available fields and records of that table or select only the fields you are interested in.

A total of 43 records are returned from the table in grid view, with no criteria applied to underlying SQL statement. (Select * From Customer)

To apply criteria to the underlying SQL statement (Select * from Customer) click “Criteria / Add Criteria”

The Add Criteria panel will allow you to select the available fields from the query from a dropdown selection menu along with an operator and the associated values for that field. The above selection will generate an underlying SQL statement equivalent to:

Select * From Customer Where (Customer.BillAddressPostalCode=’94482′)

The returned recordset contains 7 records meeting the applied criteria as shown above. Click on File / Exit “Save Changes / No” to exit MS Query.Tutorials:

 

Download and Activate QODBC Driver