• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

Using SQL TimeStamp datatype field in conjunction with Access 2003 linked ODBC tables

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.
0
kkamm
Asked:
kkamm
  • 2
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<1) TimeStamp field is used to minimize unneccessary locks on data being updated.>>
 
    Never have heard that, although it may be true.  The whole point of the timestamp field is to give Access (JET) the ability to determine if a row has changed.

<<2) TimeStamp is actually a binary value used as a form of row versioning.>>

  Yes.

<<3) Considered 'best practice' in an Access-based system.>>

  Yes, becuase in most cases, it's the only thing that works<g>.

<<1) Is there any additional client-side  'housekeeping' tasks that need to be attended to  when adding the TimeStamp field to the tables?>>

   No.

<<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.>>

   You'll need to update existing rows with a value.

<<3) Any 'gotchas' that I am not able to ask a question about because, well...I don't know about them. :)>>

  None really.   Adding the timestamp field as you state allows for row versioning.  If it exists, Access (JET) can use this as a unique key for each record.  It needs this because it uses a keyset cursor model to handle recordsets.

 JimD.
0
 
kkammAuthor Commented:
Jim,

Thanks for the info.

I already have single field (autonumbered) PK identity columns defined - does using the TimeStamp field require that it be part of the PK in order to be effective, or will the fact that it reflects a different value after each UPDATE operation be enough to let Jet know there was a version change on the row?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I already have single field (autonumbered) PK identity columns defined - does using the TimeStamp field require that it be part of the PK in order to be effective, or will the fact that it reflects a different value after each UPDATE operation be enough to let Jet know there was a version change on the row? >>

  Nope, doesn't need to be part of the PK, just needs to exist.

JimD.
0
 
kkammAuthor Commented:
Verified this with a test:

 Created two linked tables in test MDB: One with timestamp, one without. PK is still the autonumber identity column and does not include timestamp column. Floating point field in each table. Loaded up separate instances of the test MDB on 2 workstations and logged in as different users. Tried updating the float field on the same row of each linked table, respectively, at the same time. Timestamped table did not throw 'Drop Changes/Copy To Clipboard' exception, whereas non-timestamped table did upon trying to commit the update. Same error as I was receiving in the production environment. :)

Cool. I love solutions that I can see in action.

Thanks for the info.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now