My colleagues currently use an access database, FE and BE, to browse customer records. I'm looking into using a MySQL backend.
I have set up the MySQL on the server on our LAN, and have transferred some test data. I'm now accessing the data from a client PC, in Access, through DSN, using MySQL ODBC 5.2 Unicode Driver.
The setup seems to be working well except when using Access' search (ctrl + F) on a linked table. It takes an unacceptably long time to find data, especially as the searched field is indexed.
I am assuming this is due to the fact that the access will pull all the records locally in order to search through in this manner. This seems to be the case looking at the network performance in task manager.
By contrast, filtering data is quite speedy.
Unfortunately, my users DO search the database in this way, so I'm very much looking for a way to improve this find tool. Otherwise I'm gonna have to build a custom search tool into every form. Does anyone have any suggestions?
I have set up the MySQL on the server on our LAN, and have transferred some test data. I'm now accessing the data from a client PC, in Access, through DSN, using MySQL ODBC 5.2 Unicode Driver.
The setup seems to be working well except when using Access' search (ctrl + F) on a linked table. It takes an unacceptably long time to find data, especially as the searched field is indexed.
I am assuming this is due to the fact that the access will pull all the records locally in order to search through in this manner. This seems to be the case looking at the network performance in task manager.
By contrast, filtering data is quite speedy.
Unfortunately, my users DO search the database in this way, so I'm very much looking for a way to improve this find tool. Otherwise I'm gonna have to build a custom search tool into every form. Does anyone have any suggestions?
