Office 12 – Access enhancements

The main themes are:

• Easier to get started
• Adapt to your needs
• Easy to share
• Easy to manage

It’s interesting that none of these seem to address the biggest issue I see which Access usage in the corporate environment which is a concern over the following:

• Data silos being created outside of the companies main applications and databases
• Business logic being captured in departmental systems
• Individuals frittering time away creating local databases
• Interconnects being created between corporate data systems that are unknown to the data owners and architects

However I was most impressed by the fact that in this version of Access less focus seems to have been given to the addition of new features and more focus to the achievement of real business scenarios, in fact this is a trend I noticed throughout all of the Office teams.  One example of this is the creation of 30+ template applications, these applications are real functional applications and it is clear that by creating these the Access team have identified many functionality gaps that people face when trying to use Access in the wild.  Many of these gaps involve workarounds or local code being developed many thousands of times over.  This time the Access team will hopefully solve these sort of gaps at source.  The template applications focus on the following types of activity:

• Event management
• Contact management
• Issue management
• Task management
• Project management
• ….

Generically Microsoft seems to describe this area of focus as “tracking” applications.

One of the best features in Access 12 is data collection via email.  This allows HTML or Infopath forms to be distributed via email to users and the resulting data to be entered into Access when the email reply is received in Outlook 12.  This is pretty nice for ad-hoc internal and external data collection scenarios that would have previously been achieved using free text data collection and re-keying.

Core database engine improvements:

• A new database engine has been developed, that supports the same capabilities that are exposed in SharePoint lists.  This is the default database engine,  although jet is still supported but without the following capabilities:
• Multi-value fields
• Append only fields (great for change logs)
• Attachments fields (attachments are compressed and stored in the database file)

Access can now read and write to the Excel XML data format

The following useful snipits were revealed about how people work with, get access to, create data:

• Employees get between 50 and 75% of all of their data from other people
• 80% of all data is personal
• Most knowledge is still retained in peoples heads, despite a decade of “knowledge management” initiatives most of which failed

Integration with SharePoint is a big theme in Access 12,  here are some of the integration points I noticed:

• There is a new “data less” file format that can be published on SharePoint that only contains links to external tables, query definitions, report definitions etc.  Using this file format allows Access to be “launched” from SharePoint and then used to access SharePoint list data
• Access provides rich joins, queries, forms and reports that can act on SharePoint data
• Access allows bi-direction off-line working against SharePoint data
• Access allows bulk update operations against SharePoint data (this is powerful and worrying as I don’t believe any special permissions are required for Access to SharePoint via the Access client, rather than the web client)
• When taking SharePoint data off-line it’s possible to take a list and automatically all linked lists as well, to edit these off-line with validation still applied and then to sync changes back.
• Access views and reports can show up as views in SharePoint, when you click on them Access will open
• Access database tables can be stored on SharePoint and maintained as SharePoint lists but can also be accessed using Access at the same time
• Workflow tasks can be initiated or completed via Access

The following data types are supported:

• Text, single line, multi-line and rich text
• Choice single and multi-value
• Number, currency, date, time
• lookups, single, multi-value
• Booleans
• Hyperlinks
• Attachments
• Append only fields
• Folders in lists

My overall impression is that corporates concerns over Access will grow as it provides so much power to end users at the edge to get access to corporate data and to manipulate it in uncontrolled ways.  That said in the right hands, it provides very powerful tools to manipulate SharePoint data in an efficient way, to create insights from that data that would otherwise be impossible and to undertake bulk operations that will keep the data “tidy”.  With the increase in structured data that will reside in SharePoint via promotion from Office documents and InfoPath this will be of even greater benefit.