Find and Remove Index with Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I loop through all the tables in my Access 2000 database and locate
all indexes based upon the index type ("Yes (No Duplicates)", for example)
and whether the field is "Required"?

Further, having found such an index, how can I set it to an index type of
"No"?
 
Bill said:
How can I loop through all the tables in my Access 2000 database and locate
all indexes based upon the index type ("Yes (No Duplicates)", for example)
and whether the field is "Required"?

Further, having found such an index, how can I set it to an index type of
"No"?
Click to expand...


What a goofy thing to do??

Anyway, using DAO, you can loop through the database's
TableDefs collection to get to each TableDef object.

Then you can loop through the TableDef's Indexes collection
to get to each Index object.

Check each Index's Unique propert for True.

At this point, your question is unclear because the Required
property is not supported for an Index object. I suppose
you might want to then loop through the Index object's
Fields collection to see if any(?) or all(?) of the
corresponding TableDef field objects have the Required
property, but that may or may not make sense in some cases.

Once you find an index that you want to delete, use the
Delete method to remove the index from the Indexes
collection..
 
Indexes can have more than one field in them, so saying that an index is
Required means that all fields in the index must have values.

What you can do is use DAO or ADOX.

To use DAO in Access 2000, you need to add a reference. With any code module
open, select Tools | References from the menu bar, scroll through the list
of available references until you find the one for Microsoft DAO 3.6 Object
Library, and select it. If you're not going to be using ADO, uncheck the
reference to Microsoft ActiveX Data Objects 2.1 Library

Once you've do that, you can use code like the following untested air-code:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
For Each idxCurr in tdfCurr.Indexes
If idxCurr.Required = True And idxCurr.Unique = True Then
' You've found the index. You cannot change its Unique property, though:
you'll have to
' delete it and recreate it with the appropriate properties.
End If
Next idxCurr
Next tdfCurr
 
Back
Top