Re: Promt user for criteria ?

On Mon, 23 Jan 2006 06:36:03 -0800, Xavier wrote:

>Hugo
>
>You are correct I mistakenly said faster than a SP but I should has said
>faster that a passthrough query from my front end which is access.
>
>I could not run my report from access unless I add the view to my access
>front ent as a linked table, is there any other way ? for an access report to
>run a sql view and pass a parameter, like a date range to extrack particular
>data example ()now
>minus 48hrs everytime the user runs it.

Hi Xavier,

When using Access, you want to make sure that network traffic is kept at
a minimum. Stored procedures and pass-through queries meet this
requirement without any doubt. I'm less sure about queries on linked
tables.

I have read reports claiming that Access will fetch a complete linked
table over the network to execute the query client-side. I have also
read reports claiming that the former reports are rubbish. My personal
experience with Access is too limited to be able to tell which reports
are true and which aren't. If you want to find out, then run a profiler
trace while running Access and check what Access really sends to the
server.

I would personally use a stored procedure, but that's probably because
I'm more at ease on SQL Server - I know how to squeeze every drop of
performance out of the SP code; I'm not nearrly as proficient in
optimizing Jet SQL.

If your tests indicate that an Access query on an Access linked table
(to a SQL Server table or view) is faster than calling a stored
procedure, then by all means go for it.

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: ADP Help
    ... And the stored procedure would be something like: ... > this, if all the SQL statements are on the SQL Server, how would the SQL ... I use that query as a base ... >>> query for almost all of my reports. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Reaching columns -in C#.net- in queries with multiple tables with the same column name
    ... SQL Server Integration Test Team ... > I see it also in the Query Analyzer. ... So my datagrids and reports and> sourcecode can't find the right column anymore, ...
    (microsoft.public.sqlserver.programming)
  • Re: Reaching columns -in C#.net- in queries with multiple tables with the same column name
    ... SQL Server Integration Test Team ... > I see it also in the Query Analyzer. ... So my datagrids and reports and> sourcecode can't find the right column anymore, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL Server 7.0 8k rowsize limit
    ... reports to run the query. ... Crystal reports also comes back ... We were running it on SQL Server ... > have gotten error messages much earlier. ...
    (microsoft.public.sqlserver.programming)
  • Optimizer question
    ... I'm using MS SQL Server 2000. ... Suppose I have a query like this: ... Best, Hugo ... (Remove _NO_ and _SPAM_ to get my e-mail address) ...
    (microsoft.public.sqlserver.programming)