Results 1 to 2 of 2
  1. #1
    masoud_sedighy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Posts
    78

    After migrating to sql my form search is running slowly in Access front-end


    I have a single form with 2 subform , before migrating to sql I could search with below code my form. Record source of my form is a query is running fast.
    Now when I click search button for filtering records in this form is runs very slowly after 3 minutes

    Code:
    DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
    before it was 1 second.

    Code:
    Private Sub cmdSearch_Click()
    Dim varWhere As Variant, varWhere2 As Variant, varDateSearch As Variant
    Dim rst As DAO.Recordset
     
        ' Initialize to Null
        varWhere = Null
        varWhere2 = Null
        varDateSearch = Null
    If Not IsNothing(Me.txtTransmittal_to_Site) Then
            ' .. build the predicate
            ' Must use a subquery here because the value is in a linking table...
            varWhere = (varWhere + " AND ") & _
                "[Owner Document Number] IN (SELECT [Owner Document Number] FROM tblTransmittals " & _
                "WHERE tblTransmittals.[CT- Transmittals] LIKE '" & Me.txtTransmittal_to_Site & "*')"
    End If
    If IsNothing(varWhere) Then
            MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
            Exit Sub
        End If
       
        ' Open a recordset to see if any rows returned with this filter
        Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblDocuments WHERE " & varWhere)
        ' See if found none
        If rst.RecordCount = 0 Then
            MsgBox "No Documents meet your criteria.", vbInformation, gstrAppTitle
            ' Clean up recordset
            rst.Close
            Set rst = Nothing
            Exit Sub
        End If
       
        ' Hide me to fix later focus problems
        Me.Visible = False
        ' Move to last to find out how many
        rst.MoveLast
        If IsFormLoaded("frmDocuments") Then
            ' Note: if form already open, this just applies the filter
            DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
            Forms!frmDocuments.SetFocus
           
        End If

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Test it by removing the 2 subforms, they may be the problem.

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

Similar Threads

  1. after migrating to sql server some forms running slowly
    By masoud_sedighy in forum SQL Server
    Replies: 1
    Last Post: 10-06-2017, 04:06 AM
  2. after migrating to sql server my query is running slow
    By masoud_sedighy in forum SQL Server
    Replies: 4
    Last Post: 09-19-2017, 06:27 AM
  3. Access 2013 Working Slowly
    By kik in forum Access
    Replies: 2
    Last Post: 03-20-2015, 12:04 PM
  4. Help I have a Form that is opening too slowly
    By RayMilhon in forum Forms
    Replies: 2
    Last Post: 12-05-2012, 12:51 PM
  5. Replies: 0
    Last Post: 12-01-2011, 10:23 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