syntax for calling Query with parameter from VB

[Guest]

Hi,

I have a button which runs a query and copies the contents to Excel
eg:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close

Now I have changed the Query to require a parameter to be passed to it.
Does anyone know the syntax I should use to incorporate this into my code?
E.G i tried:
strQuery = "MyQueryName(2)"
but I think i have the syntax for passing a parameter wrong

thanks for any help you can give!

 

[Alex Dybenko]

i think best for you would be to modify MyQueryName SQL to replace parameter
with its value, save it and then run your select into statement

 

[Guest]

Hi,

Thanks for your help, but Im sorry i dont understand what you are aking me
to do. Are you saying I shouldnt have a parameter? Because I need a
parameter, for illustration purposes it says "2" in my description, but
obviously this would be a variable number which is input by the user

i think best for you would be to modify MyQueryName SQL to replace parameter
with its value, save it and then run your select into statement

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi,

I have a button which runs a query and copies the contents to Excel
eg:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close

Now I have changed the Query to require a parameter to be passed to it.
Does anyone know the syntax I should use to incorporate this into my code?
E.G i tried:
strQuery = "MyQueryName(2)"
but I think i have the syntax for passing a parameter wrong

thanks for any help you can give!

 

[Jamie Collins]

I have a button which runs a query and copies the contents to Excel
eg:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close

This isn't a query as such because the SELECT..INTO syntax does not
return a results set. Think of it as 'action' sql such as INSERT INTO
syntax.

Now I have changed the Query to require a parameter to be passed to it.
Does anyone know the syntax I should use to incorporate this into my code?
E.G i tried:
strQuery = "MyQueryName(2)"
but I think i have the syntax for passing a parameter wrong

How you call your procedure depends on have you defined it e.g. if I
define my procedure as:

CREATE PROCEDURE MyProc
(arg_ID INTEGER)
AS
SELECT ID, GenComment, LongCol, MyAdd
INTO [Excel 8.0;Database=C:\Blah.xls;].Blah
FROM Blah WHERE ID=arg_ID
;

then I can successfully call it using:

EXECUTE MyProc 1;

Jamie.

--

 

[Alex Dybenko]

what i mean:
you have query MyQueryName with parameter P1
make a new query MyQueryName2
before you run select into - replace parameter of MyQueryName with value:
currentdb.querydefs("MyQueryName2").sql=replace(querydefs("MyQueryName").sql,"[P1]",2)
and run your select into, but use MyQueryName2 there

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi,

Thanks for your help, but Im sorry i dont understand what you are aking me
to do. Are you saying I shouldnt have a parameter? Because I need a
parameter, for illustration purposes it says "2" in my description, but
obviously this would be a variable number which is input by the user

i think best for you would be to modify MyQueryName SQL to replace
parameter
with its value, save it and then run your select into statement

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

Hi,

I have a button which runs a query and copies the contents to Excel
eg:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close

Now I have changed the Query to require a parameter to be passed to it.
Does anyone know the syntax I should use to incorporate this into my
code?
E.G i tried:
strQuery = "MyQueryName(2)"
but I think i have the syntax for passing a parameter wrong

thanks for any help you can give!

 

[Van T. Dinh]

I am not sure but it looks like the O.P.asking about JET / Access and your
answer seems to be related to another database engine (SQL Server, perhaps)?

--
HTH
Van T. Dinh
MVP (Access)

I have a button which runs a query and copies the contents to Excel
eg:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"
objDB.Close

This isn't a query as such because the SELECT..INTO syntax does not
return a results set. Think of it as 'action' sql such as INSERT INTO
syntax.

Now I have changed the Query to require a parameter to be passed to it.
Does anyone know the syntax I should use to incorporate this into my code?
E.G i tried:
strQuery = "MyQueryName(2)"
but I think i have the syntax for passing a parameter wrong

How you call your procedure depends on have you defined it e.g. if I
define my procedure as:

CREATE PROCEDURE MyProc
(arg_ID INTEGER)
AS
SELECT ID, GenComment, LongCol, MyAdd
INTO [Excel 8.0;Database=C:\Blah.xls;].Blah
FROM Blah WHERE ID=arg_ID
;

then I can successfully call it using:

EXECUTE MyProc 1;

Jamie.

--

 

[Van T. Dinh]

If you want to look at it that way, yes. I prefer to say that you resolve
the Parameter by code.

What I normally do with this is to create 2 Queries:

1. qselBase: which is your MyQuerName without any criteria / parameter.

2. qselCustom: use the same SQL String as above since it doesn't matter: you
will modify this just before you run the INSERT...INTO... SQL.

3. You use code to modify the SQL String of the qselCustom resolving all the
parmeters so that it is actually a non-parametrised Query.

4. You use the qselBase as the Source for your INSERT...INTO... SQL.

The code should be some thing like (make sure you have DAO in the
References)

***Untested***
Dim objDB As DAO.Database
Dim qdfCustom As DAO.QueryDef

Set objDB = DBEngine(0)(0)
Set qdfCustom = objDB.QueryDefs("qselCustom")
qdfCustom.SQL = objDB.QueryDefs("qselBase").SQL & _
" WHERE [YourField] = 2"
' (as an example where the "Parameter" is replaced by the value 2.)
qdfCustom.Close
Set qdfCustom = Nothing

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM qselCustom", dbFailOnError

Set objDB = Nothing
***End***

 

[Jamie Collins]

CREATE PROCEDURE MyProc
(arg_ID INTEGER)
AS
SELECT ID, GenComment, LongCol, MyAdd
INTO [Excel 8.0;Database=C:\Blah.xls;].Blah
FROM Blah WHERE ID=arg_ID
;

then I can successfully call it using:

EXECUTE MyProc 1;

I am not sure but it looks like the O.P.asking about JET / Access and your
answer seems to be related to another database engine (SQL Server, perhaps)?

No.

The CREATE PROCEDURE is indeed Jet 4.0 sql syntax. SQL Server
arguments have a @ prefix. For some reason CREATE PROCEDURE seems to
be unfamiliar with MS Access MVPs (<g>):

http://groups.google.com/[email protected]

The syntax [Excel 8.0;Database= etc], a variation of the IN <data
source> syntax, is Jet ODBC, AFAIK. The SQL Server T-SQL equivalents
are OPENROWSET e.g.

SELECT Col1, Col2
INTO Table3
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=C:\Tempo\db.xls','SELECT Col1, Col2 FROM [General$]')

and OPENDATESOURCE e.g.

SELECT Col1, Col2
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source=C:\Tempo\db.xls;Extended Properties=Excel 8.0')...[General$]

(those three dots are literal syntax, not an ellipsis!) but I've not
been able to get OPENDATESOURCE to work with T-SQL's SELECT..INTO
syntax.

Jamie.

--

 

[Van T. Dinh]

I am aware of the CREATE PROCEDURE syntax in JET. That's why I wrote I
wasn't sure. I noted that the O.P. was using DAO code which doesn't
recognise Procedures(AFAIK). Hence, my post about the Procedure.

I was playing with these new syntaxes a while ago. The problem I found that
a number of things worked OK but you can't see these objects or execute in
the Access interface which I am used to. I can't remember exactly but I
think Procedures don't appear in the GUI either.

Also, I think ADO code is needed to run Procedures and some of the new
syntaxes in JET 4, e.g. ALTER TABLE, DEFAULT.

 

[Van T. Dinh]

I did a quick test and found that the "Make-Table" Query appears when the
CREATE PROCEDURE is executed. However, DAO code like:

CurrentDB.Execute "MyProcedure 98"

(98 is the argument value in my test) won't work. ADO code works fine.