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.

The indexes are displayed with the table in Design View:

View >>> Indexes
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"esharris" <(E-Mail Removed)> wrote in message
news:1E1BA8D1-37EC-4B70-AD48-(E-Mail Removed)...
>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.

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


"esharris" <(E-Mail Removed)> wrote in message
news:1E1BA8D1-37EC-4B70-AD48-(E-Mail Removed)...
>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.

On Wed, 12 Nov 2008 09:11:03 -0800, esharris
<(E-Mail Removed)> wrote:

>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.

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
--

John W. Vinson [MVP]

esharris <(E-Mail Removed)> wrote:

>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.

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/