Using SQL TimeStamp datatype field in conjunction with Access 2003 linked ODBC tables
Posted on 2011-05-05
I am looking for a more detailed explanation of the pros and cons of using a TimeStamp datatype field on any SQL tables that are being accessed via Access 2003 ODBC linked tables.
Here is what I think I know:
1) TimeStamp field is used to minimize unneccessary locks on data being updated.
2) TimeStamp is actually a binary value used as a form of row versioning.
3) Considered 'best practice' in an Access-based system.
Here is what I don't know:
1) Is there any additional client-side 'housekeeping' tasks that need to be attended to when adding the TimeStamp field to the tables?
2) What will be the effect of adding this field to pre-existing tables containing data? I am presuming that a DROP/CREATE w/ data operation is recommended to update the TimeStamp field for each row.
3) Any 'gotchas' that I am not able to ask a question about because, well...I don't know about them. :)
The impetus behind this is a locking problem we ran into when using floating point fields in a few tables. I contacted tech support at Microsoft and they said this can result from the row version differing due to floating point rounding errors between Access and SQL. This results in a write conflict.
Any info is welcome. I just need a reliable,safe, and accurate way of saving floating point values to SQL tables via an Access 2003 FE.