Running an SQL Query with Parameters

Three generations of the same task

by Dany Hoter, Datarails Solutions Architect

Task definition

The goal is to import data into Excel from an SQL table with some input parameters. The parameter values should come from cells in Excel, and the data should be refreshed automatically anytime the value of any parameter changes in the grid.

Oldest method: MS-Query

Oddly enough, the oldest method of MS-Query is the easiest, and doesn’t require any use of VBA. Its main drawback is that it supports only ODBC drivers, which involves a very old infrastructure that was first released in 1992. In addition, it doesn’t use Power Query technology and it’s ugly.

In Excel 2016, MS-Query can be found in the data tab.

SQL image 1

Starting a query immediately hints at how old this UI is:

sql image 2

Select a DSN, and then select a table, columns and sorting order until the Query Wizard asks if you want to further edit the query:

sql image 3

Once inside Microsoft Query, you can do a lot of SQL editing without getting into SQL syntax. In the criteria tab, you may define filters to query and can specify that the filter values will come from parameters.

sql image 4

The notation <=[ToYear] means that the value is a parameter, and the prompt for this parameter will be ToYear.

Returning the query to Excel prompts the standard import dialog:

sql image 5

Click properties, and then Definition and Parameters.

sql image 6

You can link parameters to cells in Excel.

sql image 7

You can also check the box that says Refresh automatically when cell value changes. Checking this option for more than one parameter means that the query may fire multiple times.

At this point, anytime that cells tied to a parameter change, the query will run and produce new data. The end result should be similar to this file.

Next week: A newer method for running an SQL query with parameters.

About the author:

Dany Hoter is a renowned Excel guru who works as a solutions architect at Datarails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe.