Excel, VBA and Long SQL Query Problem

[tobyjuggler]

I have a rather long SQL query to run with several date/time variables in it. The way I've done it in the past is to record a macro, paste the SQL into the query then afterwards turn the dates/times into variables by manually editing the code in the macro, so that next time it is run the SQL picks up the correct date/time and the correct data is retrieved. This has always worked okay in the past. However, the latest query I have is very long and complicated and I now appear to be unable to use the above method due, it appears, to the length of the query. It seems that the macro itself will only accept so many lines of an SQL query.

Please, does anybody know how to get round this problem?

Is it possible to save the query as an external query (.dqy) and also use variables within that query when a macro tries to run it? If so, how!? I've tried it, but it fails at the line ".Refresh BackgroundQuery:=False". With a general ODBC Error. I have put all of the necessary details in there, eg DSN etc, but no joy.

I've tried putting the SQL itself into several cells on a spreadsheet, altering the dates so that they a correct each time the query is run, concatenating the cells, turning the results into a variable and executing the SQL in a macro, but this also fails at the line ".Refresh BackgroundQuery:=False". Could this method be adapted?

Splitting the query into two shorter ones doesn't appear to be an option as there a loads of 'or' statements in it, several of which overlap. This would cause some results to be duplicated, which I would like to avoid!

Please, please help me!!

TIA

tobyjuggler

[andi_g69]

Did you think of using a stored procedure instead of constructing a sql statement on the fly?

This will have several advantages:
1) Your VBA code to call the query is simpler ==> easier to read and debug
2) The query call is faster as you just pass a single line of SQL to your DB
3) Normally a Stored Procedure performs faster than a "normal" Select statement
4) Seperating query logic from the front-end gives you the possibility to change the DB logic and / or datamodel without having to change and re-distribute the front-end.

[tobyjuggler]

Thanks for responding. The problem is I have no real experience with SQL itself. I understand the structure of the database itself and I was only able to get the SQL by carrying out a query in Excel, which kindly gives you the SQL statement after you have made your query

Did you think of using a stored procedure instead of constructing a sql statement on the fly?

Apologies, but I don't even know what a stored procedure is.

This will have several advantages:
1) Your VBA code to call the query is simpler ==> easier to read and debug
2) The query call is faster as you just pass a single line of SQL to your DB
3) Normally a Stored Procedure performs faster than a "normal" Select statement
4) Seperating query logic from the front-end gives you the possibility to change the DB logic and / or datamodel without having to change and re-distribute the front-end.

The structure of the database itself is more or less set and I do not have rights to make any changes (adding and deleting tables etc), I am only able to add entres to specific tables.

I bet you wish you hadn't responded now .

I think my question more belongs in a VBA forum and I have asked in one, but am yet to get any responses after a couple of days. I am just hoping, by asking here, that there may be somebody here who works with both VBA and SQL and could supply as simple an answer as possible!

[andi_g69]

A stored procedure is a set of predefined sql statements stored on the DB server.
If you do not have the right to create objects in the DB they are not an option obviously.

Without knowing more about the data model and what you want to achieve with your query in Excel it is hard to advice.

Sorry for not being able to provide any detailed help

[rwendel]

Would your Sys Admin allow you to generate a Stored Procedure? If not, ask if he/she would take the code from you and use their permissions in order to create the stored procedure.

If so, general syntax is as follows:

CREATE PROC[EDURE] ProcedureName [@var datatype [ = default value ] ...]
AS
[ANY SQL STATEMENT WHICH CAN USE THE LIST OF VARS PROVIDED IN THE 'COMMAND LINE ARGS']

Then, in order to execute the stored procedure using the syntax:

EXEC[UTE] ProcedureName [vars]

Another Solution:

You say your SQL expression contains a large list of boolean expressions.

Have you reduced the boolean expression via boolean algebra?
Since you are using T-SQL, have you considered more standard programming constructs (IF/ELSE, CASE, etc...)?