Editing tables linked from Access to SQL-Server

[Harry V]

On 10/6/2004 Chicago Dave asked about updating linked tables and Steve
Jorgensen replied that adding a timestamp column to the table will allow
Access to figure out whether a record has been changed.

I'm wondering what the impact of a timestamp column has on a database.

Should we consider adding a timestamp column to a) all tables, including
domain or lookup tables that rarely change - if at all, b) the tables that
are sometimes added to as well as the main data or transcational tables, or
c) just the tables that are managed through applications?

We have applications that allow for entry and edit of "field" data, but,
from time to time, there are management functions that require the use of
temporary employees to access the database for data maintenance and it's much
easier to train them through the use of Access' query designer than T-SQL,
even through SQL Server Express for the short period of time they are here.

 

[Paul Shapiro]

The timestamp is a binary field which is equivalent to a row update
sequence number, unique across the entire database. If it exists, Access can
use it as part of its optimistic locking scheme. When Access updates a row,
it wants to verify that the data hasn't changed since you last read the
data. If there is a timestamp, it just has to verify the current timestamp
value matches the value you last read, since any update to a row, even if no
data changes, will increment the timestamp. If you don't have a timestamp
field, Access compares the data in each field to ensure nothing has changed.

So the bottom line is no, you don't get any benefit from a timestamp in
tables that rarely change. Yes, it can be a benefit in updating tables with
many fields and tables which you are frequently updating in Access.

 

[Harry V]

Thanks Paul.

I'll see if that doesn't do the trick.

 

[Harry V]

That did work, thank you Paul

I'm adding a timestamp column to any table with the growth potential for
1,000 or more records.

 

[Paul Shapiro]

Glad to hear it's working. I just noticed the part of the original post
where you mentioned having temporary employees sometimes doing data entry.
It sounded like they're using the Access query designer for the data entry.
Forms would make training easier and the data entry more reliable. If part
of the original issue was Access not allowing data updates, make sure you
have a primary key defined for all tables. Access won't update SQL Server
tables that don't have a PK.