Knowledgebase: Microsoft Products
[QODBC-Desktop] Microsoft Office with MS Query and QODBC
Posted by Juliet 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 Choose advanced customization of application -> 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.

 

(the installer user interface may be different based on Office Edition.  Please look for Microsoft Query under "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. 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 65 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.ListID='150000-934380929')

 

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

(302 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).