We all know we can use MS Query to get data from a SQL server. Typically though, we pull from a Table or a View. Well in some organizations, the IT department wants all interaction with the server to be done through Stored Procedure. This adds a level of risk management and makes the DBAs feel better.
So today, I’ll show you how to easily make Excel run a Stored Procedure to get data.
An Access parameter query is a kind of interactive query that prompts you for criteria before the query is run. Parameter queries are useful when you need to ask the query different questions using different criteria each time you run it.
Now we all know you can pull data from Access into Excel using MS Query. The problem is that MS Query doesn’t let you pull in Parameter queries. There may be a technical reason for this, but I like to think it’s just Microsoft’s way of keeping things interesting. After all, technical roadblocks are the spice of life.
If you’ve had enough spice, I’ll show you a VBA workaround that will enable you to run an Access parameter query from Excel.
I often use MS Query to pull external data into Excel. I find it to be an extremely powerful way to quickly pull together interactive reporting without a lot of effort. If you’re new to MS Query, you may want to check out Kusleika’s intro to MS Query to get the gist.
.
Anyway, one of the more common requests I get is to allow a user to dynamically pass multiple criteria to a parameter. For example, a manager may want to pull in data for Tulsa, and Dallas, and Seattle at the same time.
» More: Passing Multiple Values to One Parameter
