Simple syntax question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
babs said:
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!
Click to expand...


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.
 
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!
 
confused said:
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?
Click to expand...


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.
 
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!
Click to expand...

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)
 
Marshall Barton said:
Maybe someone else can, but I am unaware of any way to do
that without using SQL Server or some other big db server.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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

Jamie.

--
 
Marshall Barton wrote ...
Jamie said:
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.


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


Would you expand on this Jamie?

I was surprised to see that I can indeed get SELECT INTO to
create a new xls file and a new sheet with the specified
name. However, I can't get it to create a new sheet in an
existing file (can't find object error, Excel 2002).

Since there's no way to specify the location of the range, I
don't see how a query could possibly create a new named
range.
 
However, I can't get it to create a new sheet in an
existing file (can't find object error, Excel 2002).
Click to expand...

I'd suggest using the TransferSpreadsheet method instead: you can
transfer a query into a new page or a new named range of a
spreadsheet.

John W. Vinson[MVP]
(no longer chatting for now)
 
John said:
I'd suggest using the TransferSpreadsheet method instead: you can
transfer a query into a new page or a new named range of a
spreadsheet.
Click to expand...


John, I still can't see how it can create a new named range
when there's no way to specify its boundaries.

As part of the Help topic for TransferSpreadsheet's Range
argument:

"Note When you export to a spreadsheet,
you must leave this argument blank. If you
enter a range, the export will fail."

Is this another case of Help foolery ;-)
 
Would you expand on this Jamie?

I was surprised to see that I can indeed get SELECT INTO to
create a new xls file and a new sheet with the specified
name. However, I can't get it to create a new sheet in an
existing file (can't find object error, Excel 2002).
Click to expand...

Here's a demo. First create a totally new workbook: we can use the
CREATE TABLE syntax to do this:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\TotallyNew.xls;].MySheet1
(MyCol1 FLOAT NULL)
;

OK, so we have an existing workbook. Now use the SELECT..INTO syntax
to create a new worksheet in the existing workbook:

SELECT
null AS MyCol2
INTO
[Excel 8.0;HDR=YES;Database=C:\TotallyNew.xls;].MySheet2
;
Since there's no way to specify the location of the range, I
don't see how a query could possibly create a new named
range.
Click to expand...

In short, the SELECT..INTO needs to be able to create a new worksheet.

The above syntax requires the table specified to be a workbook-level
Excel defined Name (aka a 'named Range'). Specifying a worksheet e.g.
[MySheet2$] would cause the SELECT..INTO to fail (the cause of your
'can't find object' error, perhaps?), as would specifying a defined
Name that already exists.

Usually, the provider will create a new sheet with the same name as
the specified defined Name. If the sheet, but not he defined Name,
already exists and is clear (you can easily create these circumstances
by issuing a DROP TABLE on the defined Name i.e. the Name's definition
and data is removed but not the sheet) then the SELECT..INTO creates
the defined Name on the existing sheet of the same name. However, if
the existing sheet of the same name is 'dirty' i.e. (create these
circumstances by opening the workbook in the Excel UI and delete the
defined Name definition but not the data) then a new sheet is created
with by appending a number to the sheet name e.g. if MySheet1 already
exists, the defined Name named MySheet2 is created on a new sheet
named MySheet21; if MySheet21 exists the new sheet will be MySheet22.

If you require any more details, post back.

PS Sorry, I can't resist but do you know there is an industrial park
in my local town named after you:

http://www.google.com/search?q="marsh+barton+is"

Jamie.

--
 
John Vinson said:
I'd suggest using the TransferSpreadsheet method instead: you can
transfer a query into a new page or a new named range of a
spreadsheet.
Click to expand...

I'd suggest a query will always be more powerful because you have more
control. From what I've read here about TransferSpreadsheet, it's
clear it's running DROP TABLE and SELECT..INTO syntax under the hood
anyway. Maybe its just me but I don't like the idea of a hidden DROP
TABLE being issued on my data. I've yet to see a wizard that write
good sql code <g>.

Jamie.

--
 
Jamie said:
Marshall Barton wrote ...
Would you expand on this Jamie?

I was surprised to see that I can indeed get SELECT INTO to
create a new xls file and a new sheet with the specified
name. However, I can't get it to create a new sheet in an
existing file (can't find object error, Excel 2002).
Click to expand...

Here's a demo. First create a totally new workbook: we can use the
CREATE TABLE syntax to do this:

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\TotallyNew.xls;].MySheet1
(MyCol1 FLOAT NULL)
;

OK, so we have an existing workbook. Now use the SELECT..INTO syntax
to create a new worksheet in the existing workbook:

SELECT
null AS MyCol2
INTO
[Excel 8.0;HDR=YES;Database=C:\TotallyNew.xls;].MySheet2
;
Since there's no way to specify the location of the range, I
don't see how a query could possibly create a new named
range.
Click to expand...

In short, the SELECT..INTO needs to be able to create a new worksheet.

The above syntax requires the table specified to be a workbook-level
Excel defined Name (aka a 'named Range'). Specifying a worksheet e.g.
[MySheet2$] would cause the SELECT..INTO to fail (the cause of your
'can't find object' error, perhaps?), as would specifying a defined
Name that already exists.

Usually, the provider will create a new sheet with the same name as
the specified defined Name. If the sheet, but not he defined Name,
already exists and is clear (you can easily create these circumstances
by issuing a DROP TABLE on the defined Name i.e. the Name's definition
and data is removed but not the sheet) then the SELECT..INTO creates
the defined Name on the existing sheet of the same name. However, if
the existing sheet of the same name is 'dirty' i.e. (create these
circumstances by opening the workbook in the Excel UI and delete the
defined Name definition but not the data) then a new sheet is created
with by appending a number to the sheet name e.g. if MySheet1 already
exists, the defined Name named MySheet2 is created on a new sheet
named MySheet21; if MySheet21 exists the new sheet will be MySheet22.

If you require any more details, post back.

PS Sorry, I can't resist but do you know there is an industrial park
in my local town named after you:

http://www.google.com/search?q="marsh+barton+is"
Click to expand...


Thanks for the clarification, Jamie. It's actually pretty
much as I understood it. The part about a sheet with the
partially created name can be an issue sometimes, at least
in the few situations I've work on. Maybe that's why I set
things up so I can use INSERT INTO instead of SELECT INTO.

As for TransferSpreadsheet, I agree that it does too much
behind the scenes for me to be comfortable. Most of the
time, I actually prefer to use DAO (sometimes even more than
queries).

I got a kick out of that link, I'm even more famous than I
knew ;-))
 
Back
Top