Migrating an MS Access Database to SQL Anywhere with Migration Wizard - The SQL Anywhere Migration Wizard, continued
(Page 5 of 6 )
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Next: On using the existing front end in MS Access >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
