The Microsoft Access ADP Story

by Philipp Stiefel, originally published May 15th, 2018, last updated March 02 2020

Access Logo copyright by Microsoft, background photo by Philipp Stiefel, all rights reserved.

Access Data Projects were a very powerful project type in Microsoft Access. Unfortunately, they have become deprecated. To say goodbye and bid them farewell I wrote down the story behind the ADPs.

They might be gone now, but their legacy is still present in Access and can be used to enhance your client-server-application.

Access 2000 – Birth of the Access Data Project

In summer 1999 Microsoft released one of the biggest milestones in the whole history of Microsoft Access. Access 2000 came along with a couple of very significant changes from the previous version.

Some of these changes were received with mixed responses from the community. Hardly anyone today still remembers the rather ill-fated Data Access Pages as a first attempt to integrate web development into Access. The idea to make ADO (ActiveX Data Objects) the default data access library in Access 2000 instead of the proven and reliable DAO (Data Access Objects) was questionable at best.

But there were other changes that certainly were unanimously received positively. The improvements to the VBA programming environment and the upgrade of the VBA language to version 6, with quite a lot new functionality, was huge step forward at the time.

The biggest change however, was the introduction of a whole new Access project type. The Access Data Project, or short ADP. This project type completely replaced the local Jet-Engine database Access used to use with a powerful Microsoft SQL Server database for data storage.

Access already was a huge success in desktop database market. Even though Access works surprisingly well even in small workgroup scenarios, desktop databases by design have severe limitations when used in a multi-user-scenario. So, overcoming these limitations by replacing the database engine was a very logical idea.

The first steps had been made with previous versions already. The features of Linked-Tables to an ODBC-Data-Source and ODBCDirect-Workspaces gave a tremendous amount of power to the humble Access developer. But properly using these features required a significant amount of knowledge as well as additional tools to work with the backend database.

ADP – Strengths and Weaknesses

The idea of the Access-Team at Microsoft, to integrate the powerful SQL Server database management system into Access, including a toolset to work with its objects was ingenious.

This would eliminate the need for any external tools. The direct incorporation of SQL Server into Access would pair the ease of Access development with the power of an enterprise DBMS. – It was a grand vision indeed.

The technological foundation of the ADPs was using the ADO data access library on top of OleDB connectivity instead of DAO with ODBC. DAO is the “native” data access library for the JET-Engine and works great for reading and writing data via ODBC. However, for structural changes to your SQL database with DAO/ODBC you need to resort to raw DDL-SQL. In contrast to that ADO comes with special ADOX (X for Extensibility) library that encapsulates the management of the database structure. So, in theory this would have allowed to manage every aspect of your backend database right from the Access environment.

Parts of this worked really well in practice. Seeing a list of all the tables, views and stored procedures in the backend database right in your Access development environment was very convenient. Entering a SQL String to query the SQL Server database into the Recordsource property of a form or the Rowsource property of a combo- or listbox was a huge improvement over linked ODBC tables. Being able to bind an Access form to the results of a stored procedure was amazing. The graphical query (view) designer was pretty helpful as well, but it leads directly to the somewhat problematic areas of the ADP project type.

Weak Toolset

In contrast to these great features, other necessary functionality had some significant flaws. While it was possible to create simple queries in the visual designer, it lacked usability when you needed to use built-in SQL Server functions in your views or needed to write more complex queries.

The most powerful tool to write complex queries is the SQL language. And there is no way around Transact-SQL (T-SQL, MS-SQL-Servers dialect of the SQL language) to create stored procedures on the server. T-SQL is, of course, a text-based language. Unfortunately, the text-based SQL editor of Access was (and is to this day) rather simplistic and lacked a huge number of features required to efficiently write SQL queries.

While the SQL editor in ADP projects was a slightly enhanced compared to the standard Access query editor, it was still lacking on many levels. It was marginally improved (e.g. with syntax highlighting) in subsequent versions of Access, but it was never comparable to the Query Analyzer application that came with SQL Server at the time. As a consequence, developers either used external tools, such as Query Analyzer, to write their queries or they really struggled with the tools available inside Access.

And that was it in regard to the need for external tools.

(Actually, when I took the screenshots for this text, I opened these design tools in Access for the first time in many years. - I work with ADPs in Access almost every business day!)

