I am converting an Access db to use a MySQL backend via ODBC. There’s a performance issue I can’t resolve relating to searching records on a datasheet form.

A user enters search criteria into a ‘locate’ field, and as each character is entered a search is performed to match the first number of characters based on the size of the search criteria string. The searched columns are indexed.

The cut-down code is rudimentary. It works fine but is slow.

*************
...
strLoc = strLoc & Chr(intAscii) ‘intAscii – character entered by user.
...

‘Build query string for FindFirst...
rsClone.FindFirst "LEFT(" & strFieldName & ", " & Len(strLoc) & ") = ‘" & strLoc & "’"

If rsClone.NoMatch Then
‘...show NOT FOUND message
Else ‘...Sync record
Screen.ActiveForm!subForm.Form.Bookmark = rsClone.Bookmark
End If

*************

I can’t use the seek function with indexes because I’m dealing with linked tables.

How can I speed things up?
Is there an alternative?

Thanks in advance.