With the dizzying array of cloud-hosted applications and database systems available to IT system administrators today (often deployable at a few button clicks), you may be forgiven for thinking that Microsoft Access has joined the ranks of InfoPath, Visual FoxPro and other semi-legendary deprecated applications. Far from it - Access is still a mainstay within most Office 365 subscriptions today, alongside Word, Excel etc. What’s more, if you are looking to develop a very simple application to be utilised within your organisation, you would be hard-pressed to find an equivalent product at the same price point that would do the job as well. Here are just a few reasons on why Access is great.

• It has the ability to connect to a wide variety of data sources - SQL, SharePoint and others - as well as letting you store data within Access itself.
• The application contains rich customisation options for forms, buttons and other controls, enabling you to tailor the interface to suit a wide variety of business requirements.
• Access has full support for Visual Basic for Applications (VBA), giving you the further potential to integrate complex logic when building your Access application.

Although there was some concerning news recently regarding Access within SharePoint Online, that looked like the writing on the wall for Access, quite the opposite effect seems to be happening. The application is being continually updated within Microsoft, with one of the latest of these updates catching my attention:

Last November, we shared our plan to add a set of modern data connectors that will enable Office ProPlus customers to expand what is possible in their organizations.

Today, we are pleased to announce the addition of two new connectors in our portfolio: Microsoft Dynamics and Salesforce. These two connectors are rolling out to customers with Office 365 ProPlus, E3, or E5 plans.

To clarify, Microsoft Dynamics in this context refers to the Dynamics 365 for Customer Engagement (Dynamics CRM) application, and not any other product within the newly revamped Dynamics 365 family. Getting started with the new data connectors is fairly easy, and may be useful for your business to look at more closely. With this in mind, let’s take a look at how to setup a straightforward Access application that links with Dynamics 365.

Before we begin…

At the time of writing, the new data connectors are only available for those who on the First Release branch of Office ProPlus, something which has to be explicitly enabled on the Office 365 Admin centre. As part of this, you may need to reinstall your Office applications (like I did) to ensure that this kicks in correctly.

Once you’ve verified that you’re on the correct Access version, you can then proceed to create your Dynamics 365 “powered” Access app…

Open up Access and create a brand new Blank database app called Contact Management.accdb, saving it in a location of your choosing:

A blank Access app will be created with an empty Table (this can be safely deleted). Navigate to the External Data tab on the Ribbon and select the From Dynamics 365 (online) external data source:

A pop-up will appear, asking for your Dynamics 365 application URL and how you want to store the data within Access - take a one-time copy of it (Import the source data into a new table in the current database) or connect directly (Link to the data source by creating a linked table). Select the 2nd option, which I would always recommend to select:

Access will then attempt to retrieve a list of the Entities within the application. You may also be asked to log in using your Office 365 credentials, but based on my testing, it seemed to automatically pick these up from my currently logged in Office 365 account for activation - which is nice 🙂

The Link Tables window will then return a list of the entities that you are able to select. In this example, select Contacts and then press OK.

Access will then begin importing the table definitions and the underlying data, which can take a few moments. It is worth noting that Access will also automatically import tables for each N:1 entity relationship for your chosen Entity. This is to allow you to effectively query for “friendly name” information, as opposed to returning the rather ugly looking Globally Unique Identifier (GUID) values for each relationship:

With our data successfully imported, we can then start to build out a Form to enable users to interact with and change records. The quickest way of doing this is via the Form Wizard, which can be found on the Create tab:

By following the Wizard’s instructions. we can select the fields we want on our new form…

…our preferred layout…

…and then, finally, our form name:

Clicking on Finish will load up our newly created Form. From there, we can run a test by adding a Mobile Number value to the Marissa Burnett sample Contact record and then verify this appears successfully on Dynamics 365 after saving:

Conclusions or Wot I Think

The role of Access within the wider context of Microsoft’s offerings is one that has been increasingly open to question in recent years. The debut of exciting new solutions such as PowerApps and Dynamics 365 for Business, does make you start to think whether Access will be for the “chop” in the near future. By adding the new Dynamics 365 for Customer Engagement and Salesforce connectors, along with the other updates that are continually being rolled out for the application, Microsoft makes it clear that for, the time-being, Access is very much here to stay. The reasons for this can be perhaps garnered from my opening comments - it still remains a very versatile way of building quick to deploy, database vendor agnostic solutions that are tailored for desktop use within businesses of any size globally. Another reason for its mainstay - and the release of these new connectors - could be seen as a stealthy means of getting organisations slowly moved across to solutions like Dynamics 365, without the need of moving everything across in one go.

Whatever the reason(s), we can be encouraged by the fact that Access is very much being actively developed, even within the current landscape of varying CRM/ERP solutions. And, what’s more, it’s very cool to be able to say that Dynamics 365 for Customer Engagement is Access compatible.