SQL Timestamp and Access

The timestamp field in a SQL Server table holds a unique value that is updated every time a record is updated. In a recent post in a SqlServerCentral forum, there was a suggestion that when you are using MS-Access as the front-end, you should add a timestamp column to every linked table - and thus prevent write-conflict messages.

If you use the SQL Server Migration Assistant (SSMA) to convert your Access database, it will automatically add a column SSMA_timestamp to every table that has either a floating-point field (Single or Double) or has a Boolean field that allows nulls. This is supposed to help SQL Server manage updates to records. For more details you can refer to the Microsoft article Move Access data to a SQL Server database by using the Upsizing Wizard.

Now, if you want to follow the original suggestion, but some tables already have a timestamp, you could write a script to generate the update scripts, like this:

SELECT
'ALTER TABLE [' + OBJECT_NAME(t.object_id) + ']
ADD my_timestamp TIMESTAMP'
FROM sys.tables t
WHERE NOT EXISTS
(
SELECT 1
FROM sys.columns
WHERE system_type_id =
(
SELECT system_type_id
FROM sys.types
WHERE name = 'timestamp'
)
AND object_id = t.object_id
)
AND t.type = 'U'
ORDER BY 1

...and when you run this, you get one ALTER TABLE statement for each table that does not have a timestamp column. NOTE that this does not change the tables, it only creates the scripts to make the table changes. Then, you can review the scripts & remove any tables (e.g. staging or import tables) where adding a column might break the application. This also documents the changes being made. When you actually execute the script, it will add the timestamp columns as needed.

(BTW in the statement "ADD my_timestamp TIMESTAMP" I've left out the "NOT NULL" clause, simply because the timestamp data type by definition cannot be NULL.)

On the other hand, if you take a look at SQL Timestamp field for Access use there is a discussion of how the timestamp can actually cause write-conflicts if you have a form / subform combination that uses columns from the same table, which is not an uncommon practice.

So, before you make radical changes to your database, remember to backup everything, and run some test cases including measuring response times - this gives you a baseline after which you can apply the mods & evaluate the results.