Access 2010 introduces a new type of database called a Web Database. This new database type which has the standard accdb file extension is designed to allow the data and application to be published to a SharePoint 2010 Server, and the resulting application can be viewed in a browser.

This article looks at how you can approach the problem of converting an existing Access Database (accdb) file to a new Access Web Database (also having an accdb file extension). The main focus of the article is how to convert the data layer in an existing Access database to a Web database.

The minimum amount of work that you will need to undertake is to convert the Access Client Tables to Web Tables (making them web-legal), after suitable restructuring you can use the built-in conversion feature for converting the tables.

After publishing your new web tables to SharePoint you will be able to use the Access application (Client Forms, Client Queries, Client Reports, Client Macros and Client VBA code to manipulate the SharePoint data. If you want to use the new Web Forms, Web Reports, Web Queries and Web Macros which can be displayed in a browser, then as there is no built in conversion feature you will need to recreate as new these objects. You may decide to adopt a hybrid approach, using newly created web objects for some users and the Access Web Database for other users.

Creating an Access Web Database allows you to develop your web solution (without the need to have a SharePoint 2010 Server). You will eventually need to have a SharePoint 2010 server for publishing your final solution.

Areas for which conversion is required

Your web application will consist of several key components.

• The first layer is the data layer; this includes your database tables and the data held in the tables, and you will need to convert your existing Access Client tables to Access Web tables.
• NOTE after making your tables web-legal, you can now use your existing client forms, client reports, queries and application code working with the published SharePoint tables. However, this part of your application can not be displayed in a browser.
• The second layer is the query layer, and you will need to recreate from new your existing Client Queries making them Web Queries for any parts of the application that will be used by the browser based interface. The queries provide a layer which presents the data layer in a more useful form to the application.
• The third layer is the user interface (which can be constructed on top of both the data layer and/or the query layer), this consist of the Web Forms which you will need to recreate from new and Web Reports which would also need to be created from new for any objects that will be used by the browser based interface. There is also the issue of how users will navigate through your application, and this is most simply achieved using a special new Navigation Control.
• The final component is the program logic any VBA code would need to be recreated from new to use a combination of Web Macros and Data Macros for any Web Forms that will be displayed in the browser interface.

When you construct a Web Application, you are working within a subset of the standard Access products features, this subset of features have been designed to allow you to produce an application which can be used in a Web Browser with SharePoint 2010. So you should not be surprised that you will need to conform to a set of rules which produce something that can be held in SharePoint.

The remainder of this article will focus on how to convert the data layer from a standard Access database to an Access Web database.

Compatibility Feature

Microsoft has added a compatibility feature into Access 2010. This allows you to manually check individual tables to see if they are compatible with the web format. The compatibility check is also automatically performed when you attempt to import a set of existing Access tables into a new Web Database. You should also be aware that even when you achieve a 100% pass on checking individual tables in a standard Access database there are still some additional checks that you could still have problems with that will only become apparent when you attempt a final import of the existing tables into the web database.

At this point you could simply try and import all your existing tables into the new database using the standard import feature in Access, this will give you an idea of the amount of design change that is going to be required before you can complete an import.

If you are lucky then this will work without any errors, if not then you will have the option to display a list of compatibility errors.

Clicking on the link show in the above graphic adds a new compatibility table to the database (nothing gets imported because we have errors).

This table contains a list of errors, with descriptions and hyperlinks to help resolve the problems.

At the time of writing this not all the error codes appear to have an online explanation, but the descriptions provided in Access are very informative.

You are probably better advised to now open the source Access database, and check each table for compatibility, then resolve the errors for each individual table.

In order to be able to do this, you need to make sure that you have converted an existing mdb to an accdb format; otherwise you will not get the compatibility check option shown below.

In your standard accdb database if you right-click on any table, you can check the tables compatibility

Each time you do this, it re-generates a Web Compatibility Issues table for the table that you have examined.

There is also an option in backstage to run the compatability checker for the entire application.

Primary keys

There does not appear as yet to be a document which fully describes all the general rules that you need to follow to get a compatible database (at this time of writing). So below we will explain what we have discovered about the general rules for having a compatible database

“A Table should have a Primary Key and it should be a number with field size 'long' to be compatible with the Web.”

Each table needs to have a primary key, and that key needs to consist of a single field. This field must either be an autonumber or a long integer (an autonumber is also a long integer).

Resolving problems here may have several implications for you (if you don’t already have autonumbers on all your tables).

• you need to delete any existing relationships to the primary key
• you need to create the new primary key
• you need to create new foreign keys
• you need to re-map the data in the new foreign keys
• you need to re-establish the new relationships
• you need to consider the topic of lookups matching the relationships

As an example we consider some of the tables and relationships in the Northwind sample database.

Consider the relationship between Orders and Customers.

The existing CustomerID is a 5 character text field which needs to be replaced with a long integer (shown below is the existing field).

We need to add a new key field called Customer_ID, and an autonumber is an ideal choice, below we see the new field and relationship.

We have added a new primary key to the Customers table (a corresponding long integer field has been added to the Orders table as a foreign key).

All that remains is to construct an update query to set the foreign keys to the new values.

Another good example of the need for re-structuring is the relationship between Orders and Order Details; which uses a composite two-part key on the Order Details table (which as a primary key is not allowed).

This needs replaced with the following autonumber field called OrderDetails_ID.

In the above example we don’t need to re-populate any keys, we just need to have a different primary key for the table.

Lookups and Relationships

In SharePoint we don’t have the distinct concept of a Relationship and a separate combo list called a Lookup; instead we have a new concept called a Lookup which is in fact a Lookup & Relationship.

Before you can convert your database you need to match all your data table lookups and relationships to be consistent with what SharePoint expects to see.

In Access a relationship can optional enforce referential integrity, and in SharePoint a lookup will always perform a Limit To List. So for the purpose of conversion, we don’t care whether a relationship enforces referential integrity or not, but we do care that the relationships matches against a lookup, and we require our lookups to have a Limit To List set to True.

So we now end up with two rules:-

“Every data lookup (combo box defined in the table design) must have a corresponding relationship (R.I not required) and have Limit To List set True.”

“Every relationship must have a corresponding Lookup defined in a table’s design”.

This is like having a balance, all relationships must balance with lookups, and all lookups must balance with relationships.

Taking our above example of the relationship between Orders and Customers. We now need to ensure that the old CustomerId does not have a lookup (because it no longer has a relationship), and the new Customer_ID does have a lookup.

The old CustomerID (text field) lookup needs to be reset from a Combo Box back to a simple Text display.

And we need a new Customer_ID lookup.

Following along with this idea, you may think well what about the relationship between Orders and Order Details, do we also need a lookup here? The answer is yes.

This is shown below:-

In summary we need to have a balance, for every relationship we have a lookup and for every lookup we have a relationship.

Lookup Rules

There are more lookup rules that you need to adhere to. So below is our present summary of what is required here.

• Each lookup must use a combo box and not a list box
• Limit To List must be specified as True
• You must specify column widths
• The SQL used for the lookup cannot just refer to a table name , it must be of the form ‘select x,y from tableX’
• You are not allowed calculated or complex expressions like [FirstName] & “ “ & [Surname] in the SQL, it must have a simple form.
• Lookups should not have default values

A need for calculated fields

The Employees table in NothWind has a challenging example of a lookup which needs modification. In the SQL shown below you can see that a calculation is used to combine the firstname and lastname fields, this requires replacement with a calculated field.

This lookup also needs a relationship before it will convert, and that is quite easy to add to the relationships.

Because we need to have a lookup on the expression [FirstName] & “ “ & [LastName] and this is not allowed, we call upon one of the new Access 2010 features, namely calculated fields.

We can now define a calculated field as shown below:-

Now we are able to redefine our ReportsTo lookup as shown below:-

The good news is that most of the hard work is now completed. There is one final structural issue to address. Having added our new foreign keys like the new Customer_ID in the Orders table, what do we do with the old foreign keys?

There are several possible answers here:-

• leave the old foreign keys and ignore them
• delete the old foreign keys
• use a Data Macro to keep the old foreign keys updated

There are a number of fundamental field settings that you also need to ensure are correctly set before achieving a successful conversion.

Field Formatting

In NorthWind if we look at the employee table we find custom formats used for dates and currencies.

This is not allowed, and we are restricted to using the standard built in formatting functions.

So for dates you need to use a built in date format, for numbers a built in number format and for currency a built in currency format.

Field Validation Rules

Validation rules will in general convert to the Web Database. For example the following is allowed.

But in what follows the use of BETWEENis not allowed and must be changed to >=0 AND <=1:-

Field Data Types

Data types in general appear to translate well between a Client Access database and an Access Web database. There is however one exception, the OLE Data type. This would need conversion to an attachment data type, and Access does not allow you to change either OLE Data types into attachments or attachments into OLE Data types. In order to do this you need special program code to export the objects and then re-import the objects.

You are also only allowed one attachment data type field per table.

Publishing Data

Your tables will need to comply with the allowable data ranges for the corresponding SQL Server datatypes. The most obvious potential issue here is in having bad dates data, as Access allows for dates outside of the allowed range for a SQL Server DATETIME data type.

Application Scale

If you have a database with only a few tables, then there is no doubt that you will be able to manually convert your application to a web database. If you have a larger application then we can provide consultancy to take advantage of conversion tools to ease the transition process.

Top of Page

Top of Page