Index names

[Max]

Hi,
Does anyone know how to return the name of a field index in an access
database from within the front end?
I have code to programmatically delete fields in a table to achieve design
changes but the index name varies and I need to check this before deleting
the field.

Thanks in advance,

Max

 

[Douglas J. Steele]

Using DAO, it would be something 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()
Set tdfCurr As dbCurr.TableDefs("MyTable")
For Each idxCurr In tdfCurr.Indexes
Debug.Print idxCurr.Name & " contains the following fields:"
For each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
Next idxCurr

 

[Max]

Thanks very much for such a prompt response.
This helps, but for my scenario I know the table name and the field name and
I just want to return a single index name that I can then use in a DROP
INDEX command. Could I impose on you one more time for guidance?

dbs.Execute "DROP INDEX " & strIndexName & " ON " & strTblName & ";"

Thanks,
Max

 

[Douglas J. Steele]

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

Set dbCurr = CurrentDb()
Set tdfCurr As dbCurr.TableDefs("MyTable")
For Each idxCurr In tdfCurr.Indexes
dbCurr.Execute "DROP INDEX [" & idxCurr.Name & "] ON MyTable",
dbFailOnError
Next idxCurr

will drop them all. If you only want to drop that index/those indices that
contains your known field, try:

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

Set dbCurr = CurrentDb()
Set tdfCurr As dbCurr.TableDefs("MyTable")
For Each idxCurr In tdfCurr.Indexes
For each fldCurr In idxCurr.Fields
If fldCurr.Name = "MyField" Then
dbCurr.Execute "DROP INDEX [" & idxCurr.Name & "] ON MyTable",
dbFailOnError
End If
Next fldCurr
Next idxCurr

 

[Max]

Thanks a million.
It worked like a dream, I take my hat off to your genius once again.
Max

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

Set dbCurr = CurrentDb()
Set tdfCurr As dbCurr.TableDefs("MyTable")
For Each idxCurr In tdfCurr.Indexes
dbCurr.Execute "DROP INDEX [" & idxCurr.Name & "] ON MyTable",
dbFailOnError
Next idxCurr

will drop them all. If you only want to drop that index/those indices that
contains your known field, try:

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

Set dbCurr = CurrentDb()
Set tdfCurr As dbCurr.TableDefs("MyTable")
For Each idxCurr In tdfCurr.Indexes
For each fldCurr In idxCurr.Fields
If fldCurr.Name = "MyField" Then
dbCurr.Execute "DROP INDEX [" & idxCurr.Name & "] ON MyTable",
dbFailOnError
End If
Next fldCurr
Next idxCurr


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks very much for such a prompt response.
This helps, but for my scenario I know the table name and the field name
and I just want to return a single index name that I can then use in a
DROP INDEX command. Could I impose on you one more time for guidance?

dbs.Execute "DROP INDEX " & strIndexName & " ON " & strTblName & ";"

Thanks,
Max