By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Finding an index in an Access DB

P: n/a
At some point an index has been added to a table in my Access DB that
does not allow duplicates. I can't see where I ever put an index on
this field.

But, when I tried to add 2nd records with a duplicate value in the 2nd
field (not the primary key field) I get an error saying "The changes
you requested to the table were unsuccessful because they would create
duplicate values in the index, primary key or relationship."
When I open the table in design mode the "Indexed" property on the
field says "No"
1. How do I find the name of this index?
2. Can I delete it through the Access menus or do I use SQL statements?

Best regards,
John

May 18 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
In table design view, open the Indexes dialog (View menu.)
If the index is multi-field, you will see it there, but not in the
properties of the field itself.

Indexes can also be hidden. If you need to find it programmatically, this
should do it:

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(ind)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(ind)
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function IndexDescrip(ind As DAO.Index) As String
'Purpose: Return a string describing the characteristics of the index.
Dim strOut As String 'String to concatenate to.
Const strcSep = ", " 'Separator between items.

If ind.Primary Then
strOut = strOut & "Primary" & strcSep
End If

If ind.Foreign Then
strOut = strOut & "Foreign" & strcSep
End If

If ind.Clustered Then
strOut = strOut & "Clustered" & strcSep
End If

If ind.Unique Then
strOut = strOut & "Unique" & strcSep
End If

If ind.Required Then
strOut = strOut & "Required" & strcSep
End If

If ind.IgnoreNulls Then
strOut = strOut & "Ignore nulls" & strcSep
End If

'Return the string without the trailing separator.
If strOut <> vbNullString Then
IndexDescrip = Left$(strOut, Len(strOut) - Len(strcSep))
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<jh***@datalyzer.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...
At some point an index has been added to a table in my Access DB that
does not allow duplicates. I can't see where I ever put an index on
this field.

But, when I tried to add 2nd records with a duplicate value in the 2nd
field (not the primary key field) I get an error saying "The changes
you requested to the table were unsuccessful because they would create
duplicate values in the index, primary key or relationship."

When I open the table in design mode the "Indexed" property on the
field says "No"

1. How do I find the name of this index?
2. Can I delete it through the Access menus or do I use SQL statements?

May 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.