Technology Impedance Mismatch

One of the most amazing features of the Jet-Database-Engine used in classic Access applications, it the Jet Expression Service. The Jet Expression Service is part of the Jet-Engine. It resolves expressions you use in your queries. It figures out if your expression is fully supported by the database engine itself and can be passed to it directly, or if it references things from your frontend application, such as controls in forms, built-in VBA functions, or custom VBA functions you implemented yourself. Values from these controls or calls to these functions are then made available to database engine during execution of the query.

While this feature can be easily abused and might have a significant negative impact on the performance of queries, it is a feature that contributes massively to the ease of use of Access and its power even for novice developers. This is only possible, because frontend application and query execution all run inside the same process on the same computer.

When using any SQL-Server backend database, the query will be executed by the SQL Server process. That process is always another process that the one running your frontend application, and most of the time it will run on a different computer that might be virtually anywhere in the world. Now, it is impossible to incorporate local elements of the frontend application into the execution of the query.

(Theoretically, it would be technically possible, but the performance implications of distributed execution on multiple machines would be catastrophic. This is (part of) the reason why some queries on linked ODBC tables are dreadfully slow.)

So, with the ADP and its SQL-Backend we lost a very powerful Access feature here. Of course, Microsoft identified this problem early on and added Input Parameters to forms and reports. With these input parameters you were able to reference local controls or functions whose values would be passed on to defined parameters of stored procedures or user defined functions on the SQL Server. However, they were quite a bit clunky to use and still had quite a few limitations compared to expressions in classic JET queries. Most of all, they require a solid knowledge and understanding of the distributed architecture of client-server-database-applications. – Something that many Access users did not possess right away.

And there went the ease of use.

This is not at all the fault of the Access-Team. They did an excellent job of incorporating useful functionality into Access to mitigate this impedance mismatch in technologies. It is simply not possible to make some of the amazing features of Access desktop databases available in a distributed client-server-application.

Component Version Mismatch

The final nail in the coffin of the ADP was a (frequent) version mismatch between Access and SQL Server.

When the Access ADP technology was developed for Access 2000, it was targeting Microsoft SQL Server 7.0, which was the current version of SQL Server at the time. But shortly after Access 2000 was released to the general public, the new SQL Server 2000 was released. It included a couple of new features, most notably user defined functions, that were not supported by ADPs in Access 2000. Less significant in general, but more disruptive to Access ADPs were several smaller changes to datatypes and design time behavior of some components.

This happened again and again and again in the history of Access ADPs and SQL Server. Of course, the Access-Team upgraded the design time features of ADPs to the current version of SQL-Server. But whenever a new Access version was released with such enhancements, a new version of SQL-Sever followed soon after, and once again was not (fully) supported by Access. There were Service Releases for Access to adapt, but still Access dragged behind most of the time.

This usually was not a big problem for ADP applications at runtime. For these the SQL Server updates were hardly noticeable because in that regard SQL Server had an excellent history of downward compatibility. So, it did hardly affected any ADP solution already in production. Almost all of them continued to run with SQL Server 2000 (and later versions) without any changes required to them.

However, the design time tools in Access were suffering from this, suffering badly. It is obvious that you could not use new SQL-Server features that were not supported in your version of Access. But with some updates the Access design tools for SQL Server objects stopped working altogether.

ADP in Access 2002 to Access 2010

After their initial introduction in Access 2000, ADPs evolved only very slowly. Support for User Defined Function was added in Access 2002 (or 2003, can’t remember anymore). Other than that, there were no major new enhancements in the following versions. So, there was very little progress being made beyond keeping the basic functionality working with new versions of SQL-Server.

The demise of ADPs

Finally, with Access 2013, Microsoft discontinued ADPs and removed support for them from Access.

I do not have any inside information on the reasoning and the process behind this decision. From my outside point of view, I guess the rationale was a combination of three things.

1. The adoption of ADPs was not as strong as Microsoft had hoped. Mainly due to the flaws and problems outlined above.
2. The pressure of keeping up with the pretty fast SQL-Server development was probably pretty intense for the Access-Team.
3. The OleDb technology was deprecated and support for it was to be discontinued. This would have removed the very basis ADPs were built on. (This decision was reversed in October 2017 and there is a new OleDb-Provider for SQL-Server available now.)

What could have been

