SQL Timestamp field for Access use

On Wed, 31 Dec 2008 18:02:09 -0500, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

>Do not repair what is not broken. If you application is currently working
>well - after correcting the problem with the bit field - I don't see why you
>should start adding the TS field everywhere. You don't have to add it to
>all of your queries as it should be automatically retrieved when you need it
>(if I remember correctly but you should make of this); however, there are
>occasions when adding a TS will give you trouble.

I really respect Sylvain's expertise, but this is one thing we
disagree on. In our shop we add Timestamp (now also called RowVersion
in 2005+) to *every* table when Access will be used as a front-end.
We've done this on probably over a thousand tables in dozens of
databases, and we've never seen a problem with having it.

You don't need to do another thing. The TS field does not need to be
referenced in queries, SQL statements, forms or reports. A side
benefit is that every update will be a bit faster because Access/ODBC
will use the TS field to check concurrency, rather than comparing all
the field values.

However, we *have* seen problems with *not* having it, even when there
was no null bit field. Problems that cannot be tested for because
they involved specific floating point values stored in specific
records.

>For example, take the case where you are editing the fields ID (as the
>primary key), A, B and C on a form or subform and the fields ID, D, E and F
>on another form or subform. The two groups { A, B, C } on one side and { D,
>E, F } on the other should be viewed as independant; however, if the table
>has a TS, these two groups will be interlinked because the real groups will
>be { A, B, C, TS } and { D, E, F, TS }, with TS as the common point.

Sylvain, I don't understand your concern here. What do you mean by
interlinked? In your example, the two forms are editing records in
the same table. Any record that is updated will get a new TS value
assigned by SQL Server. If both forms try to update the same record
at the same time, concurrency will be checked and one of them will get
an error. What am I missing?

Armen Stein
Microsoft Access MVP
[URL="http://www.JStreetTech.com"]www.JStreetTech.com[/URL]
 >> Stay informed about: SQL Timestamp field for Access use 

Thank you!

I'm of the "if it ain't broke, dont fix it" mindset myself, I just wasn't
sure if I was missing out on something.

Quote:
"....however, if the table has a TS, these two groups will be interlinked
because the real groups will be { A, B, C, TS } and { D, E, F, TS }, with TS
as the common point."

Nice! I hadn't thought of it that way. Alot of my data is editted this way,
so I think by adding the TS that I would end up making things more difficult
on myself in the long run (with users calling me EVERY time they receive a
"data has changed" error, no matter how much I reassure them <sigh>).
 >> Stay informed about: SQL Timestamp field for Access use 

"snooka9" wrote in message

> Thank you!
>
> I'm of the "if it ain't broke, dont fix it" mindset myself, I just wasn't
> sure if I was missing out on something.
>
> Quote:
> "....however, if the table has a TS, these two groups will be interlinked
> because the real groups will be { A, B, C, TS } and { D, E, F, TS }, with
> TS
> as the common point."
>
> Nice! I hadn't thought of it that way. Alot of my data is editted this
> way,
> so I think by adding the TS that I would end up making things more
> difficult
> on myself in the long run (with users calling me EVERY time they receive a
> "data has changed" error, no matter how much I reassure them <sigh>).

Exactly, some people make an heavy use of subforms and sometimes these
subforms will point toward the same table; while other people will require
that all subforms point toward different tables, splitting them as
necessary. In the first case, adding a TS will bring heavoc but will leave
unaffected the second case.

In your case, you should make some tests to be sure that you are (or not) in
this situation. Personally, I've just made one with a simple testbed: two
tables, one with a TS and the other without one; two forms, each with two
subforms and the following select statements for the main form and each of
the subforms:

Select IdTable from [TableWithTS]
Select IdTable, A, B, C from [TableWithTS]
Select IdTable, D, E, F from [TableWithTS]

Select IdTable from [TableWithoutTS]
Select IdTable, A, B, C from [TableWithoutTS]
Select IdTable, D, E, F from [TableWithoutTS]

and got the expected result: an error message in the first case and no error
message in the second when I was editing the data and switching between the
subforms.

