Simple syntax question

[babs]

Hi,

I am calling a query from a "onclick" method in a form by just using the
Query name in an "execute" statement.
Now I want to pass a parameter to the query. I have tried every combination
of syntax I can think of, brackets commas etc. Ive been searching the web for
hours and cant seem to find the answer to something I know must be simple!
Can anyone advise me please?

Thanks!

 

[Marshall Barton]

I am calling a query from a "onclick" method in a form by just using the
Query name in an "execute" statement.
Now I want to pass a parameter to the query. I have tried every combination
of syntax I can think of, brackets commas etc. Ive been searching the web for
hours and cant seem to find the answer to something I know must be simple!

You should construct the query's SQL in code and use that
with the Execute method:

strSQL = SELECT f2, f3, ... " _
& "FROM table " _
& "WHERE fn = " & somevalue
db,Execute strSQL

OTOH, if you can live with the popup messages, you could
probably do what you want by using RunSQL instead of
Execute.

 

[confused]

Hi,,
thanks for your help.
My query already exists and is saved, so I wouldnt want to duplicate by
reconstructing it in the code.
I currently have:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"

The only change I want to make to the code is to pass a parameter to the
query. So I would expect to write something like:
strQuery = "MyQueryName(2)"
Except this syntax is obviously wrong. so what I want is the syntax to pass
a paramaer to the query. Can anyone help?

Thanks!

 

[Marshall Barton]

My query already exists and is saved, so I wouldnt want to duplicate by
reconstructing it in the code.
I currently have:
strQuery = "MyQueryName"
objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strQuery & "]"

The only change I want to make to the code is to pass a parameter to the
query. So I would expect to write something like:
strQuery = "MyQueryName(2)"
Except this syntax is obviously wrong. so what I want is the syntax to pass
a paramaer to the query. Can anyone help?

Maybe someone else can, but I am unaware of any way to do
that without using SQL Server or some other big db server.

Maybe you're doing something different than I've ever tried,
because I thought a make table query would not work with an
Excel workbook. I've always used an INSERT INTO query to do
this kind of stuff.

 

[John Vinson]

Hi,

I am calling a query from a "onclick" method in a form by just using the
Query name in an "execute" statement.
Now I want to pass a parameter to the query. I have tried every combination
of syntax I can think of, brackets commas etc. Ive been searching the web for
hours and cant seem to find the answer to something I know must be simple!
Can anyone advise me please?

Thanks!

It's not as simple as it might appear. Try:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("MyQuery")

Then either...

For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm

to look at form references or issue prompts... or ... set them
explicitly:

qd.Parameters(0) = <some value>
qd.Parameters(1) = <some other value>

qd.Execute dbFailOnError


John W. Vinson[MVP]
(no longer chatting for now)

 

[Jamie Collins]

Maybe someone else can, but I am unaware of any way to do
that without using SQL Server or some other big db server.

I concur. When I try to execute:

CREATE PROCEDURE MyBadProc
(arg_Table VARCHAR(255))
AS
SELECT *
FROM arg_Table
;

I get an error, 'Parameter arg_Table specified where a table name is
required'.

Of course, even though the equivalent can be written in e.g. SQL
Server, such dynamic sql code is frowned upon (to say the least).

Maybe you're doing something different than I've ever tried,
because I thought a make table query would not work with an
Excel workbook.

You sure can use the SELECT..INTO syntax with Excel, either as the
target or the source. In fact, it is a great way of dynamically
creating Excel data because, when Excel is the target, if the workbook
and/or worksheet and/or defined Name specified does not already exist,
the Jet provider creates it.

I've always used an INSERT INTO query to do
this kind of stuff.

INSERT INTO syntax only works when the Excel workbook and table
already exists.

Jamie.

--