Access front-end and MySQL and Access(Jet) back-ends
Posted on 2012-04-08
System is outgrowing Access's db limitations – 34 back end databases @ 20Gb, and with single tables approaching 1.8Gb it’s time to change! Also more interested in the increased security, stability and multi-user capabilities. Hoping to get performance improvements too as some queries are taking a fair while to process.
I still intend to use Access as a front end, and migrate the back-ends over to MySQL – probably in stages, and starting with an important main 1.8Gb table/db. I appreciate this is a well-asked about area, but I have a few questions which I can’t seem to understand properly and would appreciate answers and any general discussion/advice. I know how to export the Access/JET dbs to MySQL in general.
1. Is using MySQL with Access as simple as linking the MySQL dbs through the External Data->Import->ODBC instead of linking to the Access dbs? (I doubt it!)
2. At the moment (almost)all data editing etc in VBA is done through DAO:
Dim dbs as DAO.Database
Dim rst as DAO.Recordset
Set dbs = CurrentDB
Set rst = dbs.OpenRecordset(x)
Will this be a problem//still work?
3. Various queries and VBA functions on datasets have functions which are Access-specific (CInt, etc). Will these still work or will the MySQL equivalents have to be used?
4. As mentioned, currently spread across 34 Access/JET back ends for space and organisational reasons. Should the data be kept in a single MySQL db/multiple/doesn’t matter?
5. Will there be any problem working an Access front end with backends in both Access/Jet and MySQL, considering queries will be using tables from both
I’m sure there are more / any general advice appreciated!
Thanks in advance.