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

Question

I have a single form with 2 sub form , 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

         DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere

before it was 1 second.

   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

  varWhere3 = Replace(varWhere, "*", "%")

      Set qdf = CurrentDb.CreateQueryDef("")

     qdf.Connect = "ODBC; DRIVER=SQL SERVER; SERVER=DESKTOP-JL7MJL4; 

     DATABASE=EDMS_BB2_with_relation - Copy (4)SQL;  Trusted_Connection=YES;"

       qdf.SQL = "SELECT tblDocuments.[Owner Document Number], tblDocuments.
   [Sazeh Document Number], tblDocuments.[Document Title], tblDocuments.
   [Project No], tblDocuments.Originator, tblOriginator.[Originator Des], 
  tblDocuments.Zone, tblDocuments.Unit, tblDocuments.Discipline, 
  tblDiscipline.DiscDesc, tblDocuments.[Document Type], tblDocumentType.
  [TYPE Description], tblDocuments.SheetNumber " & _
 "FROM tblDiscipline RIGHT JOIN ((tblDocumentType RIGHT JOIN tblDocuments ON 
  tblDocumentType.TYPE = tblDocuments.[Document Type]) LEFT JOIN 
  tblOriginator ON tblDocuments.Originator = tblOriginator.Originator) ON 
   tblDiscipline.DiscCode = tblDocuments.Discipline WHERE " & _
   varWhere3 & _
    " ORDER BY tblDocuments.[Owner Document Number]"
      qdf.ReturnsRecords = True
      Set rst2 = qdf.OpenRecordset


If IsFormLoaded("frmDocuments") Then
   DoCmd.OpenForm "frmDocuments", acNormal
    Set Forms!frmDocuments.Recordset = rst2

    Forms!frmDocuments.SetFocus

End If

C Perkins · Accepted Answer · 2017-09-20T00:58:56.143

1

First of all, the code executes the full query just to tell the user whether or not there are records. It is unnecessary to return all records for that. Limit the search query like SELECT TOP 1 ...

For the same reasons, there is no need to return all columns, so only specify a single field like SELECT TOP 1 [Owner Document Number] ...

Even though you have moved to SQL backend, I suppose from the information in the question that you are using linked tables. The Access database engine is not especially intelligent, so it will probably separately execute the subquery then it will search the subquery for every record in the main query... and that subquery's data may not be indexed. It might even be executing the subquery for every separate row in the main query. There are a few things you could do to make this more efficient. Use a JOIN. That is more likely to use indexes to make the query more efficient. Something like

SELECT TOP 1 Docs.[Owner Document Number] 
FROM tblDocuments AS Docs INNER JOIN tblTransmittals AS Tmls
  ON Docs.[Owner Document Number] = Tmls.[Owner Document Number]
WHERE Tmls.[CT- Transmittals] LIKE 'foobar*'

It will be up to you to put it together correctly.

Lastly (and I am actually surprised there are not already a couple comments), you should never insert user data directly into SQL statements. Always use parameterized queries for that. For Access, that means creating a QueryDef with parameters. A temporary QueryDef can be created like db.CreateQueryDef("", SQL)

If these tips do not improve the performance enough, consider sending a pass-through query directly to SQL server for the initial record check. Also consider resetting the Form.RecordSource with the optimized query rather than sending the WHERE condition to DoCmd.OpenForm. Both of those techniques can be researched separately and such details are beyond the scope of this question.

edited Sep 20 '17 at 00:58

answered Sep 20 '17 at 00:28

C Perkins

3,733
4
23
37

record source of my form is a pass-through query that return all the records. in VBA is possible define Form.RecordSource to this pre-defined query and adding a where clouse. – Masoud Sep 20 '17 at 03:23
To clarify, the current Form.RecordSource is set to a _saved_ query (i.e. QueryDef) which is a pass-through query? – C Perkins Sep 20 '17 at 03:42
The answer to [this question](https://stackoverflow.com/questions/38620119/using-a-stored-procedure-as-a-ms-access-form-recordsource) contains a useful example of defining a pass-through QueryDef for a form, although this is not the only way to accomplish this. – C Perkins Sep 20 '17 at 03:45
you mean i have to create, dynamically pass-through query in VBA? i had a defined pass-through query that is record source of my form. i wanted to know can i use name of this query and also adding a where clause to that or i must define this pass through query dynamically like a example you have sent. – Masoud Sep 20 '17 at 05:00
Dynamically defining another pass-through query with the specific where clause is one option, but I am not saying it is the only option. Your current form query might work just fine... I only suggested changing that if the other tips for the in-code query do not work to speed up the searching. Have you tried implementing the tips in my answer? – C Perkins Sep 20 '17 at 05:17
thank you, according to sample i tried to define a pass-through query in VBA (i added to my code in question) , now it seems speed is very good, just i had challenges until i understood i have to replace '*" with '%', is it possible check my added code specially (DoCmd.OpenForm "frmDocuments", acNormal Set Forms!frmDocuments.Recordset = rst2) is this correct or no because i am not professional in VBA writings. – Masoud Sep 20 '17 at 08:21

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

1 Answers1