For a Future Version: Re-Engineer Microsoft Access to Use a SQL Server Backend

At the core, both Microsoft Access and Microsoft SQL Server are relational database management systems (RDBMS), yet each uses a separate file format and code base. These differences add friction to the process of integrating the two technologies. Developers interacting with both back ends need to learn the idiosyncrasies of two systems. For administrators, the fact that each uses a different driver requires maintaining multiple driver configurations on systems that need to communicate with both. Power users are faced with the pain of figuring out how to cross this technology divide when growth dictates switching a home-grown Access application to a SQL Server backend.

Each of these pain points could be greatly reduced or eliminated if Microsoft switched Access to use a SQL Server-powered local database engine for its backend. Imagine Access moving to an Open Office-like zip file format containing a SQL Server mdf, a configuration file and a collection of XML files defining objects such as reports. When launched, Access would attach the mdf to the SQL Server engine using functionality similar to LocalDB’s AttachDbFilename.

Beyond SQL syntax changes, Access users would notice very little difference when using this new architecture. A SQL Server user looking in the mdf would find a database structured according to Access-specific conventions, complete with Access saved queries represented as views. Migrating an Access application to run against a full-fledged SQL Server instance would be as simple as taking the mdf out of the Access file, mounting in on a SQL Server instance and editing the configuration file inside the Access zip file to reflect the data’s new location.

Query writers will be quick to point out that there are differences between the SQL dialects spoken by Access and SQL Server. The process of upgrading to this new format would need to adapt saved query syntax to T-SQL. A set of Access CLR UDFs might be necessary to supplement SQL Server’s built-in functions with Access-specific behaviors. While significant, the SQL dialect translation could be automated with minimal loss of functionality by an upgrade assistant. Yes, Access query writers will need to adjust to the SQL syntax differences but many of them have already faced this requirement as their Access applications already talk with SQL Server. In the long run, this change could simplify the learning curve by allowing them to focus on only one SQL dialect.

The biggest challenge in pulling this off will probably be extending SQL Server to support Access’s file-based lock coordination. SQL Server’s client-server model allows only one database engine to access a data file at a given moment. Access’s modus operandi requires that multiple instances of Access be able to edit different parts of the same Access file simultaneously. SQL Server would need to be extended to offer a file-based locking mechanism allowing multiple processes to simultaneously manipulate a single mdf file. Perhaps this could be done by creating a clone of LocalDb that coordinates locks via the file system.

Making a change like this would be monumental, on par in significance with Microsoft’s re-engineering of Word, Excel and PowerPoint in version 2007. Whether doing so—particularly implementing the file-based locking extension—would be practical, is a valid question. It is one thing to dream up something so momentous; it is another to implement it. There are pros and cons which need to be considered and costs which need to be weighed against benefits. If I were the decision maker, I can’t say what recommendation I would make if I had access to such in-depth analysis. However, I can say that the idea is tantalizing enough that I would want it explored further.

Leave a Reply

Your email address will not be published. Required fields are marked *