I'm running Access 97 as a front end to a MySQL ODBC linked table. Everything is working fine with the exception of "long searches" from within Access. In the past we would be able to go to any one of the fields from within the Form, and then do a Ctrl + F to find a record by whatever value we chose. Results would take about a second when they were native Access table searches. Since converting them it takes about 40 seconds or more..
I've indexed the fields, and optimized the tables, still long searches. All ODBC settings in the Control panel appear to be correct, ONLY Don't Optimize column width and Return Matching Rows are checked.
Any Ideas? (Table is an 8 meg table with about 5 thousand records in it)
Don't use Access' Ctrl + F. It's daft and very slow.
Personally I use ADODB for just about everything.
Put a search button, and run the search in code.
In this particular case, though, you could get away with putting a text box above a sub-form. Let the user enter what they're searching for in the text box.
In the after_update() event, put some code which switches the recordset underneath the sub-form, eg:
me.name_of_sub_form.form.recordset = "select * from my_table where my_field like '*" & me.my_textbox & "*'"
For example if the person enters 'bank' in the textbox, the recordset for the sub-form will look something like:
select * from clients where category like '*bank*'
Or something like that. It's an order of magnitude faster than Access' find methods / CTRL + F.
Hope this helps :)
-- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
