Indexed.Yes(DuplicatesOK)

[peljo via AccessMonster.com]

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..

 

[Tim Ferguson]

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 ?

jetSQL = "DROP INDEX MyIndex ON MyTable"
db.Execute jetSQL, dbFailOnError

you'll have to get the name of the index first; if Access created it for
you then you'll need to find it out like this. At least you'll only have to
it once, unless you have any reason to think that it will have been renamed
since you created it.

Public Sub ListIndexes(MyTable As String)

Dim f As Field
Dim ix As Index
Dim db As Database
Set db = CurrentDb()
For Each ix In db.TableDefs(MyTable).Indexes
Debug.Print ix.Name
For Each f In ix.Fields
Debug.Print , f.Name

Next f
Next ix


End Sub


Hope that helps


Tim F