Notice that even if you don't use two or more subforms pointing toward the
same table, you can still have trouble with adding a TS if you have some
other construct with the capability of changing the value of the TS even if
the referenced fields in a form/subform are left untouched. For example,
you might have some piece of code that will calcule and update some
background value and this calculation could occur in a variety of places;
for example in the OnBeforeUpdate or the OnAfterUpdate event of either a
control or of the form itself. You could also have unbound forms or
subforms (or a mix of bound and unbound forms/subforms) whose updating code
will be affected by this. You could also have some kind of replication or
synchronisation mecanism or some kind of trigger that will affect it or even
or even another type of frontend that might be affected by this or make your
frontend be affected by it.

There is probably no such thing as a totally 100% innocuous change that you
can chose to apply globally to a system without running the risk of breaking
something.

In many cases, this risk might be low or very low but it's never 0% and in
my opinion, the risk of adding a timestamp on hundred of tables on an
existing system in production is far from beeing near the 0% level.

Personally, I had my share in the past of systems who have broken down
because someone has decided to make an *innocuous* change only for the sake
of making changes.
 >> Stay informed about: SQL Timestamp field for Access use 

"Armen Stein" wrote in message

> On Wed, 31 Dec 2008 18:02:09 -0500, "Sylvain Lafontaine" <sylvain aei
> ca (fill the blanks, no spam please)> wrote:
>
>>Do not repair what is not broken. If you application is currently working
>>well - after correcting the problem with the bit field - I don't see why
>>you
>>should start adding the TS field everywhere. You don't have to add it to
>>all of your queries as it should be automatically retrieved when you need
>>it
>>(if I remember correctly but you should make of this); however, there are
>>occasions when adding a TS will give you trouble.
>
> I really respect Sylvain's expertise, but this is one thing we
> disagree on. In our shop we add Timestamp (now also called RowVersion
> in 2005+) to *every* table when Access will be used as a front-end.
> We've done this on probably over a thousand tables in dozens of
> databases, and we've never seen a problem with having it.
>
> You don't need to do another thing. The TS field does not need to be
> referenced in queries, SQL statements, forms or reports. A side
> benefit is that every update will be a bit faster because Access/ODBC
> will use the TS field to check concurrency, rather than comparing all
> the field values.
>
> However, we *have* seen problems with *not* having it, even when there
> was no null bit field. Problems that cannot be tested for because
> they involved specific floating point values stored in specific
> records.
>
>>For example, take the case where you are editing the fields ID (as the
>>primary key), A, B and C on a form or subform and the fields ID, D, E and
>>F
>>on another form or subform. The two groups { A, B, C } on one side and
>>{ D,
>>E, F } on the other should be viewed as independant; however, if the table
>>has a TS, these two groups will be interlinked because the real groups
>>will
>>be { A, B, C, TS } and { D, E, F, TS }, with TS as the common point.
>
> Sylvain, I don't understand your concern here. What do you mean by
> interlinked? In your example, the two forms are editing records in
> the same table. Any record that is updated will get a new TS value
> assigned by SQL Server. If both forms try to update the same record
> at the same time, concurrency will be checked and one of them will get
> an error. What am I missing?

Sorry for my late answer but I chose to take the time to mount a testbed and
try different configurations in order to be sure of what I would say.

In my example, I'm talking about having two subforms on the same form that
will update different fields on the same table; for example:

Select IdTable from [TableWithTS]
Select IdTable, A, B, C from [TableWithTS]
Select IdTable, D, E, F from [TableWithTS]

with the first select statement for the main form and the two other for each
subforms. Of course, probably that we'll get the same result by using a
mainform with only a single subform if they also share different fields from
the same table excerpt for it's primary key but using two subforms make it
easier to understand.

> Armen Stein
> Microsoft Access MVP
> [URL="http://www.JStreetTech.com"]www.JStreetTech.com[/URL]
 >> Stay informed about: SQL Timestamp field for Access use 

Also, I should have mentionned that probably that we'll get the same result
by using a mainform with only a single subform if they also share different
fields from the same table excerpt for it's primary key but using two
subforms make it easier to understand.
 >> Stay informed about: SQL Timestamp field for Access use 

