Access 12's new data engine
In the first Access blog post, I noted that Access 12 has a new database engine, but one derived from and very similar to the old Jet engine. There were a bunch of questions & emails about what we did, why, and what it means, and it will take me more than one post to get through everything so please be patient. In this post, I’ll cover what how the database engine fits into Access and how the new is different from Jet. In future posts, I’ll talk about the Access data security model and other engine-level stuff.
Access differs from most other database authoring tools by storing everything in a single file. When an Access author creates a database, the data and all the UI to work with that data all live in the same “.MDB” file. This makes database installation very simple and enables end-users to work with Access databases in just the same way they use Excel spreadsheets or Word documents. The engine provides 3 things to Access: somewhere to put data, somewhere to put UI forms and reports (this is really just more data), and a query processor.
In past versions, Access has used the Microsoft Jet database engine for data storage and query processing. Jet is commonly thought of as being part of Access (or even as being “Access”) but in reality it is a Windows system component built by the SQL Server team. Jet is unique in Microsoft because it provides both a data storage mechanism with a heterogeneous query processor. This ability to run queries across a number of different data stores is a key reason people use Access, so it was super important for us to retain that ability going forward. The SQL team is de-investing in Jet, and telling developers to code against SQL Server Express, but as it lacks the heterogeneous query functionality we needed, Access stuck with Jet.
However, we needed to extend the Jet engine, so took a “private” copy of it, and have extended it for Office. This means Access 12 no longer uses the system Jet engine, but is tightly bound to its own version. The new version is fully backwards compatible with the old, so it will read & write files from earlier versions without any problems. And since Access now can update the engine, developers can be confident that their Access apps will continue to work in the future. Developers can still program against the Access engine, but since it isn’t part of the system any more, application users will need Access on their machines. Developers targeting users without Access can continue to use the Jet engine as they have for years or can move to SQL Server Express.
The primary feature we added to the new Access engine is support for “complex data”. Complex data really isn’t so complex – it is simply a join in relational terms, or a repeating region in XLM terms. What it brings to Access is full schema compatibility with Windows SharePoint Services lists. This allows us to take SharePoint list data offline and to provide rich-client UI for SharePoint using Access.
In the application, complex data usually shows up as the ability to select more than one value for a field. For example, imagine an Issues list with a column for “Assigned to:”. In a single table you could only assign an issue to one person, and Access would provide a simple bound drop down list of people to Assign to. With Complex data, you can (still looking at a single table) assign the issue to several people at the same time, and Access provides a drop down check list with the ability to select several people. This works just the same way as it does in SharePoint.
Behind the scenes, Access does just the same thing a developer would when building the same functionality. We create a table for the join information and create a many-to-many join (in this example between issues and people). The Access database is fully normalized, and there’s no string manipulation monkey business or anything untoward going on – its just vanilla relational joins. However, from the end-user’s point of view, it is far simpler than building the tables and setting up the relationships herself. And since SharePoint doesn’t support joins, it maps directly with the SharePoint way of doing the same thing.
Developers, of course, are quite comfortable doing this by hand and can choose to either continue as they have or to use the new functionality. For developers targeting SharePoint, it provides a mechanism for handling SharePoint’s complex data structures naturally.
In the next post, I’ll talk about the changes to the Access security model, and how that flows through both the data layer and the rest of the application.
