Migrating an MS Access Database to SQL Anywhere with Migration Wizard

This tutorial describes in detail the use of the Sybase specific Migration Wizard to migrate an MS Access Database to SQL Anywhere. SQL Anywhere commands an important position in mobile linkages because of its excellent synchronization, and in this context, moving to this platform may have some advantages.

Introduction

This is the fourth tutorial I’ve written in a series on import/export, or migration of databases, both for Microsoft and non-Microsoft products. The reader will be profited by reviewing these tutorials. The reasons why one might want to migrate have already been discussed in the earlier tutorials.

In importing into MS Access from SQL 2000 Server, a detailed step by step process to bring in data from SQL Server to MS Access using ODBC connectivity was described. In MS Access migration to SQL 2000 server, details of how data is migrated to SQL Server while keeping the front-end functional in MS Access were discussed. In the third tutorial a migration from / to Microsoft and non-Microsoft databases was described.

In all three tutorials either Microsoft’s DTS tool or MS Access’ native Link Tables to get external data were used. In the linked case, ODBC connectivity was used to connect to the database. The tables in SQL Server remained in their original location, but that data was accessible from the front-end. The present tutorial uses Sybase’s Migration Wizard to migrate the database from MS Access to the SQL Anywhere database. The tutorial describes in detail how this is accomplished. Since the migration involves different types of databases, the tutorial also reviews how the data is migrated.

{mospagebreak title=Summary of the Migration Procedure}

A remote server is created in SQL Anywhere which migrates the data from an external database such as MS Access using an ODBC Connection. The Migration wizard sets up the infrastructure needed to bring this data to a database on a connected SQL Anywhere Server. This is schematically shown in Figure 1 below.

Fig1

Migrating from MS Access 

The sample database that will be migrated to SQL Anywhere from MS Access is a subset of the tables from the Northwind imported into a MS Access database, Migration2.mdb. This is shown in Figure 2.

Fig.2

There exists a set of relationships between the tables, as shown in Figure 3, and there are front-end objects created, such as queries, forms, reports, and so forth, which are not shown. These tables will be migrated to Sybase’s SQL Anywhere database.

Fig.3

Migrating to SQL Anywhere 

Using command line arguments one can create a database in SQL Anywhere. The database mysorian.db was created by the command line executable dbinit mysorian.db which was given while in the WIN32 directory where Sybase is installed. The detailed description of creating a database that can be used in a web services server has been described earlier.

{mospagebreak title=Creating a SQL Anywhere Server in Sybase Central}

