support@ascassociates.biz

sales@ascassociates.biz

View updateability and Timestamp columns(25th February 2010)

It is standard practise to add a TIMESTAMP column to tables when upsizing them to SQL Server, this special column leads to more efficient and error-free updates when Access updates data using linked tables. Access query performance can often be significantly improved by converting a query to a view. The question we look at in this article is how does the use of timestamp columns apply to making updates on these linked views?

Below is a simple example of a table from Northwind which contains a timestamp column.

If we perform an edit from Access on this linked table and use the SQL Server Profiler to examine what Access is doing we see the following:-

We can see in the above WHERE clause that Access is using the timestamp column and primary key to perform the update; this makes the update efficient and accurate.

We will now examine how timestamps are used with views.

A simple View selecting all columns

The first view we consider is shown below:-

In Access we link to this view, and tell Access that the unique record identifier is the CustomerID, this makes the view updateable.

Looking at the profiler trace we will see the following, which indicates that Access knows that this view contains a timestamp which can be used as part of the update statements.

Simple View excluding TIMESTAMP column

Next we create a view as shown below which does not include the TIMESTAMP column:-

If we create a link from Access to this view and perform an update, we see the following result in the trace:-

As you can see above because we do not have a timestamp column Access uses the compare all values approach to updating the data.

If you want to ensure that the timestamp is used in the update, then you would need to change the view to include the timestamp column.

If we re-link the View and repeat the update on this view, we observe the following trace:-

This means that in most cases you can have a view which is updateable and does not include a timestamp, but if you want to get the best performance or if you have been having problems with updating a view (if Access tells you that it can’t update the data because it has been changed by another user) then adding a timestamp to the view is recommended.

Example of a View with multiple base tables

We have previously shown how to manage updateability on views involving multiple base tables, but the question raised here is what role does a timestamp column play in these updates?

Consider constructing a view as shown below joining two tables. The primary key of the [Orders] table is [OrderId] and the key for [Order Details] is [OrderId] and [ProductId].

When linking we specify the unique record identifier as shown below:-

Now if we edit the order date changing this from 1/1/2010 to 1/4/2010 we see the following (note the view should really be refreshed after the edit as there are three order details all of which have the same order date).

If we then refresh the result we see the following:-

If we attempt to change the order date using a link to this view we see the following in the profiler:-

Now we alter our view to include a timestamp for the Orders table:-

This produces the following trace:-

So we can see Access again using the timestamp.

But this view will also allow the Quantity to be changed, and we are not showing the TIMESTAMP for the [Order Details] table, so we should really alter our view to that shown below:-

This results in the following trace when editing the [Order Details] data:-

If we edit the [OrderDate] part of the [Orders] base data, then we get the following trace:-

So the rule seems to be that when Access sees multiple timestamps it uses the last one as part of the update regardless of which underlying base table is being changed. This is not unreasonable because it would be very difficult for Access to figure out which fields were associated with which underlying base table and which timestamp.

Because the timestamp ensures that two users do not have lost updates when editing the same record, we can see above that using the [Order Details] timestamp when updating data in the [Orders] table is not quite correct.

The only perfect solution here would be not to use the view to update more than one of the base tables (We explained in another article that updates to multiple base tables are not allowed at the same time, but that a view based on multiple base tables can still be updateable as long as each update only affects one underlying base table).

Problems when specifying the wrong unique identifier

If we take the above example and specify the wrong field as the unique record id, then what happens?

The answer is we can get the following undesirable effect:-

When we know the data is as shown below:-

When specifying the Unique Record Identifier it is important to make the correct choice!

Problems when keys are not included

As a final example of where there can be problems, Access will allow a query like the one shown below:-

Access will be able to update both of the underlying base tables, notice that the ProductId is not part of the query. If this was converted to a SQL Server View and you wanted it to be updateable it would be important to add the ProductId to the view.

So there are some types of query in Access to which you may need to add missing key fields to get updateability.

Summary advice

Including a timestamp in an updateable view has benefits, it makes the update more efficient by specifying fewer fields in the WHERE clause, it detects and warns when multiple users try and edit the same data at the same time, and it ensures that any update errors due to converting data values between Access and SQL Server are avoided (these are normally very rare).

Ensure that when specifying a Unique Record Identifier, it is unique.

When using an updateable view involving updates to multiple base tables consider adding the base table timestamp to improve accuracy and efficiency of updates (but as we have seen only one of the timestamps, the last one, will get used), ensure that you understand the consequences of brining through one or more timestamps and how this may impact updates on the underlying base tables.

Adding a timestamp in this special situation could fail to detect concurrency conflicts when users edit data. In our example where the [Orders] table timestamp is used, but the Quantity from the [Order Details] table is changed, we could fail to detect a conflict if another user made a change to the same [Order Details] record. So you may think that when you don’t use a timestamp that the conflicts would be detected, but this is not the case unless the view contained every field from the [Orders Detail] table in which case it would check against every value.

We would recommend when using updateable views involving several base tables that you only update data in one of the underlying tables and bring through the timestamp for that base table.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz

sales@ascassociates.biz