Microsoft Access News 2020 Recap – And a Peek at 2021

by Philipp Stiefel, originally published 2020-12-17

last revision on 2020-12-17

Image based on a photo by free stock photos from www.picjumbo.com from Pixabay

The year 2020 was not one of the best in many regards. At least Microsoft Access was a bright spot. A couple of small new improvements and new features in Access were released to production this year.

The current Access Team at Microsoft is consistently delivering new features. They may be small, but they are really focused on helping a significant number of Access developers to work more efficiently. This is a huge improvement over multi-year stasis or fancy pie in the sky features we have seen in the past.

Find and Replace in the Query SQL View

The first small but useful new feature is a new Find and Replace dialog in the SQL view of a query. Even though it is small, is actually the best new feature in Access in 2020 in my opinion so far. I use it frequently when working in the Access SQL editor, which is not too often because it has a limited user experience overall. -  But that might improve in the future …

Improvements to the Relationship Window

A couple of small improvements in the Access Relationship Window now allow you to select multiple tables, and there are new entries in the context menu to directly open any table and to resize the table “to fit” in the relationships.

New Add Tables Task Pane

Well, I have mixed feelings about the new Add Tables task pane (tool window) for the Query Design View. Sure, the old “Show Tables” window was a pain and compared to that the new window is an improvement. However, on the other hand this is a minor enhancement and just making the old window non-modal and resizable would probably have needed less effort and would have been almost as good. Alternatively, dragging tables from the Navigation Pane into the query already offered a very similar user experience.

ACE-Engine and OLEDB-Provider Available Outside Office 365 Sandbox

The Office 365 installation of Microsoft Access obviously includes the ACE (Access Connectivity Engine) database engine. But before this enhancement the ACE-Engine and the corresponding OLEDB-Provider was only available to applications running inside the Office 365 sandbox but not to any other application on the same computer. – In Essence: Even though you had a full installation of Access, you would also need to download and install the ACE Redistributable to connect any non-Office application to an Access database.

Now, with this “Breaking out of the bubble“ enhancement, other applications, including PowerBI and the SQL Server Migration Assistant, can directly connect to Access databases with just Access 365 installed.

A similar enhancement to the DAO Library and ODBC Interface is announced to be released soon.

New Date/Time Extended Data Type

This is a particularly useful new data type for developers working with SQL Server backend databases. SQL Server has a DateTime2 data type which has an extended value range and extended precision (up to 100 nanoseconds) for date time values. If you connected to a SQL Server database using the DateTime2 enhancements by linking such a table to Access before this new data type was available in Access the DateTime2 data was linked as text, which caused problems with sorting and filtering the data. Now DateTime2 is mapped to the new Date/Time Extended type in Access.

You can also use Date/Time Extended in native Access Tables without SQL Server being involved.

But don’t get too excited about Date/Time Extended yet. There are still a lot of limitations when using this data type. It is only supported in VBA as text (String) data and needs diligent conversion when it is read or written from/to DAO recordsets. Data entry of Date/Time Extended in Access forms also has some pitfalls. – Not that you should enter date/time with nanoseconds manually on a regular basis…

Upcoming new Features in Access in 2021

With 2020 drawing to a close, let’s take a peek at the highlights of the announced features for 2021. Microsoft now also includes planned new features for Microsoft Access in the Office 365 Roadmap. – There are some bigger ones coming…

New Chromium/Edge based web browser control (Planned for August 2021) – This will finally replace the old Internet Explorer based web browser control in Access. Very promising.

Large Address Awareness for 32bit Access (Planned for July 2021) - Very useful to have this baked into Access. – If you don’t now what this is about or if you are looking for a workaround to also get this in older versions of Microsoft Access and to get it right now, read my text on Large Address Awareness.

New Microsoft Graph Data Connector (Planned for September 2021) – This is not about the any charting component, but about the Microsoft Graph, basically an API to connect to the data behind the Microsoft 365 platform.

Monaco Editor for SQL Queries (Planned for August 2021) – The Monaco Editor is the core editor engine also used to power the popular VS Code development environment.

Dataverse and Microsoft Teams Connector (Planned for June 2021) – Microsoft Dataverse is the new name for Microsoft Common Data Service (CDS) which is a unified interface for data used particularly by the Microsoft Power Platform.

Conclusion

Access enhancements in 2020 were small but useful. They show that the Microsoft Access Team currently does not have the resources to make big improvements, but they also show that they care about Access developers needs and that they are trying to do what they can to improve the product. In the bigger picture we also see that Access has not much priority at Microsoft at the moment.

The 2021 outlook still shows features that are actually relevant to many Access developers, but the scope of some of those features is different to what we have seen this year. Particularly the new connectors to Graph and Dataverse can give whole new level of power to Access, but it is crucial to get this right and not to leave it half-baked. I hope the Access Team delivers something great there.

Share this article: