Re Indexes in table design 1

[figgis]

I have a diary application which I am trying to speed up, have combined some fields in a multiple index ie Date and Name.

Question is when using muliple indexes what use is the Index Name and when is it used, and does Access automaticaly use this new index or do i have to call it somehow.

 

[Steve101]

You can use the index explicity, using the SEEK statement. For example, here is a snippet from the online help (I've doctored the bottom line a bit)

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
' You must open a table-type Recordset to use an index,
' and hence the Seek method.
Set rstProducts = _
dbsNorthwind.OpenRecordset("Products", dbOpenTable)

With rstProducts
' Set the index.
.Index = "PrimaryKey"
.Seek "=", Val("SomeValue&quot

Most of the time though, indexes will be automatically used when available, by the Access query optimiser; check online help on optimisation for further details.

Generally though, set up indexes on fields which are used to join tables in a query, or used as constraints (ie. in the WHERE clause) to improve performance. Note that there are exceptions so this is only a general statement.

Regards,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[wtmckown]

http://support.microsoft.com/default.aspx?scid=kb;en-us;98793

http://support.microsoft.com/default.aspx?scid=kb;en-us;112112