Prompting for parameters in MSQuery to pass into a stored procedure

[Steff]

Good afternoon and thanks in advance for any advice.

I'm trying to create a complicated query in MSQuery which prompts the
user for certain parameters (eg product group, item code etc) and
extracts the data from flat files on an SQL server database via ODBC.

I have already discovered the limitations of MSQuery (my query joins
multiple tables and there is a left outer join involved!) and have
followed the advice already posted on this forum to create a stored
procedure on my SQL server database, and reference the stored procedure
from within my query, including input parameters.

Everything works fine up until the point that I try to give meaningful
labels to my parameter prompts (instead of just Parameter 1, Parameter
2, Parameter 3 etc). Because the query can't be represented
graphically, I can't do what I usually do and enter the label within
the [] on the criteria tables. When I try to do it from the SQL
screen, I just get errors.

I can do it by embedding the query in an XL spreadsheet and adding the
Prompts to the parameters on the actual spreadsheet, but my users will
probably want to run multiple queries on the same spreadsheet (several
items, customers etc), and for the life of me, I cannot work out how to
embed the prompts into the query itself.

Please tell me it is something simple that I have missed!

 

[dbahooker]

flat files on a SQL Server database.

good stuff.. lol.

a) don't keep flat files in a database; you keep tables
b) dont use excel for db tasks; it doesn't have 1/10th of the
functionality you need
c) access data projects are really really beautiful functionity for
sprocs
d) spit on anyone that uses excel. it is obsolete.

 

[Steff]

OK, OK, I stand corrected on the terminology. It is not my
strongpoint. Also, I am new to all of this stuff so my humblest
apologies if I ask stupid questions.

Yes I have tables on my database.

As for using Excel, unfortunately I have no option at the moment.
Query is what I have and Query is what I have to use. It is not my
choice, believe me!

So, this being the case, back to my original question, is it possible
to embed my parameter prompts in the actual query?

Actually, in the meantime, I have come up against another possible
limitation....

I have created a stored procedure that contains 3 queries. This works
fine on SQL server, but when I call the stored procedure from Query, it
only returns the results from the first query.

So is this just another reason for spitting on Excel (if you have that
luxury), or is there something I can do?

flat files on a SQL Server database.

good stuff.. lol.

a) don't keep flat files in a database; you keep tables
b) dont use excel for db tasks; it doesn't have 1/10th of the
functionality you need
c) access data projects are really really beautiful functionity for
sprocs
d) spit on anyone that uses excel. it is obsolete.

Good afternoon and thanks in advance for any advice.

I'm trying to create a complicated query in MSQuery which prompts the
user for certain parameters (eg product group, item code etc) and
extracts the data from flat files on an SQL server database via ODBC.

I have already discovered the limitations of MSQuery (my query joins
multiple tables and there is a left outer join involved!) and have
followed the advice already posted on this forum to create a stored
procedure on my SQL server database, and reference the stored procedure
from within my query, including input parameters.

Everything works fine up until the point that I try to give meaningful
labels to my parameter prompts (instead of just Parameter 1, Parameter
2, Parameter 3 etc). Because the query can't be represented
graphically, I can't do what I usually do and enter the label within
the [] on the criteria tables. When I try to do it from the SQL
screen, I just get errors.

I can do it by embedding the query in an XL spreadsheet and adding the
Prompts to the parameters on the actual spreadsheet, but my users will
probably want to run multiple queries on the same spreadsheet (several
items, customers etc), and for the life of me, I cannot work out how to
embed the prompts into the query itself.

Please tell me it is something simple that I have missed!

 

[ajmorales]

I ran into the same issue 6 months ago as I was learning to 'connect
through Excel. I came up with a solution that is easy to manage a
query updates & spec changes occur.
I discovered this solution by running the macro recorder a few time
while 'building' queries manually via MsQuery. Studying the resultin
macros, I discovered the query can be a text string fed through a quer
VBA module.
Each query spec 'Range' is simply a text concatination of cell
containing the query spec line items as text. Any of the query spe
line item(s) CAN be the result of an Excel function; date parameters
filtering etc.

Query date spec line item example in the WHERE clause:
AND Table1.DateCreated Between '2006-06-01 00:00:00' And '2006-06-0
23:59:59'
Excel function:

="AND Table1.DateCreated Between '"&sqlDateStart&"' An
'"&sqlDateEnd&"' "

Variables sqlDateStart & sqlDateEnd are date results selected from
calendar control dialog on a user form.

Here is the VB Module I now use as my 'cookie' cutter!:

Sub UpdateQuery ()
sheetSQLQuery.Select
ConnectionString1 = Range("ConcatinatedSqlConnectionSpec").Value
ConnectionString2 = Range("ConcatinatedSqlServerPortSpec").Value
QuerySpecSelect=Range("ConcatinatedSelectQrySpecs").value
QuerySpecFrom=Range("ConcatinatedFromQrySpecs").value
QuerySpecWhere=Range("ConcatinatedWhereQrySpecs").value
QuerySpecOrderBy=Range("ConcatinatedOrderByQrySpecs").value

Range("TopLeftCellForQueryRecords").Select
With Selection.QueryTable
.Connection = Array(Array(ConcatinatedConnectionString1)
Array(ConcatinatedConnectionString2))
.CommandText = QuerySpecSelect & QuerySpecFrom & QuerySpecWher
& QuerySpecOrderBy
.Refresh BackgroundQuery:=False
End With
End Su

 

[ajmorales]

Here's how I send the connection string(s) from an Excel worksheet range
to MsQuery....

Spec Connection1Specs (Excel range Y2:Y10)
1 ODBC;
2 DRIVER=SQL Server;
3 SERVER=ODBcDataBaseName;
4 UID=GuestLogInName;
5 PWD=GuestPassWord;
6 APP=Microsoft® Query;
7 WSID=NetWorkComputerName;
8 DATABASE=ServiceActivity;
9 Network

Spec As Excel Function
7 ="WSID="&SqlSWID&";"
which is passed to Excel from the VBA code on file open:
Computer = Environ("ComputerName")
Range("SqlSWID").Value = Computer

Cell Y11 (named: SqlConnectionSpec) concatenates the Connection1 specs
as follows:
Y11=Y2&Y3&Y4&Y5&Y6&Y7&Y8&Y9&Y10

Spec Connection2 Specs (Excel range Y14:Y15)
1 DBMSSOCN;
2 Address=ODBcServerName,1513

Cell Y16 (named: SqlServerPortSpec) concatenates the Connection2 specs
as follows:
Y16="="&Y14&Y15