Access back to mysql - the long journey

[ahmed_optom]

I thought I would share my access, to split to sql to mysql journey.

I really enjoy developing in Access and VBA, and our database and company have grown to the point where we need many users in different locations accessing. Im experimenting with a mysql server for our expansion, and heres the steps I have taken. I have searched and watched many videos etc, but there doesnt seem to be a "guide" as such on this long path. Im trying to remember every step I have taken, but its taken me about a week of tweaking, reading etc to get to this point.

I hope this helps others, and serves as a guide, Im sure I will have problems and hopefully we can work through them and include the solutions in this thread.

So here are the steps I have taken.

1. ensure database is split
2. install sql express
3. install mysql workbench
4. uninstall access (crazy but trust me, ssma doesnt seem to work without this)
5. install access runtimes.
6. use ssma to export access to sql.
7. create a mysql server, database and user login
8. use mysql workbench to migrate sql to mysql
9. if doesnt work, ensure you have named the mysql database to the same as the sql database name, there is no option to set it.
10. watch for errors, default values in tables is a no no eg get date()
11. re install access.
12. try to relink tables in front end to mysql

Hope this helps others, and Im going to try and keep adding things as I remember.

 

[ahmed_optom]

One of the outstanding issues I have is that I cant seem to find a way of connecting to the mysql without having to install the odbc mysql driver on every pc.

This is a real hassle for deployment, as you not only have to install it, but enter the mysql user name and password. You cant just be handing out such data, so it means you have to do it manually on every pc. Whats worse, is that Im now currently sure how the user/pass is stored and how secure it is.

 

[ahmed_optom]

Also before I did all this, I was weighing up the pros/cons. Now I have done it let me summarise.

pros
-definitely faster. It strangely seems even faster than when front and back are on same pc. My mysql is remote, eg on the internet. I have been testing on a 4g dongle, and its almost as fast as in the office. Its miles better than vpn we tried. I would say it feels twice as fast as a split database over on a lan.
- robust - I have had several corruptions in access due to disconnections, wireless internet and access just dont mix. Mysql seems to handle the whole wireless thing better so far.

cons
-time consuming, its a mega pain to sort out. Its a new kind of pain that makes all previous pains seem irrelevant.
-you will probably have to re write some vba, or forms etc to get all your forms working as before.
-you need to understand mysql as you will probably have to modify the backend as well.

overall, if your running a medium to large database, with users in different locations, then mysql/sql seems to be the best way. I can't get over how fast it is. Its just point and click. When we were running the back end on a fileserver over a gig network, it was significantly slower than this.