DevCity.NET - http://devcity.net
MS Access to .NET Migration (Unedited)
http://devcity.net/Articles/268/1/article.aspx
Byapti Albert
1.MBA with specialization in Systems & Marketing. 2.Handled many IT related projects.  
by Byapti Albert
Published on 9/5/2006
 

 Please Note that this article is published in its original  form,  unedited by DevCity.Net editorial staff.     

-----------------------------------------------------------------------------

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.


Page 1
  Please Note that this article is published in its original submitted form,  unedited by DevCity.Net editorial staff.      

          -----------------------------------------------------------------------------------------                 

Access to Dot net Migration

Mindfire Solutions (www.mindfiresolutions.com

Table of Contents

 Introduction

 

Why do we need to migrate?

 

How do we migrate?

 

Steps to migrate

 

Architecture for migration

 

Pictorial comparison

 

Conclusion

                                                           

 

Page 2

Introduction

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

http://www.microsoft.com/downloads/

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

(optional).

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.

 

Page 3

Step 2 starts with the converted file from Step 1. To start Step 2 go to the Tools - >Database Utilities -> Convert Database and click on the Upsizing Wizard menu item.

                          Picture 1.2 Upsizing Wizard

Whenever we try to upsize an .mdb file to SQL server it leaves behind an .adp file  and the .mdb file. This .adp file known as Access Data Project contains Access forms which connect to migrated SQL server database through the link tables left in the .mdb file. Step 2 leaves us with two options,

a. If you want you can retain this .mdb and .adp file and still work with the same Access forms.

or

b. Migrate from the native Access forms to windows forms thus eliminating the need of Access database from the picture.

There are many RAD (Rapid Application Development) tools available in the market.We have chosen Microsoft Visual Studio.Net. Microsoft Access developers generally consider a move to a .NET front-end for performance, extensibility, security and stability reasons. This process is known as

Application conversion and developers will find a number of key differences while migrating from Access to the .NET environment. It is crucial that these differences are noted and appropriate action is  taken to ensure a seamless and incident-free migration from Access to .NET Windows Forms.

Step 3 is the process of converting these Access forms to Dot net forms and seamlessly integrating the converted forms to the upsized SQL Server database because windows forms (forms) are not directly bound to data and database updates cannot be done automatically. In Access, when a change is made to a data bound form field, the field in the database it is bound to is automatically updated. In Dot net, because your forms are bound to a local in-memory copy of the data and not the actual database, you must add a Save button to your form which, when clicked, will perform the database update but Dot net also enjoys the power of controlling the database connections and .NET offers a host of new form controls to ease data entry and organize your forms.

We have dealt Step 3 by sub classifying it into the following steps.

1.Move data access and ADO code to ADO.NET and a Data Layer.

2.Utilizing new Dot net controls.

3.Recreating Access forms in Dot net in the Presentation Layer.

4.Moving logic from Northwind forms to a business logic layer in Dot net.

 

                                 Picture 1.3 Classification of the Step 3 into Layers

 

The business logic layer is the place where the data manipulation is carried out according to the organization rules and regulations. Generally this data needs no change from the source. For example Calculation of Tax for certain organization is different from others. In that case, the particular method used in Access is left unchanged (of course change of syntaxes according to language needs to be done).

You can download the sample project named

Access_Dotnet_SQL.zip at \\Lanserver\NorthWindDotNet . In this sample we have migrated from the following Access forms to Dot net forms by the above specified layered architecture.

  • Main Switchboard form.
  • Orders form.
  • Orders Sub form.
  • Products form.

Access_Dotnet_SQL.zip file contains a file named WindowsUI.exe.config where you need to key in your SQL server settings. You can also find the NorthwindSQL file with in Backup Database Directory, this file is the upsized version of the Ms- Access Northwind. All you need to do is to restore that NorthwindSQL file on to your SQL server and make according changes in the WindowsUI.exe.config file. WindowsUI.exe.config file contains the Configuration settings to run the application. Open this file with in notepad and replace all the “XXXXXX” with necessary information and save it back. Now you can run the application named WindowsUI.exe

The next pages of this paper contains the pictorial comparison of Access and Dot net forms with in the sample application.

Note: No Reporting services are added to the sample Application.

                                

                                             picture 1.6 Orders (Access)

 

Page 4

 

Conclusion

.NET is the latest software development technology from Microsoft. As it has been built from the ground up, Access developers will notice several major differences between VBA and .NET. Access developers should be aware of differences in application design, form design and the programming model. Despite these differences, you will gain many benefits by migrating your Access applications to .NET; you will be moving your applications to a powerful and scalable industry standard object-oriented architecture.

Prepared By

KOLICHINA SIVA SHANKAR

www.mindfiresolutions.com