And of course, even without subforms you can still have problem if you are
showing more than a single form on the screen or if different forms are used
by different people concurrently. For example, one person could update one
type of data on the table and another person could update a different set of
columns on the same table at the same time. I've seen that in the past for
an application about sport results.
 >> Stay informed about: SQL Timestamp field for Access use 

"Sylvain Lafontaine" wrote:

> And of course, even without subforms you can still have problem if you are
> showing more than a single form on the screen or if different forms are used
> by different people concurrently.

This is basically my setup. I have 16 Access front-end databases linked to
tables in an SQL back-end and about 20 users at any given time using any
variation of databases. There are many instances where a user can be
editting data on a form that contains certain fields from a table and another
user can be editting data on a form that contains different fields from the
same table so it would definitely be a problem if I were to add a TS to these
tables.

If I get ambitious enough I may go through all my DBs and figure out where a
TS may help, but my company never seems too thrilled with the idea of paying
me to do behind-the-scenes stuff that they can't see the results of first
hand.

Thanks again for all your help.
God Bless.
 >> Stay informed about: SQL Timestamp field for Access use 

>On Wed, 31 Dec 2008 18:02:09 -0500, "Sylvain Lafontaine" <sylvain aei
>ca (fill the blanks, no spam please)> wrote:
>
>>Do not repair what is not broken. If you application is currently working
>>well - after correcting the problem with the bit field - I don't see why you
>>should start adding the TS field everywhere. You don't have to add it to
>>all of your queries as it should be automatically retrieved when you need it
>>(if I remember correctly but you should make of this); however, there are
>>occasions when adding a TS will give you trouble.

>>For example, take the case where you are editing the fields ID (as the
>>primary key), A, B and C on a form or subform and the fields ID, D, E and F
>>on another form or subform. The two groups { A, B, C } on one side and { D,
>>E, F } on the other should be viewed as independant; however, if the table
>>has a TS, these two groups will be interlinked because the real groups will
>>be { A, B, C, TS } and { D, E, F, TS }, with TS as the common point.

Hi Sylvain,

Well, you were right. But of course you already knew that.

We finally did some extensive testing based on your statements and
determined that indeed, Access-SQL Server can do partial-row
concurrency checking. As you indicated, without a Timestamp, fields
A, B, C can be updated independently from fields D, E, F and no
conflict will occur. But adding a Timestamp causes the whole row to
be checked.

I think we avoided this issue because we write our applications so
that we don't have two forms updating the same record concurrently.
However, your point about background processes updating other fields
is well taken. Adding a Timestamp increases the chances of a trigger
or other background update encountering an issue.

So if I may summarize some proposed best practices:

--------------------

- Avoid using floating point numeric fields, because certain floating
point values may fail unexpectedly in the future.

- Avoid using Bit fields, or at least ensure that they always contain
True or False, not Null. If you must include Nulls, you can use a
Small Integer to store True, False or Null.

- Do not include a Timestamp/RowVersion field in the table.

- For all updatable recordsets, include only the fields you need to
update, as those will be the ones that are checked for concurrency.
Changes to other fields will occur independently without causing a
conflict.

- If you can't avoid floating point or Null Bit fields, then adding a
Timestamp/RowVersion field is recommended. However, the negative side
effect to this is that you will lose partial-row concurrency checking,
and instead updates to any field in the row (even fields that are not
part of the recordset) will cause a conflict.

--------------------

Note that KB article [URL="http://support.microsoft.com/kb/280730/"]http://support.microsoft.com/kb/280730/[/URL] presents
adding the Timestamp as one of the resolutions to the conflict
problem, but does not mention the negative side effect.

Interestingly, we also tried similar testing in Access 2003 using a
Jet back-end, and found that Access can perform partial-row
concurrency testing also. This underscores the importance of
including the minimum number of fields in a form's Recordsource
instead of using just the table name or "Select * From ...".

Sylvain, thank you for taking the time to explain this! I've learned
something new that will improve our project standards in the future.

Armen Stein
Microsoft Access MVP
[URL="http://www.JStreetTech.com"]www.JStreetTech.com[/URL]
 >> Stay informed about: SQL Timestamp field for Access use 

