Results 1 to 3 of 3

Thread: SSMA_Timestamp

  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there,

    I'm in the process of coverting an Access 2007 DB to SQL Server 2008 and have been testing the Microsoft Sequel Server Migration Assistant 2008 for Access tool. For the most part this has worked quite well in converting the tables, but one of the default properties (which I can switch off) is the "Add timestamp columns" facility which as it says adds a Timestamp field to certain tables.

    I'm in the dark as to why the SSMA does this and would there be a problem if I switched it off? One reason for switching this off is the Front end falls over when inserting new records to a certain table (A recordset loops through all the fields adding data picked from an entry form). I would rather stop this before the event than having to remove this field from all the tables it has been added to after if it serves no useful purpose.

    Thanks and regards

    Niven

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The addition of the timestamp is useful if you are using Access as the front-end to your SQL Server database using ODBC linked tables. It cures a problem that occurs where people get a message that a newly edited record has been edited by another person. That problem occurs most frequently on subforms linked to tables or queries that are or use SQL Server linked tables. This MSDN Article provides some valuable background. See in particular this section:
    Supporting Concurrency Checks
    Probably the leading cause of updatability problems in Office Access–linked tables is that Office Access is unable to verify whether data on the server matches what was last retrieved by the dynaset being updated. If Office Access cannot perform this verification, it assumes that the server row has been modified or deleted by another user and it aborts the update.

    There are several types of data that Office Access is unable to check reliably for matching values. These include large object types, such as text, ntext, image, and the varchar(max), nvarchar(max), and varbinary(max) types introduced in SQL Server 2005. In addition, floating-point numeric types, such as real and float, are subject to rounding issues that can make comparisons imprecise, resulting in cancelled updates when the values haven't really changed. Office Access also has trouble updating tables containing bit columns that do not have a default value and that contain null values.

    A quick and easy way to remedy these problems is to add a timestamp column to the table on SQL Server. The data in a timestamp column is completely unrelated to the date or time. Instead, it is a binary value that is guaranteed to be unique across the database and to increase automatically every time a new value is assigned to any column in the table. The ANSI standard term for this type of column is rowversion. This term is supported in SQL Server.

    Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

    The SQL Server Migration Assistant for Office Access automatically adds a column named SSMA_TimeStamp to any tables containing data types that could affect updatability.

    Note that data types that cause updatability problems can also cause problems when included in keysets. If Office Access fails to find a matching value on the server, it assumes that the row has been deleted. When choosing unique identifiers, pick columns with values that can be matched reliably.
    If the timestamp on a particular table creates issues, then I would remove it, but in general we put a timestamp on all SQL Server tables that we link to.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wendell,

    Many thanks your reply. Food for thought there.

    It turns out the SSMA_Timestamp field wasn't the cause of the Front End failing!

    I'll post a fresh thread to get to the bottom of why a procedure in the front end isn't working.

    Regards

    Niven

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •