Extend your Microsoft Access Investment: Migrate to SQL Azure

Craig Yellick, VP Business Solutions

SQL Azure; Familiarity and Performance

In a previous blog article, I presented the case for using SQL Azure with Microsoft Access. Briefly, connecting Access to SQL Azure makes your applications securely available outside your local network and perform much better. Your users will continue to see a familiar Access interface and the same application functionality and behavior.

In this article, I will discuss the migration process, which is complicated by the wide range of application development options available when using Access. Applications range from using strictly out-of-the-box features and no programming, to extensively customized applications, and everything in between. As such, the cost of migrating applications can range considerably.

An SQL-Based Database Engine

The built-in Access database engine is SQL-based and works largely the same as SQL Azure, but there are differences. All Access data types can be mapped to something equivalent in SQL Azure, but they don’t all behave the same way. Similarly, the database query syntax is largely the same but not identical. Access forms and reports are based on queries and rely on bindings to data types, so if you get all of that rectified, you’ll find the forms and reports will work just fine.

One of the most common migration stumbling blocks is the amount Visual Basic for Applications (VBA) code running behind the scenes. Outside of VBA code, Access has a very abstract relationship to data. This means if you get the data types and queries sorted out, things will generally work. Programmers can embrace that abstraction, or they can write Access database-specific code that will not work correctly with SQL Azure, requiring a rewrite.

In short, while you are almost certain to run into some issues — in all cases, there are workarounds. It comes down to determining how much you need to invest to make the migration and whether that makes financial sense.

Blue Net is offering a FREE Access-to-Azure Migration Assessment so you can make an informed decision about how to proceed. We’ve been working with Access since the 1990’s and have delivered dozens of migrations of all kinds.