"Armen Stein" wrote in message

>
> We finally did some extensive testing based on your statements and
> determined that indeed, Access-SQL Server can do partial-row
> concurrency checking. As you indicated, without a Timestamp, fields
> A, B, C can be updated independently from fields D, E, F and no
> conflict will occur. But adding a Timestamp causes the whole row to
> be checked.
>

Very very interesting. However, the question becomes (ignoring trigger
issues). when you say whole row, is that checking occurring server side
or access side? Now that this issue be brought up, I understand that
including a timestamp field can "COST" some concurrency issue and
ability. However, if that timestamp allows access to do a BETTER job
of not having to check which fields (in fact "any" fields) that
may have changed then we gain on the access side. In other words I
have to dig farther as to what access does when it "checks" that fields
have changed. If we can "skip" the field checking (comparison) step that
jet uses with sql server, then it still going to be beneficial to use a
timestamp in those fields.

> However, your point about background processes updating other fields
> is well taken. Adding a Timestamp increases the chances of a trigger
> or other background update encountering an issue.
>
> So if I may summarize some proposed best practices:
>
> --------------------
>
> - Avoid using floating point numeric fields, because certain floating
> point values may fail unexpectedly in the future.
>

Agreed. Floating point fields don't compare well at all. I not even sure
ms-access can compare the values correctly to determine that a record been
changed....

>
> - Do not include a Timestamp/RowVersion field in the table.
>

I 90% agreeing with this "new" suggestion. However, if ms-access can skip
the field comparison and only use the timestamp field to determine a record
change...I am willing to give up the concurrency gains you mention in place
of less work (less network clatter) by ms-access to determine field changes.
If the timestamp field does NOT help ms-access to reduce checking, then that
last 10% of my doubts are gone and as a future recommend we should not just
shotgun add timestamp fields to the sql tables (and, I am guilty of doing
this).

>
> Interestingly, we also tried similar testing in Access 2003 using a
> Jet back-end, and found that Access can perform partial-row
> concurrency testing also. This underscores the importance of
> including the minimum number of fields in a form's Recordsource
> instead of using just the table name or "Select * From ...".
>

I was actually aware of the above, and it is even outlined in the old jet
"white" performance paper...


> Sylvain, thank you for taking the time to explain this! I've learned
> something new that will improve our project standards in the future.
>
> Armen Stein
> Microsoft Access MVP
> [URL="http://www.JStreetTech.com"]www.JStreetTech.com[/URL]
>

And a big thank you to you also! I just learned something new that the
access community been not too knowledgeable on here (that issue is simply to
always adding a TS column). However, I am still reserving the 10% doubt due
to my above question/issue.

Note that I am not doubting the behavior outlined, I am simply wondering if
the TS column can eliminate the field by field checking, and if yes then
I'll not feel so bad about having a TS column everywhere since then at least
understand the tradeoff between concurrency and less fields having to be
checked for a dirty record...
 >> Stay informed about: SQL Timestamp field for Access use 

Albert D. Kallal wrote:

> Note that I am not doubting the behavior outlined, I am simply wondering if
> the TS column can eliminate the field by field checking, and if yes then
> I'll not feel so bad about having a TS column everywhere since then at least
> understand the tradeoff between concurrency and less fields having to be
> checked for a dirty record...

See:

Optimizing Microsoft Office Access Applications Linked to SQL Server

[URL="http://msdn.microsoft.com/en-us/library/bb188204.aspx"]http://msdn.microsoft.com/en-us/library/bb188204.aspx[/URL]

under "Supporting Concurrency Checks" (about halfway down the page).

James A. Fortune
MPAPoster.Remove DeleteThis @FortuneJames.com

HOMEY - An Englishman. -- Dictionary of Australian Slang, Second
Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)
 >> Stay informed about: SQL Timestamp field for Access use 

"James A. Fortune" wrote in message

