Jet 4.0 and 3.5 (and earlier versions) cluster on the Primary Key and a
Compact will keep it managed.

Indeed a clue to this is the Registry entry for:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engi nes.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes

both contain the setting CompactByPKey.

I am not sure what would happen if you changed the above setting from 1, I
expect 0 would skip the clustering - I am not sure if any other setting
would be valid.

SQL Server generally clusters on the Primary Key, however, you can select
another index.

AutoNumbers are very poor devices to truly define a unique record in the
real world, You can enter the name John Doe 1,000,000 times in your database
if the Primary Key is an AutoNumber and you have failed to do something to
prevent the creation of 1,000,000 John Doe's. You may have 1,000,000 unique
records but so what?

Recommending the AutoNumber as Primary Key without pointing out the dangers,
and suggesting the definition and declaration of the natural key (should one
exist), is unwise.

BTW A clustered index is merely a physical ordering of the records in a
table in the database file. Using the true natural key (should one exist) as
the primary key will ensure that all the records with a similar PK will be
physically located next to each other. Using an AutoNumber (sequential
order) as PK will mean the records are clustered according to their creation
order. Using IDENTITY and AutoNumber as PK defeats the purpose of PK, this
is not so bad in SQL Server as it allows you to choose something more
sensible if you have an IDENTITY field in use as PK.


--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"BruceM" wrote in message
...
That you disagree with somebody does not make that person wrong. Roger
has provided a wide range of assistance in this forum, and has made
samples available on his web site. Based on his track record I would be
inclined to follow his advice. If you are trying to convert people to the
idea of using clustered indexes, a very basic discussion of what they are
would be most helpful. I have taken your suggestion to look at Google
groups. There is indeed a lot of discussion, but I have not yet found how
I would create a clustered index if I wanted to. My databases with a few
thousand records seem to work just fine. Why would I want to put extra
effort into something that already works well? I know you have posted
code that includes MAKE TABLE or some such, but the utility of such code
is not clear. The other thing I noted in Google groups is that most of
the discussion of clustered indexes seems to be in discussions about SQL
server.

wrote in message
oups.com...

Roger Carlson wrote:
Autonumber fields make excellent Primary Keys.

You've misunderstood what PRIMARY KEY means. An unique integer which
has no meaning in respect fo the entities being modelled makes a lousy
PRIMARY KEY. Google for "clustered index" in the Access groups.

An autonumber is a convenient uniqueifier but unquieness for its own
sake make not be such a good thing.