MS-Access / Getting Started

---

Timestamps and Row Versioning

When you use a software tool to convert an Access database to SQL Server, you are normally provided with an option to add a TIMESTAMP column to your tables (it cannot be over-emphasized how important this option is when working with Access linked to SQL Server), and when you create new tables in SQL Server, you should remember to add a TIMESTAMP column to each table. Most of the current documentation from Microsoft no longer talks about a TIMESTAMP data type but instead discusses a ROWVERSION data type. However, Management Studio only displays a TIMESTAMP data type and not a ROW VERSION data type; you can regard both as meaning the same thing.

When you edit data over a linked table to SQL Server, Access maintains in memory a "Before" image of your data, and once you are ready to save the record, it needs to write the data back to SQL Server by using the Before image to detect any conflict in the data caused by another user simultaneously editing and saving changes to the same record. This standard product feature opens a write-conflict dialog box when a conflict is detected.

Access can use one of two strategies for detecting a conflict during the write-back. If you ignore TIMESTAMPs, Access checks that the values in the Before image for every field matches the current data held in the row in SQL Server. It does this by using a WHERE clause on the Update command, as shown in the following:

WHERE fld1 = fld1 (old value) and fld2 = fld2 (old value)

The following example illustrates how you can see the trace output from the SQL Server Profiler when you perform an update on the Customers table linked to Northwind:

exec sp_executesql N'UPDATE "dbo"."Customers" SET "ContactTitle"=@P1  WHERE "CustomerID"
= @P2 AND "CompanyName" = @P3 AND "ContactName" = @P4 AND "Contact-
Title" = @P5 AND "Address" = @P6 AND "City" = @P7 AND "Region" IS NULL AND
"PostalCode" = @P8 AND "Country" = @P9 AND "Phone" = @P10 AND "Fax" = @P11',N'@
P1 nvarchar(30),@P2 nchar(5),@P3 nvarchar(40),@P4 nvarchar(30),@P5 nvarchar(30),@
P6 nvarchar(60),@P7 nvarchar(15),@P8 nvarchar(10),@P9 nvarchar(15),@P10 nvarchar(
24),@P11 nvarchar(24)',N'Sales Agent',N'ANATR',N'Ana Trujillo Emparedados
y helados',N'Ana Trujillo',N'Owner',N'Avda. de la Constitucion 2222',N'Mexico
D.F.',N'05021',N'Mexico',N'(5) 555-4729',N'(5) 555-3745'

The problem with this strategy is that if you have 100 fields, then the WHERE clause includes 100 comparisons, so this update involves a lot of comparisons. The other danger is when working with floating-point numbers; for example, if there were any difference in the precision with which the numbers were held in the two products, then the update could fail to locate the target record, causing the update to incorrectly register a conflict.

The second strategy is to use a TIMESTAMP as soon as you add this column to a table, and then ensure that the linked table is refreshed. Access switches automatically to this second strategy, which involves verifying that the primary key is matched and the TIMESTAMP column is unchanged, as shown here:

WHERE pkey1 = pkey1 (old value) and TS = TS (old value).

If you now add a TIMESTAMP column called TSCustomers to the Customers table, and then refresh the linked table and repeat the update, you obtain the following output in the Profiler:

exec sp_executesql N'UPDATE "dbo"."Customers" SET "ContactTitle"=@P1 WHERE
"CustomerID" = @P2 AND "TSCustomers" = @P3',N'@P1 nvarchar(30),@P2 nchar(5),@P3
binary(8)',N'Owner',N'ANATR',0x0000000000000FA2

This second method has the advantage of fewer comparisons and no dependencies on the form of internal storage of data in the products (with the exception of the primary key). The TIMESTAMP column data is automatically generated by SQL Server and changed whenever a record is changed. If you want to see this in greater detail, you can use the SQL Server Profiler to monitor what happens under the hood.