MS Access 2003 or later To MySQL Migration Project
Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I find some time to write down my project experience on the same, thought some of you may get some help out of it!
Background Information/Available Alternatives
The Database Application is built and developed in MS Access 2003, it is further splitted in Front-End and Back-End to perform in Multi-User environment. However, due to more concurrent users, slow network, increasing data etc. the existing architecture is resulting poor performance. To increase the backend stability, application performance and considering zero cost MySQL Server (http://dev.mysql.com/
) is being opted.
Business Goals and Objectives
The business goals and objectives for this project will focus on implementing MySQL backend that:
• A faster Database.
• Zero Cost.
• Open Source.
• Scalability (No more 2 GB Limitation).
• Heavy Duty Multiple User Capability.
• Portability for the Back-End Data (PC, Mac etc.)
• Easy Installation.
• Easy Maintenance.
• Provides high levels of data security.
• Provides an open, flexible, reliable technology base for the future.
• Easy to Use.
The Project will introduce new faster backend storing and querying technology; including the following:
• A Relational Database System
• Faster, More Reliable, Zero Cost
• Able to handle enough Concurrent Users
• Client / Server Architecture
• SQL Compatible, Stored Procedures Supported
• Platform Independence (Any OS) … more …
MySQL Server Hardware and System Requirement
A recommended system would be MS Windows XP / 7, 2 GB + RAM, 100 mbps LAN connection, Min 10 GB of Disk Space.
Applications or Softwares to Download and Install:
• .NET FrameWork 4 : Download .NET Framework 4
• MySQL Installer for Windows (Latest) : Download MySQL Installer
• Bullzip Access to MySQL (Latest) :
Download Bullzip Converter
First Install .Net Framework 4 and Restart the system. After that run MySQL Installer to Install and run MySQL Server. With the latest version we will have MySQL ODBC Driver 5.1 and higher.
Points to note during installation:
• Your firewall will have to be set to allow connections through Port 3306.
• We chose to run MySQL as a service (command line is an option).
• MySQL installation defaults to username/login = root and server = localhost.
• MySQL offers a choice of storage engine/table type – the main ones are MySAM or InnoDb. The latter seems more like Access, with transactions and foreign keys, so at the moment we’re leaning that way.
• User and security options are available. Don’t forget to record any passwords.
We also installed Bullzip Access to MySQL and Dreamcoder for later use.
Migrating an MS Access 2003 Database to MySQL
Either use Bullzip to transfer back-end tables to MySQL, or open your database and export the table via ODBC.
Points to note:
• MySQL does not recognise Access functions used as field defaults (eg Now() and Date() in Date/Time fields): they may be dropped or the table rejected. Recommend you remove them before transfer/export.
• Access autonumber fields are not identical to MySQL auto_increment. Bullzip will convert on transfer, ODBC will not.
• Access data types will be converted to similar MySQL data types. However, there is a greater range in MySQL (refer to the Manual) and you may wish to change them later.
• An Access ‘ole object’ becomes a ‘blob’ in MySQL. Due to an OLE Server problem, we changed blobs to longtext (memo in Access).
• Consider creating Primary key field with Autonumber before migration.
Linking Database FE to BE
Open your Access front-end. If you are already linked to an Access backend, you can’t use the linked table manager to change to an ODBC data source. So delete the links to the Access backend and link, via ODBC, to the MySQL back-end. Similarly, once linked to an ODBC data source, the linked table manager offers only a choice of ODBC sources.
When setting up the ODBC data source, in ODBC Configure – Advanced – Flags1, tick Return Matching Rows and Allow Big Results.
Database Application Performance
Observations so far:
• Access 2003 and later with a MySQL backend was significantly faster than Access 2003 and later with the same backend.
Our next step is to test the effect on performance of using Access pass through queries and MySQL queries (views).
Step By Step Migration Process
1. Arrange all the files: Create or use a folder where you have full access (Read, Write) and copy the complete database application there.
2. Check whether you are using the complete version of the Application.
3. Open the database application file in Design Mode and see if it contains all its objects such as Forms, Queries, Reports, Macros, Modules and of course Tables (Not Linked Tables).
4. Format and Export the Tables into the MySQL Server using BullZip’s Ms Access to MySQL Application.
A. Tables should have primary key or auto number field.
B. Open and Run the Tables Exporter
5. Trouble during the Table Export ¿ Need to assign a Primary key in this table.
SQL statement for altering a Table in the MySQL Database:
ALTER TABLE t_tmpactual ADD column t_ID bigint not null auto_increment primary key first;
6. Create a Database Connection using ODBC.
A. Administrative Tools ¿ Data Sources (ODBC) ¿ Create a New File DSN or configure the Existing One say RCP_Test_Conn.dsn
8. View all the Exported tables from the MySQL Manager.
Finally Test the Application.
Now, you have a working ms access front end file to distribute which is linked with MySQL Server.
To deploy this architecture to all of your client in the intranet / internet you need to host the mySQL to a server. You can talk to your IT / Support to have windows server 2008 with latest service pack for intranet / internet.