indexed property

[peljo via AccessMonster.com]

What is wrong with my function to change index ? I want to change the indexed
property of the field delays from the table visits but i do not know am i
doing this properly.Could you help ? The field delays has the indexed
property set to Indexed Yes(Duplicates OK) and i want to remove it with the
function below
Public Function Cur()

Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim idx As DAO.Index
Set tdf = dbs.TableDefs("visits")

Set idx = tdf.CreateIndex("delays")

idx.Unique = False
tdf.Indexes.Append idx

dbs.Close
Set prp = Nothing
Set idx = Nothing
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Function

 

[Allen Browne]

From your example and explanation, I assume you want to change an existing
index so that its Unique property is Yes?

I don't think you can change the property, so you will need to remove the
existing index, and create another one. The steps will be:
1. Locate and delete any existing index(es) on this field.
2. Create the new index.

Let's clarify what you are doing here. Could you just open the table in
design view, and change the property in the lower pane? If you cannot do
that, presumably the actual database is at some other location. Can you know
what the name of the index is? The index might be called "delays", or it
might have a different name (e.g. if the field name has been changed, or
that name is already in use.) Additionally, there could be more than one
index on the field. (Since it it NOT a unique index, the complication of
enfored relationships that depend on the index does not arise.)

Step 1: Identify and delete existing index(es) on the field
========================================
This code illustrates how to iterate the indexes on the field, and the
field(s) in the index. You can modify this to identify names of the indexes
where the Count is 1 (only one field) and the fld.Name is "delays" (indexes
on just this field.) You can then use the Delete method of the table's
Indexes collection to remove the index.

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(ind)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(ind)
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Step 2: Create the new index:
=====================
The code to create the new index with the properties set the way you want
will be similar to what you already have, but you need to append the
field(s) to the index before you append the index to the table's Indexes
collection. The example below illustrates how to do that:

Sub CreateIndexDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

Set db = CurrentDb()
Set tdf = db.TableDefs("visits")

Set ind = tdf.CreateIndex("delays")
ind.Fields.Append ind.CreateField("delays")
tdf.Indexes.Append ind
tdf.Indexes.Refresh

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub