In desktop deployment projects we see customers spending a lot of time testing departmental Access databases for compatibility with newer versions of Office. Quite often a home-grown Access database becomes critical to the business. At that point, it is generally better to spend the testing effort in getting the database into SQL where it can perform faster, be more secure, and achieve better robustness. This also reduces the impact of new Office versions because the database format will be independent from the client.
We have a tool which helps here – it makes the process pretty painless. I’m using it in a project at the moment and it is saving tons of time.
To quote the blurb:
“SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server. “
You can download it at http://www.microsoft.com/sql/solutions/migration/access/default.mspx. There are similar assistants to help migrate from Oracle & Sybase to SQL. I haven’t used those – typically that type of activity is performed by our application consultant colleagues or database specialists.