One could connect to the Sybase Central user interface to connect to this database by invoking the following command with the specified switches: dbeng9 -xs http (port=8082) mysorian.db. The mysorian server will respond to HTTP requests at Port 8082 instead of 80, which is being used by IIS. When created new, the database mysorian.db is empty except for system tables, as shown in Figure 4. Make sure there is no space between http and the open parenthesis (.

Fig.4

Connecting to External Data through an ODBC Connection

We shall create a connection (a system dsn) that assists in the migration of data from an MS Access database.  This is a System DSN created using the ODBC Data Source Administrator as shown in Figure 5.

Fig.5

The data comes from the MS Access database Migration2.mdb. Therefore the driver we choose is the Microsoft Access Driver, and the database we connect to is Migration2.mdb as shown Figure 6.

Fig.6

When this is configured as above, the DSN MigToAsa is added to the list of System DSNs in the ODBC Data Source Administrator’s panel, as shown Figure 7.

Fig.7.

{mospagebreak title=The SQL Anywhere Migration Wizard}

The SQL Anywhere Migration Wizard can be accessed through the Sybase Central management console as shown in Figure 8. It is of course necessary to start the Sybase Central program. Refer to earlier tutorials for starting Sybase Central. It is assumed that Sybase Central is connected to the mysorian database. If not, you can connect to the database from the menu as discussed in previous tutorials. You may be asked to supply credentials. 

SQL Anywhere Migration wizard

When you click on Migrate Database…, the Migrate a Database window shown in Figure 9 pops up. Make sure you read the instructions in this window.  You can migrate the schema, or schema and data from an external database through the intermediary of a remote server. The remote server may obtain its data from different kinds of databases, such as DB2, Oracle, MSSQL, and so on. You click Next> on this screen.

Fig.9.

The external database will be migrated to an existing SQL Anywhere database. Since mysorian.db is defined as a SQL Anywhere database, it shows itself as an available connected database, as you can see in Figure 10. The name of the server as well as the database happens to be mysorian. Click on the Next> button.

Fig.10.

This brings you to the screen shown in Figure 11, where you are asked to create a remote server. You could create a new remote server, or choose from an existing remote server. Click on the Create Remote Server Now… button. 

Fig.11.

This opens up the next screen, shown in Figure 12. If you create a new one, you may give a name to it. In this case it is called Access Server. Remote servers give access to data located on other databases, such as MSSQL, Oracle, DB2, and so on. Click on the Next> button now.

Fig.12.

{mospagebreak title=The SQL Anywhere Migration Wizard, continued}

The Migration wizard can bring in data from a variety of data sources listed in this window. Since MS Access is not listed, the Generic type is chosen when you click Next> to the screen shown in Figure 13.

Fig.13.

When you click on Next> after highlighting Generic, you will be asked to supply the connection information. We will be using the ODBC source we configured earlier [MigToAsa] that connects to an MS Access database, Migration2.mdb as shown in Figure 14.

Fig.14.

Clicking on Next> brings us to the following screen, shown in Figure 15. You can make it read-only for this remote server by checking the checkbox. Here the default is taken, which is not checked.

Fig.15.

Click on the button Next> to bring you to the screen in Figure 16. In this final screen of Create a New Remote Server, in accessing the external database, you are connected as DBA. The naming of the DBA is specific to SQL Anywhere. In Access is called ADMIN. If needed you may create an external login.

Fig.16.

In this tutorial, no external login is created. If you text the connection now, you should get a message indicating that the connection was successful, as shown in Figure 17. You click Finish to reveal the next screen shown in Figure 18.

Fig17 & Fig.18 overlapped

When you click OK to the above, you establish the connection to the server from which you want to migrate, as shown below in Figure 19.

Fig.19.

When you click Next> you will be shown the tables that you may migrate, as shown in Figure 20. You can pick and choose what you want to migrate. Here all tables will be migrated, since we want to separate the data from the front end. The data will be on SQL Anywhere and the front-end continues to remain on MS Access.

Fig.20.

At this point you could create other users in addition to the DBA who started this process.  To keep the discussion limited, the DBA is the only one who owns the migrated tables. It is possible to change/modify ownership later. Click on the Next> button now to reveal the screen shown in Figure 21.

Fig.21.

By default only the schema will be migrated, but you may choose to migrate the information regarding foreign keys as well as migrate the data by making appropriate selections in the check boxes as shown. The migration also involves creation of proxy tables (with the suffix _et in the table node) that refer to the tables in the remote database. You may delete these tables by making the appropriate selection. Proxy tables are used to track the success/failure of migration. When you click Finish you will go to the next screen, shown in Figure 22.

Fig.22.

When you click on the Finish button you will have migrated all the data, schema and foreign keys to the SQL Anywhere database, as seen in the expanded node of mysorian.db’s table node, shown in Figure 23. Notice that for each table in MS Access, two tables are created in SQL Anywhere, one of them with _et, being the proxy table.

Fig.23.

{mospagebreak title=On using the existing front end in MS Access}

It is necessary to delete the original tables in MS Access and link to the tables that were migrated to SQL Anywhere, which now resides in mysorian.db. Delete the MS Access tables. Now you need to create an ODBC connection to SQL Anywhere Server’s mysorian.db. This can be done using the ODBC manager in the Windows system. This can be accessed from Start->Control Panel->Administrative Tools->Data Bases (ODBC) shortcut which opens the ODBC Data Source Administrator.  Herein a File Data Source called LinkbackToAccess is created, as shown in Figure 24.

Fig.24.

When you use this file source to link the tables on SQL Anywhere you will see the following Link Tables window, shown in Figure 25, where there is an option to link back any or all of the chosen tables.

Fig.25.

Here the five tables that were migrated to SQL Anywhere are linked to the Access application. However, they are all appended with the DBA, and after linking you may rename them to the original names as shown in Figure 26. This is necessary if you want to use the queries, forms, and so forth on the Access Application.

Fig.26.

Comparison of table schemas

MS Access and SQL Anywhere will in general have different data types, and they will differ in other features as well. In this case, MS Access data types were all “Text” and they all migrated as “Text.” File sizes increased and there is less chance of truncation. Quite a few items, such as captions, validation rules, and so on, did not migrate. The table marked a shown in Fig. 27 refers to the table before migration and b refers to the properties of the linked table shown in Figure 28.

a. Customer Table property before migration

 

Fig.27.

b. Customer Table property of Linked table

Fig.28.

Summary

The Migration Wizard is a convenient tool that migrates not only data, but schema and foreign keys as well. Since it is a pure database product, application items like forms and reports will not get migrated. However, by renaming the linked tables to their original names the forms and reports can be used. The data types in SQL Anywhere are different from those of MS Access, and this needs to be reconciled. MS Access saves (persists) queries in the application, and these serve as data sources for forms, reports, and so forth.  SQL Anywhere saves (persists) such information in the form of stored procedures.

Security models are also different. In SQL Anywhere user information resides in the database, which is more secure than the system.mda files. While the example in the tutorial migrated a couple of tables, in practice a lot more is involved in migration; that includes the before and after migration tasks that need attention, in addition to the Migration proper of data and schema.

3 thoughts on “Migrating an MS Access Database to SQL Anywhere with Migration Wizard

  1. Growth requires migration as it is the key requirement as well as the main driver. SQL Anywhere’s Migration wizard does an excellent job of the migration process. The example treated in this tutorial is simple, rather trivial, but steps through the required stages.

    I invite your comments, remarks, questions and suggestions.

    Thank you for reading the article.

    sincerely,

    Jay

  2. Hi Jay,

    i followed your article and was able to migrate the tables to the sqlanywhere DB. But i could not get the relationships migrated. On selecting migrate with foreign keys option, the migration utility threw error.
    Is there a way to migrate the relationship between the tables ?
    If i want to replicate the relations in the sql anywhere DB manually, then what is the way for that ?
    Plz suggest.

[gp-comments width="770" linklove="off" ]