Optimize msAccess / MySQL search

Odders · #1 (**permalink**) 04-13-04, 04:16

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?

r937 · #2 (**permalink**) 04-13-04, 05:31

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 *

Internet Services

Web Development

Digital Marketing

Consumer Tech