Knowledgebase: Microsoft Products
[QODBC-Desktop] Microsoft Office with MS Query and QODBC
Posted by Juliet (QODBC Support) on 04 January 2010 08:36 AM

Microsoft Office with MS Query and QODBC

Microsoft Office with MS Query and QODBC

Setup QODBC to work with Microsoft Office

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

   Click Start -> Control Panel -> Add or Remove Programs -> Change program Microsoft Office Professional Edition 2003/2007/2010/2013 -> Select Add or Delete Functions -> Check to Choose advanced customization of application -> Check the options to select the items you want to install and uncheck the options to clear the items you do not want installing, be sure to check the Microsoft Query option.

 

(The installer user interface may be different based on Office Edition. Please look for Microsoft Query under the "Office Tools" section)

   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. (Uncheck the Query Wizard, if checked, to create more in-depth queries.) the data source selected here is the Sample Serviced Based Business, which is currently open.

   The Add Tables screen will then pop up. Highlight the table you wish to query, click Add, and then Close.

 

   MS Query should now show the table selected with a scrollable list of all the fields available. If you double-click on the asterisk in the list of fields open, 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.

 

   Sixty-five records are returned from the table in grid view, with no criteria applied to the 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 the following:

   Select * From Customer Where (Customer.ListID='150000-934380929')

 

   The returned recordset contains only onemeets meeting the applied criteria, as shown above. Click on File -> Exit -> Save Changes / No to exit MS Query.

(305 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).