Migrating from Microsoft Access to Microsoft SQL Server

Microsoft Access is a popular desktop database management system that incorporates application development tools (Forms, Reports, Queries, VBA macros) with a relational DBMS. MS Access is ideal for individual users or for small workgroups where a few users work on the data at the same time by sharing an MS Access database file over a local area network.
With that in mind, if you’re an IT Administrator in a small to medium size company looking at best practices for migrating company data from a simple platform to a more robust, scalable one, then this article is a must read for you.
The limitations of MS Access begin to show when the number of simultaneous users grows above a handful. MS Access also has a database size limit of 2GB which can also be problematic to a growing company.
The transition from MS Access to MS SQL Server can be a painful one and something that’s best done gradually.
Through my years of experience, I found that there are four options to ease the transition:
1. Split the MS Access database, keeping Forms, Macros and Queries in one MS Access database and put the tables in another MS Access database. This approach allows multiple users to have access to the same data.
2. Upsize all database objects from an Access database to an Access project so that you can create a client/server application. This tactic requires some additional application changes and modifications to VBA code and complex queries.
3. Upsize only data or data definitions from an Access database to a SQL Server database.
4. Create an Access database front-end to a SQL Server database back-end so that you can create a front-end/back-end application. This approach requires very little application modification since the code is still using the Access database engine (ACE).
Of the 4 options listed above, I have found that option #4 is the best since it integrates the power of SQL Server with the flexibility of MS Access forms, VBA and Macros. This technique is the quickest to implement, requires the least amount of programming and provides the most flexibility to keep jobs going and to meet the end users’ requirements.
When Crimson’s Retail Suite was originally being developed back from 2000 to 2003, the development staff decided MS Access was not going to satisfy the application demand with a large implementation. The task to migrate from MS Access to SQL Server took about a month namely because of syntax differences with dates and casting from one data type to another.
In the end, the effort was worth the transition and in 2003, our first installation was installed successfully with MSDE (Microsoft Server Desktop Edition) 2000 at a 78-store chain.
