How do I find the indices on a table in Access 2003?

  • Thread starter Thread starter esharris
  • Start date Start date
E

esharris

I am trying to find an index on a table.

I inherited a MS Access 2003 database containing a table. If I add a row to
that table whose XXX field value is not unique to that table, I get the
following error message.

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."

The XXX fields is not the primary key; I checked the table's design view. So
I assume the problem is in some index.

How do I find the related index?
Thank You.
 
Easiest way is to open the table in design view, then select Indexes from
the View menu.

When the "Index Name" field is blank, the fields listed are part of the last
value shown for Index Name.

Put the cursor on each unique Index Name, and look in the Unique box in the
bottom left-hand corner.
 
I am trying to find an index on a table.

I inherited a MS Access 2003 database containing a table. If I add a row to
that table whose XXX field value is not unique to that table, I get the
following error message.

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again."

The XXX fields is not the primary key; I checked the table's design view. So
I assume the problem is in some index.

How do I find the related index?
Thank You.
Click to expand...

Here's a little routine that will list them all in the Immediate window; you
could adapt it to print them out or store them in a table if you wish.

Sub ShowAllIndices()
Dim db As DAO.Database
Dim tdf As TableDef
Dim idx As Index
Dim fld As Field
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
Debug.Print tdf.Name
For Each idx In tdf.Indexes
Debug.Print Tab(5), idx.Name
For Each fld In idx.Fields
Debug.Print Tab(10), fld.Name
Next fld
Next idx
End If
Next tdf
End Sub
 
esharris said:
I inherited a MS Access 2003 database containing a table. If I add a row to
that table whose XXX field value is not unique to that table, I get the
following error message.
Click to expand...

Now that unique index might be a very useful one. Interesting problems
might happen with some queries.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top