Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    UK
    Posts
    15

    Unanswered: Optimize msAccess / MySQL search

    In my Access runtime applications I often include a ‘locator field’ search box to speed-up searches on tabular forms.

    The user can choose (via radio buttons) which column field to search by and as each character is entered into a search box the record that matches the pattern will become the active record.

    This has worked well up to now.

    I am in the throws of replacing the mdb/mde backend with MySQL. The custom search has now become much slower!

    I have included small snippets of the search code from different event handlers.

    Code:
    code:Private rsClone      As DAO.Recordset
    
     …
    
    ‘strField – field in tabular form to search by.
    Screen.ActiveForm.subForm.Form.OrderBy = strField
        
    'Create clone after datasheet has been sorted...
    Set rsClone = Screen.ActiveForm.subForm.Form.RecordsetClone
    rsClone.Sort = strFiel
    
    …
    
    ‘strLoc - user entered search string.
    If strLoc = "" Then		
       rsClone.MoveFirst
    Else
       rsClone.FindFirst strSearch & " LIKE '" & strLoc & "*'"
    End If
        
    'Sync current record with clone...
    If Not rsClone.NoMatch Then
       Screen.ActiveForm!subForm.Form.Bookmark = rsClone.Bookmark
    Else
       ‘show not found message in status bar.
    End If
    
    …

    How can I optimize it now that the tables are MySQL InnoDB?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have no idea what you're doing with that code

    i just thought i'd point out that the wildcard character for the LIKE expression in mysql is % instead of *
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •