Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Modify an index properties

Posted on 2000-04-11
12
Medium Priority
?
260 Views
Last Modified: 2008-03-06
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).
0
Comment
Question by:tusken
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2703636
Hello tusken,

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
0
 
LVL 3

Expert Comment

by:davereynolds
ID: 2703659
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.mdb")
      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
0
 
LVL 10

Accepted Solution

by:
paasky earned 400 total points
ID: 2703670
Here's same with Objects:

Public Function AlterIndex(IndexName As String, TableName As String, FieldNames As String, Optional Unique As Boolean)
Dim MyIndex As Index

    CurrentDb.TableDefs(TableName).Indexes.Delete IndexName
    Set MyIndex = CurrentDb.TableDefs(TableName).CreateIndex(IndexName)
    With MyIndex
        .Fields = FieldNames
        .Unique = Unique
        .Primary = False
        .IgnoreNulls = False
        .Required = False
    End With
    CurrentDb.TableDefs(TableName).Indexes.Append MyIndex
   
End Function

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 10

Expert Comment

by:paasky
ID: 2703685
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,Field2",False)

? AlterIndex ("Test","Table1","Field1;Field2",False)

Paasky
0
 

Author Comment

by:tusken
ID: 2704802
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??



0
 
LVL 10

Expert Comment

by:paasky
ID: 2704831
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
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704835
(Access names the index same name as field by default)
0
 

Author Comment

by:tusken
ID: 2704836
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??



0
 
LVL 10

Expert Comment

by:paasky
ID: 2704843
Unique = False -> Duplicates OK
Unique = True -> Duplicates are not allowed
0
 

Author Comment

by:tusken
ID: 2705314
Hey passky!
i have to go for today, i'll check that tomorrow and let you know how it goes...

0
 

Author Comment

by:tusken
ID: 2708094
Thanks!
0
 
LVL 10

Expert Comment

by:paasky
ID: 2708118
Happy to help you tusken.

Best regards,
Paasky
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question