Migrating an MS Access Database to SQL Anywhere with Migration Wizard - On using the existing front end in MS Access
(Page 6 of 6 )
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.
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.
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.
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
b. Customer Table property of Linked table
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
