Re: SQL queries that have parameters passed by user

Erland,
Thanks for your tip.
I'm researching this method.
This seems to be very powerful and should be callable from the web
also.
I'll get back if I have more questions.

Thanks again.
Brad

Erland Sommarskog <esquel@xxxxxxxxxxxxx> wrote:

>Brad (bmeyynospamnospam@xxxxxxxxxxxx) writes:
>> I have written a view (in MS SQL that appears below) that shows a
>> sample what we need against our 5 million record db. It groups on
>> several fields and filters on a criteria that I would like to be user
>> selectable.
>>
>> The query works fine in MS SQL and seems to be fairly quick, but I
>> don't know how to allow people to select the IDNumber dynamically.
>> (There may be several ID numbers and other fields that will be part of
>> the ultimate criteria.)
>
>Rather than making it a view, you could use a table-valued function
>which takes parameters. If you have more complex criterias, you
>might be better of with a stored procedure.
>
>As for performance, for this particular query you should have a
>clustered or non-clustered index on tblMaster.IDnumber and a
>non-clustered index on (recNumber, HCODE4) in qry_HCODE_LKUP.
>
>> When I use Access, I just create a form and have the query point to
>> the form control and get the criteria from there. I can do that here
>> also and have Access get the data from a linked MS SQL table or view,
>> but once I start trying to group and sort this quantity of data in MS
>> Access (even though it is only linking to the data in SQL) it slows
>> down dramatically and I don't know how to have Access link to this
>> view and dynamically insert the criteria.
>
>Well, I don't know Access, so I have no idea what is going on. Although,
>I can guess there is a server-side cursor somewhere.
>
>If you are to work with a 250-million-rows table to be, you may find
>that a tool like Access that generates a lot of code behind your back
>is not optimal. For smaller databases, it's possibly useful, because
>it gives you an application in small amount of time. With those volumes,
>you need full control over the code, so you can tune performance in every
>aspect of the application. You might still be able to use Access - but
>then you need to understand exactly what happens when you link tables
>in Access etc.
>
>You could use the Profiler to see what is going on with regards to
>SQL Server communication. But with those data sizes, you can get
>problems in Access itself, if gets the idea to get all those
>five million rows to the client side.
>

.

Relevant Pages

  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... "Jeff Boyce" wrote: ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ...
    (microsoft.public.access.formscoding)
  • Re: Adjusting SQL in Query
    ... from the RESPEL table which satisfy the criteria in the PRICELIST Table. ... RESPEL INNER JOIN PRICELIST ON RESPEL.PRICELIST = ... Subject: Adjusting SQL in Query ...
    (microsoft.public.access.queries)
  • Re: Using combo box to search form
    ... Since you "started adding" additional criteria, ... about SQL syntax to know how to form a grammatically-correct SQL statement. ... One way to do that is to see what Access generates when you build a query. ... Dim strWhere As String ...
    (microsoft.public.access.formscoding)
  • Re: Criteria linked to form combo Yes/No or All expression help
    ... The way I'd debug it would be to start w/ one OR clause in the criteria. ... Run the query & see if it works for that criteria. ... I followed your suggestion of pasting the sql, ...
    (microsoft.public.access.queries)