I just recently had to switch our office system from Office 2007 to 2010. The Access database will not allow me to enter data in the table or the form. I receive the following 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"

I have never had this problem and the information that I am entering is all new information..... HELP!!!!! Please

IronAngel,

(IMG:style_emoticons/default/welcome2UA.gif)

A good place to start would be to list your table's fields, particularly noting any fields that are set up with unusual Index properties. Go ahead and open up your table in Design View, check each field, and note which ones have something besides "No" next to the "Indexed" property. You may be surprised to spot the problem as you're doing this, so don't skip this step. (IMG:style_emoticons/default/smile.gif)

If possible, list your fields along thse lines:

CustomerID [Primary Key, Autonumber]
strFirstName
strLastName
strSocialSecurityNumber [Indexed, No Duplicates]
lngStateID [Foreign Key to tblStates.StateID]
etc.

From there, we may be able to help you find which field(s) are being duplicated by your data entry.

Hope this helps,

Dennis

Thanks Dennis!

So what you are saying is that each field should have Yes(Duplicates OK) . . My primary key is based on the social security number.

Thanks

I tried changing the first name field to allow duplicates in the index and it told me:


This error can be caused by one of the following:

The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.
The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.
An expression is not specified in the Expression property of a calculated field.
If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.

If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:

Well DON'T edit the registry! That would be swatting flies with a sledgehammer.

I suspect that this database has been subject to many rounds of tweaking, right? Have you Compacted and Repaired it? If so, or if that doesn't help, try creating a new, empty database and Importing everything from this one (to get a clean set of system tables).

Ironangel,

No, what I'm saying is that you should LOOK at your table's fields, and LIST them here in a post. If any of the fields have Indexing, make a note next to that field's name, as I did in my example.

In other words: Don't Change Anything Yet. Show us what you currently have, so we can better help you with specific instructions.

Dennis

Well said, John.

This is a very good example of a really BADLY written error message.

Ironangel,

In the world of databases, you should NEVER have a table with more than a couple dozen fields, AT MOST. Any more than that, and your table design IS the problem, not the Windows registry. Just list the fields in your table (including any indexing), and we'll go from there.

As I've said many times over the years, the table structure of a database is like the foundation to a house. If the tables aren't set up well, the rest of the database will have problems. If the table design is sound, everything else should fall into place without too much fuss.

Dennis