After writing the previous paragraphs about the ADP technology and its history, I cannot refrain from thinking about what could have been if things had been done differently.

Hindsight vision is always 20/20 and from the outside seeing only results, it is hardly possible to guess which challenges existed for the Access-Team during the development and maintenance of the ADP functionality in Access. So, take the following paragraphs with a grain of salt. It’s always easy to know better after the fact, all the more without having to take the risk and facing the challenges yourself.

In the beginning – Access 2000

When designing ADPs, the Access-Team did a very sensible thing. Instead of reinventing the wheel (the graphical designers) in Access, they reused many of the graphical components from Enterprise Manager, the primary administrative client tool for SQL Server.

When integrating these components into Access it seems there was too little attention concentrated on the upgrading of those. They should have planned for a simple way to upgrade these components independently of Access (e.g. with the SQL Server client installation). That would have taken the burden of keeping up with SQL-Server from the Access-Team.

However, with SQL Server 2005 this would have fallen apart anyway. Enterprise Manager was replaced by SQL Server Management Studio and all those reusable components would not have been updated any more by the SQL Server team.

So, even in hindsight I don’t see any easy options for significant improvements when ADPs first came to light.

At the End of ADPs – Access 2013

In my opinion, there would have been another option than killing-off ADPs altogether with Access 2013.  The Access-Team could have embraced the fact that most serious developers working with ADPs were using external tools for the development work anyway. So, it could haven been a viable option just to drop all those SQL-Server design tools from Access and keep the skeletal core functionality intact as it was.

This would have removed the pressure of keeping up with SQL-Server without loosing the runtime capabilities of ADPs. – If you look closely, this is almost what they did. - Of course, all the design time features of ADPs are not available in the AccDB file format. But you can do almost all the things that were essential to ADPs with VBA code. E.g. you can still bind a form to an ADODB.Recordset created from an SQL-Server stored procedure via an OleDb-Connection. Almost all of it is still there!

What is essentially missing, is just setting the main connection (CurrentProject.Connection) to an OleDb-Connection to SQL-Server and the Recordset-Property of a report. It should not have meant too much effort to continue to maintain these two tiny features.

Now – Access 2019 and onwards

SQL Server connectivity and integration into Access is still a huge topic. There are several popular suggestions on user voice about this.

This functionality will be used only by small percentage of the Access users. Nevertheless, just having the option to scale an Access application up to SQL-Server it is definitely a factor when deciding to use Access for many kinds of projects. So, improving SQL Server connectivity in Access should be a priority.

Bringing back ADPs in all their (tainted) glory, is completely out of the question. The Access-Team is rather small nowadays and the effort required to do this is beyond their capabilities.

However, they could focus on improving the areas where the current options for SQL-Server integration is lacking.

In my opinion there are two key issues that need to be fixed/improved.

• When using ODBC to connect to SQL-Server, ODBC-Pass-Through-Query need to be improved. It should be possible to get writeable Recordsets from these queries and it should be possible to get the return values or output parameters of stored procs (without crude workarounds), as it was with ODBCDirect in the past.
• The option to bind ADODB.Recordsets to reports should be added and the binding to forms should be improved. In that context, automatic detection of UniqueTable and ResyncCommand should be improved and the issues (errors and crashes) with using the F5- or CTRL+F9-key to refresh/requery open forms should be fixed.

These should be manageable tasks and would hugely improve the possibilities of Access with SQL Server.

Migration Projects

I currently work on an ADP to AccDB application migration project for one of my key customers. Several similar migration projects have been requested by other customers and will be implemented in next couple of months.

I will probably write on this website about my experiences with these migrations and publish general information on using Access with SQL-Server backends. If you are interested in these topics you should subscribe to my newsletter at the bottom of this page.

My schedule is usually pretty busy for months in advance. Nevertheless, if you want to hire me and my team for your migration project, please contact me.

A Helpful Tool

If you think about or are already working on migrating your ADP to application but don’t want to hire external help, I have another tip. – Not entirely selfless, though. – I recently published a commercial Find and Replace tool for Access. It proved to be invaluable when I need to rename or change countless occurrences of certain terms in a sizeable Access application. – Exactly what you need to do in an ADP migration. – I wrote about using Find and Replace to help with efficient ADP migration on the Access DevTools blog.

Share this article: