Parameter Driven Query

[mr tom]

Hi,

I'm used to using MS Query to return data to Excel, but there's a whole
stack of queries that I run each month (literally, dozens) where the date
range needs to be changed.

I'm wondering whether I could put the chosen date ranges into an excel
spreadsheet, maybe in the format:

Col 1 Col 2 Col 3 Col 4
sheet query start date end date

....and use VBA to modify the SQL for me.

Alternatively, can the SQL itself be written to request a parameter from a
particular location?

Any ideas? This could be a massive time saver!

Cheers,

Tom.

 

[Joel]

I usally start by Recording a macro when I perform a querry. Then I modify
te querry to make it general purpose. if you post the recorded macro I can
make the modification you require.

to start a Record macro from worksheet
1) Tools - Macro - Record New Macro

2) Perform the querry

3) Tools - Macro - Stop Recording

 

[mr tom]

Thanks Joel,

I really should have done that earlier. I'd (stupidly) assumed that a
recording macro wouldn't track my activity within MS Query.

I play about with VBA a bit, so now I've got a recorded one to play with, I
can probably work it out from here.

Thanks so much for pointing me in the right direction.

Cheers.

Tom.

 

[RB Smissaert]

Better to move away from MS Query and code with ADO where you have
parameterized queries and a lot of other useful options.

RBS

 

[mr tom]

It probably would be better, but I'm still getting to grips with VBA and SQL.


I'm a project manager (not an IT one) rather than a techie, although I do as
much techie stuff as I can as I don't like to be helpless...

Cheers.

 

[RB Smissaert]

There really isn't that much to learn there only a few objects, methods and
properties. Then there always is the public.data.ado NG to ask if you get
stuck. MS Query is fine for a quick query from the Excel interface, but if
you
get into anything that goes a bit further you are better of with ADO.

RBS

 

[mr tom]

Thanks. That sounds as if it could be worth thinking about.

I've got someone who does loads in ms query and this could be a good next
step for them.

 

[mr tom]

Just a couple of quick questions:
What's the difference between ADO and ADO.NET? - Which do I need for Excel?
Where can I learn (idiot level) about this? Is there a good book or website?

Cheers.

 

[bart.smissaert]

ADO.NET is for the dotnet languages like C# etc. so you want ADO.
Plenty of good tutorials on the web to get started. Just Google on
something like ADO tutorial.

RBS

 

[ward376]

MS Query is set up to use parameters easily - you can use square
brackets with a space in between in the interface (not the wizard, the
advanced interface) for the criteria values. When you run or refresh
the query Excel will display a dialog where you can choose to use a
range (cell)/ formula or value for the parameter and whether to always
use this range/formula/value for this parameter. I've used this
feature with input boxes/userforms extensively with success.

If you're editing the SQL statement directly, you can use a question
mark for the parameter value and Excel will behave as described. (The
ui is just writing SQL based on your input anyhow)

You may run into some trouble trying to record complex queries (using
the advanced interface) as you write them. (can't record) You can work
around this by writing the query then record refreshing it.

Cliff Edwards

 

[mr tom]

Thanks Cliff. That sounds like the best answer yet.

Tom.