Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba
Before tackling automated methods for incorporating data into XL, we should look at a surprisingly useful and free component included with every version of XL, MS Query. MS Query provides a graphical interface into databases. You can use it to explore database tables, examine columns and contents, join tables, filter tables, sort tables, reveal the SQL statement built behind the scenes, and load results into XL.
Unfortunately, MS Query is not always installed (Use XL’s Help and search for Install Query, or see Installing Microsoft Query) so you may have to find your XL installation disks for this one. When we get to delivering applications to end users we won’t use MS Query, we will only use SQL. So it doesn’t matter if MS Query is installed on their machines. Having it on your machine helps you determine what information you need in your report or application and what the SQL needs to be. If you’re new to SQL, you will find it immensely useful in learning how to join two or more tables and how to deal with certain field types such as dates.
MS Query is found (assuming it has been installed):
Office 2000: Under the “Data” menu, “Get External Data” option, select “New Database Query” sub-menu option.
Office 2002: Under the “Data” menu, “Import External Data” option, select “New Database Query” sub-menu option.
Office 2007: On the “Data” tab, in the “Get External Data” group, click “From Other Sources” icon, and select the “From Microsoft Query” option.
NOTE: If you’re using short menus, you may need to click the menu’s down arrows to see these options.
Before MS Query starts it asks you to select a data source and that will be the topic of our next post.
[kml_flashembed movie="http://www.youtube.com/v/I1qvqMiGyhc" width="480" height="292" wmode="transparent" /]
Click here for northwind database download: Northwind 2003.zip
Next Post: Data Sources