Results 1 to 4 of 4
  1. #1
    Misjel is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Location
    Belgium
    Posts
    9

    Access SQL Server


    Hi,

    How to set a filter on a SQL Server table in Access.
    This works, but takes a very long time.

    If rsPRD.State = 0 Then
    rsPRD.Open "Produktregistraties", cnThisConnect, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    End If
    strFilter = "[TAG] = " & Chr(39) & Tag & Chr(39) & " And Done = False"
    rsPRD.Filter = strFilter


    If Not rsPRD.EOF Then

    This line : rsPRD.Filter = strFilter is the problem.

    The SQL Server is on a Hosting Server.

    Tx

    Regards

    Michel

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You want to let the server do the filtering rather than Access, when you can. I don't use ADO much, but I'd open the recordset on an SQL statement that included a WHERE clause with your filter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is illustrating something different, but note how the recordset is opened using SQL with criteria:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    cross posted on https://www.helpmij.nl/forum/showthr...-Server-ACCESS

    My answer: create a stored procedure or table-valued function on SQL server. I prefer working with stored procedures because they offer more possibilities.

    Example stored proc:

    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        NGA
    -- Create date: 2022-08-13
    -- Description:    Demo
    -- =============================================
    CREATE PROCEDURE p_getDataDemo 
        
        @pTag varchar(50) = '', 
        @pDone bit = 0
    AS
    BEGIN
        
        SET NOCOUNT ON;
    
        
        SELECT * FROM vw_Produktregistraties WHERE [TAG] like '%' + @pTag + '%' and [Done] = @pDone
    
    END
    GO
    In your example you first get all data over the network and then filter it. That is the slow way of doing so. Check your view for indexes that might help. For the query above a (filtered) index on the field [Done] might help. You could execute the query (procedure) in the SSMS with execution plan and then check the execution plan for further query improvements.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-30-2021, 11:53 AM
  2. Replies: 2
    Last Post: 05-19-2020, 11:55 AM
  3. Upgrade from SQL Server 2008 to SQL Server 2014
    By RayMilhon in forum SQL Server
    Replies: 4
    Last Post: 07-12-2017, 04:54 PM
  4. Replies: 1
    Last Post: 02-23-2015, 07:06 AM
  5. Replies: 2
    Last Post: 11-21-2012, 09:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums