Most of us know about the famous desktop database Microsoft Access (Access). Access works very well with Microsoft NT Server, Internet Information Server and with Majority of Desktop or Network Applications. Most of us are impressed at the speed and ease of use, once it is setup. The nice thing about Access is it is user friendly and fairly easy for non-programmers to maintain. In this White Paper we are going to explain you the process migrating from this famous desktop database to Microsoft SQL Server 2000. We have taken the “Northwind” database as an example which is served as a sample by Microsoft for both Access and SQL Server. Another reason is for the forms that are contained with in the Access database called Access forms. The Northwind database is shipped with Access and can be found in the
Samples subdirectory of the Access installation directory. Alternatively it can be downloaded from Microsoft’s Download Center at
Why do we need to migrate?
Generally the need for migration arises in Access because of the size of the data stored in the MDB file. If the data stored, Increases, then it results in poor performance of the application using this data. Also as organizations tend to grow the need for Stability, Scalability, Security, Capacity and Sharing of the Database is found necessary, which cannot be catered by Access. SQL Sever is a true relational database; while the Jet database is an Indexed Sequential Access Method (ISAM) database - also known as a flat file database. In a professionally set up SQL Server, the users (and their front-end applications) can never write directly to the tables at all. They access the data through views, and modify the data using stored procedures. None of these techniques are available in Access.
How do we migrate?
The following steps are necessary in the migration process of the Northwin.mdb file.
1. Converting the existing Access 97 file to Access 2000 or later versions
2. Running the Upsizing wizard.
3. Migrating from Access forms to windows forms.
In this paper we will concentrate on Step 3, but not before having a glance at Step 1 and Step 2.
Step 1 is optional, the reason we say it is optional because, only if you have an Access 97 file or of earlier version then it needs to be converted into Access 2000 or 2002 or 2003 file format. To proceed to step 2 we need this conversion because the upsizing wizard runs only on the above mentioned versions of Access. Any how if you have Access 97 file then open it in the new version of Access. A message box appears asking you to convert the existing file format to the new file format; Clicking “Yes” button will convert the file to the new format. Another way of converting this file format is to go to the Tools ->Database Utilities -> Convert Database -> To Access XXXX file Format. The following picture 1.1 depicts that.