Changing primary keys

[ksammie01]

Ok, so i have a database, with very many tables, forms and queries.
In the initial database design, i used the id auto generated, which auto increments, as the primary key, and then created all the forms and queries, and these all work fine.
The needs have changed, and now i want to change the primary key in one of the tables, from the auto id, to something else, still unique though.
Am not quite sure, what the effect of this will be on the forms and queries.
Is it ok for me to change the primary key?

[RuralGuy]

I would have to ask why? You can have any field a unique/no duplicates without it being a PrimaryKey.

[ksammie01]

Ok, maybe i should throw more light on the database application. Its for an organization that supports children. the children have sponsors.
so, the sponsors were first identified by the auto generated key, then the organization wants now to use the sponsors PRN(payment reference number), which the sponsors use to send the money, as the primary key.
hope it explains why i want to change the sponsors primary key from the auto key to the prn number.

[RuralGuy]

You can still use the "sponsors PRN" without changing the PrimaryKey. The two will probably go hand in hand.

[orange]

As Allan has said, these are really independent things.

If you want a field for your or sponsors' use to identify records, then put in the field with a unique index.

Access is quite content with the unique autonumber as PK. These things can coexist.

An old rule says --never change the PK. There have been cases where PKs (built upon some local identifier) have had to be changed because the scope of the database at design time, or through merger etc, was underestimated. That is why many use an autonumber ( or sequence in some dbms) as the table's PK.

Good luck with your project.