Microsoft
Access 2007

Enhancements to Access 2007

One of the most basic tasks in Access is creating somewhere to put data - designing schema.  Access 2007 can create schema by example, where the user simply starts typing into a grid (the "datasheet") and Access guesses the data types automatically.  This is ideal for beginning users, and Access 2007 automatically creates an ID column for each table, so there's a unique index for the table, and the user has something to build joins on as she gets more sophisticated.  The basic schema by datasheet experience looks like this:

To perform this operation, the user simply starts typing in the header row to set column names, then types data in the rows below to fill-in the table.  Access guesses the data types as the user does this. 

Table Templates – While simple tables are rather easy to create, one of the key problems that new users have in Access is creating well-structured databases.  The tracking apps will help by creating normalized schemas for the most common applications, but many users will need to build their own databases without starting from the tracking apps.  In this case, Table Templates provide a way for users to get well-structured single tables that they can easily add to their own applications.  To accomplish this, Access 2007 provides a simple entry point on the ribbon as shown to the right:

 

 

The entry provides a list of five (5) basic common table types that the user can simply drop into her application and extend or use as is.  An example would be the Issues table, which looks like this:

The full list of fields is easier to see in the table designer:

SharePoint List Templates - Access 2007 also provides a set of templates for SharePoint lists similar to that for local tables.  This list is available from a button in the ribbon right next to the Table Templates:

It has a similar list of tables, with the exception of Custom (which creates a simple SharePoint list that you can then extend) and it also provides an alternate way to link to an existing SharePoint list.  Selecting one of these lists brings up a prompt for the appropriate server and a name for the list:

The schemas for these lists are similar to those for the local tables, the only difference being that the SharePoint lists are created on the server and linked to Access.

Field Templates - Users who want to create their own tables can still get help building well-structured fields (i.e. with appropriate data types and lengths) from the Field Templates.  Here the user selects the field templates button on the ribbon and gets a list of potential fields.  This list contains a set of generic fields, and then all the individual fields from the Table Templates above.  Click the image below to see the list of available fields for Issues. 

The user then simply drags and drops the appropriate field into their table. 

Lookup Fields and Complex Data - Access has supported "Lookup Fields" for several versions and has added power to the feature for Access 2007 by allowing for multi-valued lookups.  Lookup fields are simply fields that present a different value in their control than they store in the table.  In the Issues example we've been using, "Assigned to" and "Opened by" are both lookups.  The Issues table stores the ID of the entry in the Contacts table, but whenever the user sees the Issues table, they see the Contact Name rather than the number.  This is simply good database practice (normalized data, joined on unique identifiers), but made easy enough for non-developers. 

Access 2007 extends the lookup concept by enabling multi-valued lookups.  Where a lookup is really just a join made easy, a multi-valued lookup is simply a many-to-many join made easy.  In XML terms this would be represented as an optional repeating value, which is why we think of this internally as support for complex data.  SharePoint uses these data structures in a number of places (e.g. support for multiple attachments for each item in a list), so Access needed to add similar support to provide schema symmetry, but the concept is broadly useful outside the context of SharePoint.  In our Issues example, the user may want to assign an issue to more than one person.  This was do-able before, but required a lot of work (building the join table, setting up the relationships) and a lot of knowledge (i.e. how to build the join table, etc.)The Lookup Wizard now does all this for the user.

The user simply starts by clicking the Lookup Column button on the ribbon (with a column selected in the datasheet):

 

Then the user goes through the wizard as they did before:

On the last pane of the wizard, select "Allow Multiple Values" and hits finish.  This automatically builds (and hides) the join table and sets up the relationships, so the experience is just the same as for a standard lookup. 

The key difference, though, is that the user can now select multiple people in the Assigned to column:

- END -