What factors should be considered when migrating from Access to SQL Server?

Access 2000 and 2002 (the version in Office XP) provide an Upsizing Wizard to convert an Access database into a SQL Server database. This can be helpful, but there are several important points to be considered before simply upsizing the database "as is".

The most critical analysis lies in the data structure itself. There are many differences between SQL Server and Access in features and functionality, and consideration of these differences may indicate that changes in database structure are needed to take advantage of them.

Code in Access forms and modules which manipulates data can be converted into SQL Server stored procedures. Code in Access form data events (such as Insert, Delete, and Before/After Update) which manipulates or validates data can be converted into SQL Server triggers. Doing so minimizes client-side data processing, which helps eliminate unnecessary network traffic and server round-trips. This increases the speed and efficiency of the application. An additional benefit is the ability of the database to support multiple interfaces without unnecessary duplication or rewriting of code.

Once the database design is finished, Data Transformation Services (DTS) can be used to easily migrate data from the Access database to SQL Server. Using DTS packages has several advantages. Packages can be reused, which is important because data migration usually involves more than one attempt. DTS can also perform modifications to data between the source and destination, using a simple built-in scripting interface or languages such as VBScript or JavaScript, which can ensure that the data conforms to predefined rules as it is migrated.

Access can still be used as a user interface. Access Data Projects (ADPs) are special Access applications designed to connect to a SQL Server database. Forms, reports, macros, and modules behave essentially the same as in a standard Access database, although forms and reports have some differences in data-related properties.

Need help with that Access or SQL database project? Give us a call at 630-513-1280.