SQL Timestamp field for Access use

[snooka9]

I have mutliple (Office XP) Access databases linked to tables in an SQL
Server 2005 database.

I was searching the newsgroup boards for info on a write conflict message I
was getting and I came across alot of suggestions to other people to add
timestamp fields to ALL of the SQL tables being linked in Access. (It turned
out that one of my tables had a "bit" field that allowed nulls and had no
default value, which caused the error in Access because it saw the field as
False, not null). I have a few questions on the timestamp field:

1) Is that all there is to the solution, or do I also have to add the
timestamp fields to all of my queries and recordsets where data can be edited?

2) Are there any negative side effects of adding a timestamp field to each
of my 130+ tables in SQL?


Thanks.

 

[Sylvain Lafontaine]

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.

Another common source for trouble will be the use of triggers.

If you don't know exactly what your database is made of, adding TS
everywhere without an in-depth checking first could put you into trouble.
However, if you have a good knowledge of the structure of the database and
that you don't foresee any problem with the use of TS fields; yo can easily
add them to see what happens. Later, if you ever run into some trouble,
they will be easy to remove. Just don't forget to refresh all the links
after any change to any table's structure.

 

[Armen Stein]

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
www.JStreetTech.com

 

[snooka9]

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

 

[Sylvain Lafontaine]

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.

 

[Sylvain Lafontaine]

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.


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.

 

[Sylvain Lafontaine]

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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.

 

[Sylvain Lafontaine]

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

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.

 

[snooka9]

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.

 

[Armen Stein]

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 http://support.microsoft.com/kb/280730/ 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
www.JStreetTech.com

 

[Albert D. Kallal]

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:

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
www.JStreetTech.com

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

 

[James A. Fortune]

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

http://msdn.microsoft.com/en-us/library/bb188204.aspx

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

James A. Fortune
(e-mail address removed)

HOMEY - An Englishman. -- Dictionary of Australian Slang, Second
Edition, Sidney J. Baker, 1943 (Price: Three shillings & sixpence)

 

[Albert D. Kallal]

See:

Optimizing Microsoft Office Access Applications Linked to SQL Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

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.

 

[Armen Stein]

**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
www.JStreetTech.com

 

[David W. Fenton]

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

 

[David W. Fenton]

Optimizing Microsoft Office Access Applications Linked to SQL
Server

http://msdn.microsoft.com/en-us/library/bb188204.aspx

Albert is right in saying what a great article that is. I'm just
halfway through it and have already learned a lot (mostly about why
the way I retrieve data is the correct way to do it, i.e., never
retrieve the whole table, which is, of course, proper advice for a
Jet back end, too). I have a question about this, though:

When you create an ODBC-linked table, Office Access looks first
for a clustered unique index.

Now, I have an app that has people in it. I have the table clustered
on Lastname/Firstname. It has a long integer (i.e., integer in SQL
Server) PK. I retrieve data from that table one of three methods:

1. selecting on Lastname only

2. selecting on Lastname and Firstname

3. selecting on the PK field.

The data is always presented sorted in Lastname/Firstname order, and
that's why I clustered on that, hoping it would save a sort.

Is this a problem? There is no clustered unique index on the table
at all, so based on the quote above, am I slowing things down? It
doesn't seem so -- retrieval from a table with 350K rows is
instantaneous.

The table is almost never retrieved as a whole -- the only time that
happens is when I'm mucking around viewing a table datasheet (and I
have noticed that it's much slower to get to the end of the table
than with a Jet back end, and now I know why). When the users are
using the app, data is always requested with criteria as listed
above (or in certain reports where the criteria are on a joined
table that is a child of the main table described above).

Have I made a mistake with the clustering on Lastname/Firstname?

 

[David W. Fenton]

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.

Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

 

[Tom Wickerath]

Hi David,

A long time ago I remember asking at a PNWADG (Pacific NW Access Developer's
Group) meeting if we needed to include the timestamp field in queries in
order for Access to make use of it. I was told "no" by a member of the
Access Development Team, who was present at the meeting.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/

__________________________________________


Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

 

[Sylvain Lafontaine]

Isn't this problem avoided by leaving the timestamp field out of
your SELECT statement? That is, if Access doesn't retrieve the value
of the timestamp, it's not going to be able to use it, right?

Nope, as far as I can remember, Access will still pull out the timestamp
value and use for controling the updating even if it's not part of the
Select statement.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Brendan Reynolds]

Albert is right in saying what a great article that is. I'm just
halfway through it and have already learned a lot (mostly about why
the way I retrieve data is the correct way to do it, i.e., never
retrieve the whole table, which is, of course, proper advice for a
Jet back end, too). I have a question about this, though:

When you create an ODBC-linked table, Office Access looks first
for a clustered unique index.

Now, I have an app that has people in it. I have the table clustered
on Lastname/Firstname. It has a long integer (i.e., integer in SQL
Server) PK. I retrieve data from that table one of three methods:

1. selecting on Lastname only

2. selecting on Lastname and Firstname

3. selecting on the PK field.

The data is always presented sorted in Lastname/Firstname order, and
that's why I clustered on that, hoping it would save a sort.

Is this a problem? There is no clustered unique index on the table
at all, so based on the quote above, am I slowing things down? It
doesn't seem so -- retrieval from a table with 350K rows is
instantaneous.

The table is almost never retrieved as a whole -- the only time that
happens is when I'm mucking around viewing a table datasheet (and I
have noticed that it's much slower to get to the end of the table
than with a Jet back end, and now I know why). When the users are
using the app, data is always requested with criteria as listed
above (or in certain reports where the criteria are on a joined
table that is a child of the main table described above).

Have I made a mistake with the clustering on Lastname/Firstname?

You could create a clustered, unique index including the three fields,
Lastname, Firstname, and PK, and see if it makes a difference?