In Access 2003 (if it's not in 2003 - upgrade!) search for "Create and
use a parameter query" in Help. This will allow you to set up the Access
version of an SQL Server stored proc.
In you web page code you'd then want to have something like
Select * from tablename Where @var1 = 'something' or @var2 =
'somethingelse'
This means you can do all the hard work of the SQL query (like joins
etc) in Access - you can even use the same paramter for multiple fields
- like if you wanted to search for 'James' in six different fields, all
the 'OR' stuff is in the access query
For instance in that case the saved Access query (called qryPerson)
would be:
Select ID From tblPerson Where FirstName = @var1 OR SecondName = @var1
or Nickname = @var1
(and of course you could join multiple tables etc into that query and
make it as complex as you like. Nice and easy to build using the Access
Query Designer)
That would be a much more simplistic stored query than you would use of
course, I would expect the payoff to be where the stored query gets to 5
or more lines, but the beauty is the next bit - far fewer lines of ASP
code still runs a very very big query!
The full web page code (excluding connection info) on the other hand
would be a sleek and nifty
cmd.CommandText = "qryPerson"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter &_
("@var1",adVarChar,adParamInput ,10,"James")
cmd.Execute
Basically it means the hoops the query has to jump through are in Access
rather than on the web page - more efficient. To pass more parameters
when the stored query demands more parameters, all you need to do is add
more of these lines, just changing the info
cmd.Parameters.Append cmd.CreateParameter &_
("@var1",adVarChar,adParamInput ,10,"James")
Reasons for using this method - performance. From ASPFree... "executing
a Stored Query using the Connection object with the EXECUTE syntax. The
difference between these fastest and slowest times is 2.519 seconds,
which means that using method 3c instead of 1a results in a performance
increase of 36%!! That's more than a third faster! "
Here's some links on the subject as well:
http://www.15seconds.com/issue/020919.htm
http://www.webconcerns.co.uk/asp/accessqueries/accessqueries.asp
Hope that's more helpful!
Anna
> -----Original Message-----
> From: Dian Chapman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, 26 August 2004 03:50
> To: [EMAIL PROTECTED]
> Subject: RE: [ASP] Multi Criteria Searches
>
> Thanks, but sorry...can you give me a little more details. I
> mean, wouldn't that still end up as a huge statement of
> ORs...just built with vars?
> Sorry...just not sure what you mean.
>
> Dian ~
>
> -----Original Message-----
> From: Anna Crooks [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 25, 2004 9:54 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [ASP] Multi Criteria Searches
>
> You should be able to write an Access Query and pass the
> variables to it.
> Not as quick as an SQL Server stored proc but better than
> executing the full query from the web page.
>
> HTH
> Anna
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/2004
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> $9.95 domain names from Yahoo!. Register anything.
> http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/17folB/TM
> --------------------------------------------------------------
> ------~->
>
> --------------------------------------------------------------
> -------
> Home : http://groups.yahoo.com/group/active-server-pages
> ---------------------------------------------------------------------
> Post : [EMAIL PROTECTED]
> Subscribe : [EMAIL PROTECTED]
> Unsubscribe: [EMAIL PROTECTED]
> ---------------------------------------------------------------------
> Yahoo! Groups Links
>
>
>
>
>
>
>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
Now with Pop-Up Blocker. Get it for free!
http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/17folB/TM
--------------------------------------------------------------------~->
---------------------------------------------------------------------
Home : http://groups.yahoo.com/group/active-server-pages
---------------------------------------------------------------------
Post : [EMAIL PROTECTED]
Subscribe : [EMAIL PROTECTED]
Unsubscribe: [EMAIL PROTECTED]
---------------------------------------------------------------------
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/active-server-pages/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
