Re: SQL Server - Filter

Steve,

Yes Rich missd my point actually!

> Sure, but that doesn't mean you have to turn the whole app inside out. If
> adjusting the Access query doesn't give the same benefits you're
> describing, then perhaps a stored procedure should be used in that
> specific case.

Happy to use a stored procedure but prefer to use a View that can be linked
to so that it looks like a table to Access - at least that is what I think I
want to do - still climbing that learning curve!


Bob Collinson

"Steve Jorgensen" <nospam@xxxxxxxxxxxxx> wrote in message
news:V9-dnVvJ_cdtTF7eRVn-hw@xxxxxxxxxxxxxx
> Rich P wrote:
>> Hi Bob,
>>
>> If you are connecting to sql server via ODBC and running queries agains
>> the sql server tables from access you are pretty much defeating the idea
>> of using a sql server. The idea is for the sql server to do the work.
>
> Now, your code example below looks like a very nice implementation for
> certain kinds of application, but statements like the one above get me
> really pissed off. I've have had to support the worst kinds of
> unnecessarily complex tangled messes of code that users have created
> because they read some false statement like this one on some forum, when
> their app would have been vastly easier to implement and maintain and had
> many fewer bugs if they'd just used Access queries for the most part.
>
> The truth is that in most cases, Access works very well querying data
> using Access queries against links to server tables, and many of the
> features that make Access such a convenient platform for developing
> database applications are thwarted by shemes to use only stored procedures
> to query data from the back-end.
>
> Specifically - it is NOT true that Access must read entire back-end tables
> to the client to perform Access queries. Access generally forwards query
> logic to the back-end for processing, even when joining 2 or more tables,
> so long as they are linked to the same ODBC source.
>
> Of the cases where Access cannot run efficient queries against server
> tables using an Access query, 90% of those are due to problems that can be
> fixed by adjusting the query to eliminate problems like including a VBA
> function in a GROUP BY field.
>
>> You run the queries (stored procedures) in sql server and pull down the
>> result set to Access. The catch is that you don't use ODBC. Use ADO. I
>> have seen people at my place run queries against the sql server tables
>> from Access - 45 minutes later they are still waiting for a result set.
>> Eventually, I wrote them a stored procedure for the same query in sql
>> server. 45 minutes turned into 450 miliseconds. No kidding!
>
> Sure, but that doesn't mean you have to turn the whole app inside out. If
> adjusting the Access query doesn't give the same benefits you're
> describing, then perhaps a stored procedure should be used in that
> specific case.
>
>> Here is a sample how you run a stored procedure in sql server from
>> access and pull down the result set - this example assumes you are
>> pulling data for a specified date range, so you have date parameters for
>> start date and end date -- Note: you need to make a reference in
>> Tools/Reference/Microsoft ActiveX Data Objects 2.6 Library or higher.
>> You get this from MDAC2.6. Note also, you need to have MDAC2.5 loaded
>> because MDAC2.5 was the last MDAC to include Jet4.0. Get this from MDSN
>> or google it.
>>
>> Sub GetResultSetFromSqlSvr()
>> Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
>> Dim RSdao As DAO.Recordset, j As Integer, Retval As variant
>>
>> cmd.ActiveConnection = "Provider=SQLOLEDB;" _
>> & "Data Source=yourServer;" _
>> & "Initial Catalog=yourSqlServerDB;UID=sa;PWD=;"
>> cmd.CommandTimeout = 600
>> cmd.CommandType = adCmdStoredProc
>> cmd.CommandText = "stp_YourStoredProc"
>> cmd.Parameters("@bDate").Value = sDate
>> cmd.Parameters("@eDate").Value = eDate
>> Set RSado = cmd.Execute
>> Set RSdao = CurrentDb.OpenRecordset("yourAccessTable")
>> DoEvents
>> Do While Not RSado.EOF
>> RSdao.AddNew
>> For i = 0 To RSado.Fields.Count - 1
>> RSdao(i) = RSado(i)
>> Next
>> RSdao.Update
>> RSado.MoveNext
>> j = j + 1
>> RetVal = SysCmd(acSysCmdSetStatus, j)
>> Loop
>> RSado.Close
>> End Sub
>>
>>
>> Rich
>>
>> *** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages

  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)