Clustered Indexes

[Lyle Fairfield]

'Property Clustered As Boolean
'Member of DAO.Index

Private Sub IsThereaClusteredIndex()
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
For Each tdf In DBEngine(0)(0).TableDefs
For Each idx In tdf.Indexes
Debug.Print tdf.Name, idx.Name, idx.Primary, idx.Clustered
Next idx
Next tdf
End Sub

(For Jet), under what conditions will idx.Clustered be true?

--
Lyle Fairfield

[Tom van Stiphout]

On Wed, 02 Aug 2006 01:59:48 GMT, Lyle Fairfield
<ly***********@aim.comwrote:

Put this code at the top of your procedure:

Set tdf = DBEngine(0)(0).TableDefs("Customers")
Set idx = tdf.CreateIndex("testClustered")
With idx
.Clustered = True
.Fields.Append .CreateField("CustomerID")
End With
tdf.Indexes.Append idx

-Tom.

>'Property Clustered As Boolean
'Member of DAO.Index

Private Sub IsThereaClusteredIndex()
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
For Each tdf In DBEngine(0)(0).TableDefs
For Each idx In tdf.Indexes
Debug.Print tdf.Name, idx.Name, idx.Primary, idx.Clustered
Next idx
Next tdf
End Sub

(For Jet), under what conditions will idx.Clustered be true?

[david epsom dot com dot au]

From my 97 help (The Access 2000 help is broken again)

---------
Some IISAM desktop database formats use clustered indexes.

· Microsoft Jet databases ignore the Clustered property because the
Microsoft Jet database engine doesn't support clustered indexes.
· For ODBC data sources the Clustered property always returns False; it does
not detect whether or not the ODBC data source has a clustered index.
----------

A clustered index is one where the physical order of the data matches the
index order. You do this so that when you want related records, they are
physically close to each other: 'clustered', (or to spread related records
if you are using page locking) To help achieve this, you pack the table with
free space, so that records can be entered into the correct position.

For those old ISAM databases, you wanted to specify the cluster index, and
you wanted to use the cluster index when walking through the table.

Actually, a Jet 3/4 primary key index is a kind of clustered index, but with
no free space, so the only way to correct the physical order to match the
index is to compact the database.

SQL Server allows you to specify the free space ratio, so that the clustered
data remains correctly clustered according to the cluster index as you add
new data. Also, SQL Server lets you specify separate primary key and cluster
indexes.

Access, as a relational database, didn't use clustered indexes. But in fact
there was still a physical order, so it is more correct to say just that
Access didn't allow you control the clustering. One of the changes between
Jet 1 and Jet 3 was to allow you to specify (the default) that the physical
record order would be the primary key index order. But records are still
added in time sequence, only re-ordered to primary key order on compact.
The mixed order after data entry is the reason 'first' and 'last' don't work
any more. (But actually, 'first' and 'last' don't work anyway because of
anomalies in the jet query compiler).

(david)
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:Xn*********************************@216.221.8 1.119...

'Property Clustered As Boolean
'Member of DAO.Index

Private Sub IsThereaClusteredIndex()
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
For Each tdf In DBEngine(0)(0).TableDefs
For Each idx In tdf.Indexes
Debug.Print tdf.Name, idx.Name, idx.Primary, idx.Clustered
Next idx
Next tdf
End Sub

(For Jet), under what conditions will idx.Clustered be true?

--
Lyle Fairfield