Editing Primary Key in Split Database

[jaypoppin]

I've inherited the management of a relatively complex database (~15 related tables, union queries, &c.) that hinges on a few basic primary keys.



I have split the database, and distributed the front end client to other users.

The most important primary key is that assigned to each client. It takes the numerical form XXXX-XXX. Typically, the final three digits are 001. When the database was in its infancy, many of the first entries skipped these last three digits, and just entered XXXX-.

It is now necessary to go back and make sure all entries are complete. They must have the last three digits. I tried going into the back end, and editing them straightforwardly, however, it tells me that it is 'related' and cannot be edited.

Is there a way to edit these primary keys and have them push out to all the clients and forms etc?

Much obliged,

Jay

[RuralGuy]

In the Relationship screen, edit the Relationship and turn on cascading updates.

[jaypoppin]

I just tried that, and chose "cascade update related fields," instead of "cascade delete related records."

When I tried, I received the following error:

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

Should I try, "cascade delete related records"?

[RuralGuy]

Should I try, "cascade delete related records"?

The simple answer here is NO! How many tables have this PK as a FK in them?

[jaypoppin]

It is the primary key on one table, and sits on 5 other tables (one of which is a union query, not a table).

[RuralGuy]

FYI, queries are just a string of SQL and some strategy. They take up very little room. So that makes 4 tables with this PK as a FK. I think it can be fixed with queries and appends.

[jaypoppin]

FYI, queries are just a string of SQL and some strategy. They take up very little room. So that makes 4 tables with this PK as a FK. I think it can be fixed with queries and appends.

How would I go about that?

[RuralGuy]

How many records do not have the trailing 3 numbers in the PK field?

[jaypoppin]

Roughly 20% of maybe 500.

[KathyL]

I've inherited the management of a relatively complex database (~15 related tables, union queries, &c.) that hinges on a few basic primary keys.

I have split the database, and distributed the front end client to other users.

The most important primary key is that assigned to each client. It takes the numerical form XXXX-XXX. Typically, the final three digits are 001. When the database was in its infancy, many of the first entries skipped these last three digits, and just entered XXXX-.

It is now necessary to go back and make sure all entries are complete. They must have the last three digits. I tried going into the back end, and editing them straightforwardly, however, it tells me that it is 'related' and cannot be edited.

Is there a way to edit these primary keys and have them push out to all the clients and forms etc?

Much obliged,

Jay

Your database is small. I manage one with hundreds of tables, queries, forms, etc. I've lost count. Live, 24/7. I don't split the database, because I find it slows down processing by the client.
Primary keys must be unique in terms of data for each record. You should be able to edit the data, and it's stored in just the one table. I don't know what you mean by "push out to all the clients". The clients should not have data at their local PC. I also do not use Microsoft's relationship model, it causes too many development issues/restrictions and wastes my time. I code (using VB) any edits to provide integrity to the data.

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

When you get this error, it means exactly that. You have two or more records with identical key values. Create a temporary query looking for duplicates to find them.

Kathy, I.T. Consultant and Computer Science Instructor

[RuralGuy]

Though I do not agree with much of what KathyL said, the issue with the error is valid. Are you *certain* you are changing the PK to a unique value in *all* of the tables?