Index names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Max - 07 May 2005 09:41 GMT

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

Reply to this Message

Douglas J. Steele - 07 May 2005 10:17 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
> Does anyone know how to return the name of a field index in an access
[quoted text clipped - 6 lines]
>
> Max

Reply to this Message

Max - 07 May 2005 11:27 GMT

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

> Using DAO, it would be something like the following untested air code:
>
[quoted text clipped - 22 lines]
>>
>> Max

Reply to this Message

Douglas J. Steele - 07 May 2005 13:44 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 32 lines]
>>>
>>> Max

Reply to this Message

Max - 07 May 2005 14:47 GMT

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
[quoted text clipped - 62 lines]
>>>>
>>>> Max

Reply to this Message