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
there is also aready many Access queries running on the Access Database and how i can handle these access database queries in MySQL Databse
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.
Download Connector/ODBC
The page has links to various documentation. Also, search for MyODBC, and you'll find bunches of code examples and guides.
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(dbOpen
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.
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.
Set rsLtr = tdLtr.OpenRecordset(dbOpen
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.
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.
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.).
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.).
Didn't I provide an answer?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
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.