databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Backup and Restore

MySQL Workbench For Database Migration By The Remote DBA Experts

MySQL Workbench can be considered as the new visual tool for the developers, database architects and the database administrators. It can provide with the SQL development and the data modeling processes. Available in Windows, Mac OS X, Linux, it can be used for back up, user administration and the server configuration. The best remote DBA experts provide you with the method of database migration from MS Access using the workbench.

Preparation Process: The migration from MS Access is available from the Windows as it allows the ODBC drivers of the MS access to perform without any obstruction. You can obtain the destination of MySQL server from your system or from the external network. The internal table that stores the foreign key information is called as the MSysRelationships. The table remains protected even from the admin user. So if you try to unlock the table without migrating properly, you may get an error message. Therefore you need to follow some simple steps for the migration of MS Access 2007.

Database Preparation Under MS Access 2007:

  1. You can unlock the database in Access
  2. Select the ‘Database Tools’ then click Macro > Visual Basic for opening the VB console
  3. Then type “? Current User “and press Enter from the ‘Immediate’ panel like this:

    ? Current User

    Admin

    This step will make sure that you are logged in as the admin.
  4. For granting the access, you are required to type CurrentProject.Connection.Execute “GRANT SELECT ON MSysRelationships TO Admin “
  5. You can Quit.

Start the Migration Wizard: You can open the MySQL workbench for starting the Migration Wizard. Select the Database Migration Launcher from the Workbench Central Panel. You can also select simply Database > Migrate from the main menu. After this process, a new tab of the overview page will easily appear in the Migration Wizard.

Operate ODBC Drivers: You may click on the “Open ODBC Administrator” and look at the Drivers Tab for opening the ODBC system tool. This process will ensure that the ODBC drivers are installed in your system. As the MySQL workbench includes 32 bit and 64 bit executable operations, the binaries of the workbench may act same as the architecture of the ODBC drivers.MS Access 2007 can easily be supported with an older 32 bit version of the workbench. If you receive an error message ’Architecture Mismatch between Driver and Application’ during the migration, you need to prevent the installation of wrong workbenches.

Parameters for Source Access Database: Select the ‘Start Migration’ button from the Overview page to obtain the Source Selection of the specific page. Here you are required to provide the information about the relational database you want to migrate.

After that, you can release the Database System box to get all the list of the RDBMS. Select Microsoft Access from the mentioned list and move forward to the Stored Connection. All the saved connections are being listed in this option. You can mark the checkboxes and provide names according to your preferences. The specific Connection Method can also be selected after receiving the entire ODBC data source. If you have configured the DSNs, the method will allow you for viewing them through the DSN drop down list. You can also set the preferred characters in the Default Character Set. If your version allows both Western and Latin characters, you can choose the default as cp1252.

Finally you can select the Test Connection to check whether the ODBC connection have been established properly or not. After a successful migration, you can see the results by opening the SQL editor page.

The Author

Jennifer Anderson is a popular database expert who works with the best remote DBA experts - RemoteDBA.com. She is sharing the process of database migration with the MySQL workbench in this article.