MS Query Tips

Here are a few tips on using MS Query :-

MS Query Overview  Back
The Help file (Msqry32.chm) is worth browsing to get an overview of the capabilities of MS Query.

Input of Query Parameters  Back
To specify a user (or spreadsheet) selection criteria use [Input] as the Value in the Criteria View, where Input is the string used in the user dialogue box. When returning to Excel you will be asked whether you wish to specify a spreadsheet cell to contain the criteria.

Preserving Spreadsheet Format  Back
If you wish to preserve the formatting in your spreadsheet when refreshing the data then you need to specify that in the External Data Range Properties. Those properties are accessed through the External Data toolbar, when the cursor is positioned anywhere within the data range.

Data Range Properties  Back
It is worthwhile examining the various properties in the External Data Range Properties to see whether any of them are appropriate to your requirements.

Wildcard Character  Back
MS Query uses % for a wildcard character, whereas MS Access uses *.

Read-only Data Source  Back
To prevent inadvertently changing the data in your source database ensure that the Data Source is set to read-only when it is set-up initially.

Changing the Data Source  Back
Changing the source database in MS Query can be done by first removing the current query and second using Add Table to change the source database and then rebuild the query. However, for the easy way to maintain the Access database links within your Excel spreadsheets, download ExcelDBLinks.

Query Specification  Back
The full specification of the MS Query query can be viewed by saving it from within MS Query as a qry file and then examining that file with a text editor like Notepad.

 

For the easy way to maintain the Access database links within your Excel spreadsheets, download ExcelDBLinks. The ExcelDBLinks utility is free to download and use.

 

This Free Visual Basic Utility is the result of a joint development initiative between Influential Computers and Axsis Consultants.

Page last updated 04/03/2010 14:30:57      Copyright © 2003 Influential Computers Ltd. and Polly Projects All Rights Reserved