want to use Mysql Database on the Access system as a beckend instead of the Access Database

I have access system and that is using the access data base as backend . Now i want to connect the database with MySQL Server database . Is it possible and how much i have to be do changes  queries and some others . Please share with  me your experience or point of view  regarding to this matter.
there is also aready many Access queries running on the Access Database and how i can handle these access database queries in MySQL Databse
kausar aliweb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nociSoftware EngineerCommented:
I guess as both have a relational model data is transferable, possibly with some choices about dataranges... (smallints -> ints or the like).
the ACCESS API is quite different from MySQL. So after transfer of data including redesign issues for indexing etc.

All applications will need to be converted to use the Mysql API.   How easy that is i have no idea... very dependant on queries involved, languages, availibility of API libraries.
ODBC might help, but will also cost some performance as intermediate and the extra copies needed.
Gustav BrockCIOCommented:
You will use ODBC to connect:

Download Connector/ODBC

The page has links to various documentation. Also, search for MyODBC, and you'll find bunches of code examples and guides.
PatHartmanCommented:
In theory, the vast majority of things will just work after you delete the links to the ACE tables and replace them with links to MySQL.  However, in practice, you will most likely need to modify your DAO code to add the dbSeeChanges argument whenever any table in the query uses an autonumber (identity) column

Set rsLtr = tdLtr.OpenRecordset(dbOpenDynaset, dbSeeChanges)

Later you can consider changing the DAO to ADO since DAO is optimized for ACE.

Make sure that ALL your tables have a primary key.  If you don't have a natural PK, use an autonumber.  If the table does not have a pk, Access will not allow you to update it and any query you use it in will also be not updateable.

Make sure your indexes are optimal.  Access automatically defines indexes on a  FK fields.  You will need to do this manually in MySQL.

The biggest early change would be to how the forms work.  If you have used common Access techniques that bind forms to tables or queries with no where clauses and you then use filtering to get to the record the user wants, you will see much SLOWER response if the tables are anything more than trivial in size.  The whole point of using a RDBMS is to minimize network traffic and make data access more efficient.  That means that you do not want Access acting like a straw to suck down every row of every table in the BE.  Rather than filters, you would use queries with criteria.  If the selection criteria is complex, you will need to build a search form to do this.  The search form will build the SQL String or sometimes just the WHERE clause and the form will use that as its RecordSource.  Here's a picture of one of my search forms.
search.PNG
Once you have done those things, if you are experiencing slowness, try building some views of commonly joined tables.  This helps to optimize the query because some of it will be pre-built on the server.  Some update actions, especially bulk deletes will be much faster as pass through queries.  The final fix is to create stored procedures.  I use them on rare occasions for especially complex reports.  Pass-through queries and sp are not updateable so they are rarely used for forms.
nociSoftware EngineerCommented:
On MySQL indexes don't over doe it.
If there is a index on field1, field2, field3...
then you dont need an extra index for just field1... (SQL engines will use the index on 3 fields for that. because the first  is the same.).
PatHartmanCommented:
Didn't I provide an answer?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.