<

Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

DATABASE MIGRATION FROM MS ACCESS TO MYSQL PROJECT

Published on
10,088 Points
4,088 Views
Last Modified:
Approved
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.


Scope Definition
      
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 …

My-SQL features

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

Installation Process
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.

ODBC Connector
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.

Arrange Files
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).

tables2tables
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.

tab
B. Open and Run the Tables Exporter

im67891011121314
5. Trouble during the Table Export ¿ Need to assign a Primary key in this table.

1516

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;

Open in new window


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

17
18
8. View all the Exported tables from the MySQL Manager.

19
Finally Test the Application.

20
21

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.
0
Comment
Author:suvmitra
0 Comments

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Join & Write a Comment

Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month