optimizing DB w/ access front end & MySQL backend
So the day was upon us where our database system(s) needed to upgrade our back end to something that could handle all our data. The decision was made to consolidate our largest and most used databases into one front end and one SQL server for the data. Were early on in the consolidation but my big question is with regards to the most efficient way of pulling the data from the MySQL back end to the access front end.
I currently have one main form with a large number of queries so optimizing this process is crucial.
first off, which index(es) are used, the ones with MySQL or with Access? Does access require its own indexes?
secondly, would a Pass through query be best or would it be better if i created custom views within MySQL and then link to those views in my front end?
Can i create a pass-through query in VBA, currently most of the SQL is VBA generated based on the user selections and actions, which is then queried to a recordset or assigned to a recordsource property, can this same thing be done and then passed through?
running a MySQL back end and connecting via ODBC is new territory for me so I want to make sure i get this right. were early on in this transition and currently many of our forms with built in queries are pretty slow, enough to be frustrating.
iv'e googled for days now and haven't come across a good source for this kind of situation, any suggested reading would be appreciated.