tusken
asked on
Modify an index properties
Hi there, i want to modify the "indexed" property of a field. I want to be able to do that in a function with code. I know how to change a field type and size but i don't see how modify this property from yes(no duplicates) with yes(duplicates OK).
Hi tusken,
There seems to quite a bit of info about this in the Help section (TableDefs~Indexes). I've pasted an example of setting the Unique property.
This example sets the Unique property of a new Index object to True, and appends the Index to the Indexes collection of the Employees table. It then enumerates the Indexes collection of the TableDef and the Properties collection of each Index. The new Index will only allow one record with a particular combination of Country, LastName, and FirstName in the TableDef.
Sub UniqueX()
Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxNew As Index
Dim idxLoop As Index
Dim prpLoop As Property
Set dbsNorthwind = OpenDatabase("Northwind.md b")
Set tdfEmployees = dbsNorthwind!Employees
With tdfEmployees
' Create and append new Index object to the Indexes
' collection of the Employees table.
Set idxNew = .CreateIndex("NewIndex")
With idxNew
.Fields.Append .CreateField("Country")
..Fields.Append .CreateField("LastName")
.Fields.Append .CreateField("FirstName")
.Unique = True
End With
.Indexes.Append idxNew
.Indexes.Refresh
Debug.Print .Indexes.Count & " Indexes in " & _
.Name & " TableDef"
' Enumerate Indexes collection of Employees table.
For Each idxLoop In .Indexes
Debug.Print " " & idxLoop.Name
' Enumerate Properties collection of each Index
' object.
For Each prpLoop In idxLoop.Properties
Debug.Print " " & prpLoop.Name & _
" = " & IIf(prpLoop = "", "[empty]", prpLoop)
Next prpLoop
Next idxLoop
' Delete new Index because this is a demonstration.
.Indexes.Delete idxNew.Name
End With
dbsNorthwind.Close
End Sub
HTH
There seems to quite a bit of info about this in the Help section (TableDefs~Indexes). I've pasted an example of setting the Unique property.
This example sets the Unique property of a new Index object to True, and appends the Index to the Indexes collection of the Employees table. It then enumerates the Indexes collection of the TableDef and the Properties collection of each Index. The new Index will only allow one record with a particular combination of Country, LastName, and FirstName in the TableDef.
Sub UniqueX()
Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxNew As Index
Dim idxLoop As Index
Dim prpLoop As Property
Set dbsNorthwind = OpenDatabase("Northwind.md
Set tdfEmployees = dbsNorthwind!Employees
With tdfEmployees
' Create and append new Index object to the Indexes
' collection of the Employees table.
Set idxNew = .CreateIndex("NewIndex")
With idxNew
.Fields.Append .CreateField("Country")
..Fields.Append .CreateField("LastName")
.Fields.Append .CreateField("FirstName")
.Unique = True
End With
.Indexes.Append idxNew
.Indexes.Refresh
Debug.Print .Indexes.Count & " Indexes in " & _
.Name & " TableDef"
' Enumerate Indexes collection of Employees table.
For Each idxLoop In .Indexes
Debug.Print " " & idxLoop.Name
' Enumerate Properties collection of each Index
' object.
For Each prpLoop In idxLoop.Properties
Debug.Print " " & prpLoop.Name & _
" = " & IIf(prpLoop = "", "[empty]", prpLoop)
Next prpLoop
Next idxLoop
' Delete new Index because this is a demonstration.
.Indexes.Delete idxNew.Name
End With
dbsNorthwind.Close
End Sub
HTH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There's a little difference in parameter passing if the index contains more than one field.
With first example use commas (,) and second semicolon (;) to separate different fields.
? AlterIndex ("Test","Table1","Field1,F ield2",Fal se)
? AlterIndex ("Test","Table1","Field1;F ield2",Fal se)
Paasky
With first example use commas (,) and second semicolon (;) to separate different fields.
? AlterIndex ("Test","Table1","Field1,F
? AlterIndex ("Test","Table1","Field1;F
Paasky
ASKER
Hey paasky, you've remember i like the object version better!!!! ;-)
One thing, i do the change with the field, the code goes by without an error but i does not change anything....
My table have 2 Primary key cNoCSST and noDossier, does that change something??? I just want to change the indexed property of the field cNoCSST. If i want to pass from (no duplicate) to (duplicate OK) i just have to set the unique property to false, Right??
One thing, i do the change with the field, the code goes by without an error but i does not change anything....
My table have 2 Primary key cNoCSST and noDossier, does that change something??? I just want to change the indexed property of the field cNoCSST. If i want to pass from (no duplicate) to (duplicate OK) i just have to set the unique property to false, Right??
yes tusken, I remembered you love objects :-)
This should work assuming your cNoCSST field index name is same as field name (check that in Indexes window in table design view -> View|Indexes)
? AlterIndex ("cNoCSST","YourTableName" ,"cNoCSST" ,False)
Your two field PK is probably named as "PrimaryKey".
regards,
Paasky
This should work assuming your cNoCSST field index name is same as field name (check that in Indexes window in table design view -> View|Indexes)
? AlterIndex ("cNoCSST","YourTableName"
Your two field PK is probably named as "PrimaryKey".
regards,
Paasky
(Access names the index same name as field by default)
ASKER
Hey paasky, you've remember i like the object version better!!!! ;-)
One thing, i do the change with the field, the code goes by without an error but i does not change anything....
My table have 2 Primary key cNoCSST and noDossier, does that change something??? I just want to change the indexed property of the field cNoCSST. If i want to pass from (no duplicate) to (duplicate OK) i just have to set the unique property to false, Right??
One thing, i do the change with the field, the code goes by without an error but i does not change anything....
My table have 2 Primary key cNoCSST and noDossier, does that change something??? I just want to change the indexed property of the field cNoCSST. If i want to pass from (no duplicate) to (duplicate OK) i just have to set the unique property to false, Right??
Unique = False -> Duplicates OK
Unique = True -> Duplicates are not allowed
Unique = True -> Duplicates are not allowed
ASKER
Hey passky!
i have to go for today, i'll check that tomorrow and let you know how it goes...
i have to go for today, i'll check that tomorrow and let you know how it goes...
ASKER
Thanks!
Happy to help you tusken.
Best regards,
Paasky
Best regards,
Paasky
Here's Drop-and-create function to change table index:
Public Function AlterIndex(IndexName As String, TableName As String, FieldNames As String, Optional Unique As Boolean)
CurrentDb.Execute ("DROP INDEX " & IndexName & " ON " & TableName)
CurrentDb.Execute ("CREATE " & IIf(Unique, "UNIQUE", "") & " INDEX " & IndexName & " ON " & TableName & "(" & FieldNames & ")")
End Function
Hope this helps,
Paasky