Hands on: The future of Access revealed

The future of Access has been a matter of debate for some time. When pushed, Microsoft made all the right noises.

For example, only last year Clint Covington, then Access lead program manager, told the Access User Group that Access had a secure future. But he wouldn’t be drawn on the future of the Jet database engine, and it wasn’t directly clear about the function Access would have.

Would Access continue in its current role or be relegated to, for example, acting as a front end to SQL Server?

Access granted
At Microsoft’s Tech Ed developer conference in Boston this summer, Suraj Poozhiyil, program manager at Microsoft, revealed all (well, almost).

Access is to retain its own database engine for the foreseeable future. The Jet engine has undergone a ‘very significant update’ in this release for Office 2007 and has been renamed Ace.

I mourn the passing of the name: the Jet engine sounds powerful, with a faint hint of Dan Dare. But I always have been a reactionary. This is excellent news for enthusiasts, who can continue to have database fun.

However, on the face of it there is something very strange going on in the world of Access. It is acquiring some new data types, one of which is a multivalue field.

A multivalue field, as the name suggests, is a field that can contain multiple data items. As the new Microsoft documentation says: “With the multivalue field, you can now select more than one value; for example, assign a task to more than one person in each cell.”

Anyone who is a fan of both relational database theory and well-designed databases will find their toes curling up in their shoes on reading this. But if your toes are still straight, you might want to read ‘Problem unsolved’ later in this feature.

Suraj explained there are two main reasons Microsoft has done this. One is for compatibility with Sharepoint, which already supports multivalue fields.

Access users might well ask why they should care about Sharepoint compatibility, which is a fair question and one I can’t answer. Microsoft sees the compatibility as important for its own reasons and had made its decisions accordingly. We just have to live with it.

The second reason concerns what Microsoft wanted to achieve in this version of Access. Let’s face it, the product has suffered from a lack of development in the past. But that period of the product’s history is now over and the product team was keen to bring it up to date.

However, there is only a certain amount that a version change can encompass and, for this version, Microsoft focused on the power user rather than the developer.

When many is too many
Power users, in this definition, include people who create simple databases using the graphical interface tools rather than the programming language.

Microsoft believes such users find the creation of many-to-many joins between tables conceptually very difficult because of the need for a joining table.

A multivalue field lets you create many-to-many relationships within data. An example would be multiple sales, each of which can refer to multiple products.

In a relational database this is handled with a joining table. Traditionally, we’d use a set of three tables to represent the many-to-many relationship. We could use a form in the database application to hide this complexity from the end user.

Click on the following link for a demonstration of the Access database dbcaug.mdb. Alternatively, if you’ve bought the DVD edition of PCW, you’ll find it on the October issue's cover disc.