> Albert D. Kallal wrote:
>
>> Note that I am not doubting the behavior outlined, I am simply wondering
>> if the TS column can eliminate the field by field checking, and if yes
>> then I'll not feel so bad about having a TS column everywhere since then
>> at least understand the tradeoff between concurrency and less fields
>> having to be checked for a dirty record...
>
> See:
>
> Optimizing Microsoft Office Access Applications Linked to SQL Server
>
> [URL="http://msdn.microsoft.com/en-us/library/bb188204.aspx"]http://msdn.microsoft.com/en-us/library/bb188204.aspx[/URL]
>
> under "Supporting Concurrency Checks" (about halfway down the page).
>

**Excellent** ...this very close to what I had read in the past. How this
works is even more clear now...thanks for posting that link.

I bookmarked the above Optimizing article. In fact I had not seen that
article before. I note the date is late 2006 (about around the last time
been reading on sql articles). I have to say that link is quite rather full
of good reading.
 >> Stay informed about: SQL Timestamp field for Access use 

On Mon, 6 Apr 2009 02:47:50 -0600, "Albert D. Kallal"
wrote:

>"James A. Fortune" wrote in message

>> Albert D. Kallal wrote:
>>
>>> Note that I am not doubting the behavior outlined, I am simply wondering
>>> if the TS column can eliminate the field by field checking, and if yes
>>> then I'll not feel so bad about having a TS column everywhere since then
>>> at least understand the tradeoff between concurrency and less fields
>>> having to be checked for a dirty record...
>>
>> See:
>>
>> Optimizing Microsoft Office Access Applications Linked to SQL Server
>>
>> [URL="http://msdn.microsoft.com/en-us/library/bb188204.aspx"]http://msdn.microsoft.com/en-us/library/bb188204.aspx[/URL]
>>
>> under "Supporting Concurrency Checks" (about halfway down the page).
>>
>
>**Excellent** ...this very close to what I had read in the past. How this
>works is even more clear now...thanks for posting that link.
>
>I bookmarked the above Optimizing article. In fact I had not seen that
>article before. I note the date is late 2006 (about around the last time
>been reading on sql articles). I have to say that link is quite rather full
>of good reading.

It's a great article, and seems to recap some of the ideas Mary
Chipman has talked about from the book she wrote with Andy Baron
(Microsoft Access Developer's Guide to SQL Server).

However, the section Supporting Concurrency Checks does not mention
the downside of adding timestamps, namely the loss of partial-row
concurrency checking that Sylvain pointed out. We have some large
databases where we have denormalized fields that are calculated by
triggers or scheduled jobs. Having partial row concurrency checking
could help in these situations.

Albert, you're right that there's a purported performance improvement
in the field comparison when a timestamp is added to the table,
because the timestamp can be used instead of comparing all the fields.
But I haven't seen a performance test on this, so I don't know how
significant the improvement is. Anyone want to try it? Say on a
table with 20 fields or so, thousands of updates, both with and
without a timestamp field.

Armen Stein
Microsoft Access MVP
[URL="http://www.JStreetTech.com"]www.JStreetTech.com[/URL]
 >> Stay informed about: SQL Timestamp field for Access use 

"Albert D. Kallal" wrote in

> "James A. Fortune" wrote in message


>> See:
>>
>> Optimizing Microsoft Office Access Applications Linked to SQL
>> Server
>>
>> [URL="http://msdn.microsoft.com/en-us/library/bb188204.aspx"]http://msdn.microsoft.com/en-us/library/bb188204.aspx[/URL]
>>
>> under "Supporting Concurrency Checks" (about halfway down the
>> page).
>
> **Excellent** ...this very close to what I had read in the past.
> How this works is even more clear now...thanks for posting that
> link.
>
> I bookmarked the above Optimizing article. In fact I had not seen
> that article before. I note the date is late 2006 (about around
> the last time been reading on sql articles). I have to say that
> link is quite rather full of good reading.

It's filled with all sorts of good things. Perhaps they were all
covered in Mary Chipman's book, but it was so long ago that I read
that, and when I had so little experience with SQL Server that it
probably never sunk in.

A really amazing thing in that article for me is the revelation that
you can run DDL against a table link to change the index. I've
always known there's a lot of metadata stored in a linked table
definition, but this is the first time I've read that you can
manipulate any of it in a useful way. I'll have to do some poking
around to see what I can learn about that.
 >> Stay informed about: SQL Timestamp field for Access use