Indexed property

[peljo]

Indexed.Yes (Duplicates OK).

In my code for remote control i wanted to remove the property Indexed of a field, but i receive the date type conversion error.How can i change these properties by code ?
At the end of the list of the properties of the field,just below Required, it is written Indexed.Yes (Duplicates OK). i want to turn it to No and i write False, but it says property is unkown. What is the exact property and the command to turn it to No ?

Below is a part of my code
Set tdf = dbs.TableDefs("customers")
Set fld = tdf.Fields("afid")
fld.Properties("Indexed") = False
dbs.Close
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Set wsp = Nothing

etc

The Access interface is not the same as the DAO object model. I know I will have to loop through the Indexes collection of the TableDef, and loop through the Fields collection of each index to see if the field is among them.
And if so, to delete the index. Of course, it is much easier to delete the index in the Access interface,but for the time being I cannot do that, I have no access to the Access interface and i will have to send the code and beside I have about 7 users in different towns that makes the matter difficult to me.
Is it possible to help me writing out the code for that? I will be much indebted..

 

[RoyVidar]

First, I don't think this is exposed through the field object, I think you need the index object. Also, I don't think you can change these properties, I think you need to delete the index.

Say you have a tabledef td, and idx as dao.index, fld as dao.field then

    Dim td As DAO.TableDef
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim idx As DAO.Index
    Dim l As Long
    
    Set db = CurrentDb
    Set td = db.TableDefs("MyTable")
    For l = td.Indexes.Count - 1 To 0 Step -1
        Set idx = td.Indexes(l)
        Debug.Print idx.Name, idx.Primary, idx.Unique, _
                    idx.IgnoreNulls, idx.Required
        For Each fld In idx.Fields
            Debug.Print fld.Name
            If fld.Name = "MyField" Then
                td.Indexes.Delete idx.Name
                Exit For
            End If
        Next fld
    Next l

Note that since one might be removing items from the collection one iterate, one will need to start "at the end" and move to the start (just thrown together, you'll probably need some testing/errorhandling...).

Should you wish to (re)create the index, perhaps look up the .CreateIndex method.