Please help me change the primary key data type or other possible solutions

[Grady]

Confirm whether or not you have a split --frontend/backend-- database. This is a critical structural issue to avoid/reduce corruption. It is more than a good idea.
Do you have a separate development/testing area for working with the revisions you anticipate? You can design and test/verify possible approaches before committing to a specific implementation.
Good luck. Let us know how things progress.

Thanks for this reply Orange; I apologize for my tardiness in getting back to you. I have learned a lot about our database through this process and have a better understanding of its limitations now. It is an old replicated DB and no, it isn't split, nor do I believe it could be split until the replication is removed from it. I understand the benefit of splitting the DB now and aspire to do that in the future so we can for instance, as you say, "design and test/verify possible approaches before committing to a specific implementation". Thanks for all of you suggestions in this thread, they were very helpful.

[Grady]

The satellite files have UI and tables? Exactly how are you synchronizing satellite and main files? If this is not split design and you do revise tables in the main file and then 'synchronize', will surely corrupt the data.

June7, Thanks for this reply. Yes, we have had so many problems with corruption of data through synchronizing that we have essentially stopped using that "feature". The design master lives on our network drive and we have a single replica on a dedicated work station that we were synchronizing periodically to the master. We stopped synchronizing individual user replicas to the master to avoid these issue. All of our changes need to take place in the design master and no-one can use the database while those changes are taking place. This can be very frustrating. As I said, we aspire to getting things updated, including un-replicating the DB and then splitting it so we can move into the modern era ;-) Thanks for your input.

[Grady]

I responded to a pm to assist by looking at the db, which is far too large to post here even if zipped. It has 47 tables (but I think I lost some in the conversion process), 239 queries, 82 forms and 90 reports. Concentrating on the issue at hand, the basic solution recommended was to
- copy the table and open in design view
- remove the autonumber field (as shown in a previous post, all existing entries of concern were ascending and consecutive numbers)
- save and switch to datasheet view and add the needed values
- open again in design view and reinstate the autonumber PK field with random option

To keep me from making a long story about the db, I'll just say that it's a replication db thus the need for random autonumber. I saw no evidence of this field being used as meaningful user data; just being related to tblInterview in the customary fashion. Thus I still don't see the importance of the PK autonumber field being sequential. Any place that the number is observed in use might actually be the InterviewTypeNumber and not the PK field - they just happen to have the same values.

Regardless, I've proposed the above solution. It must be noted that the only reason the approach could be taken is that every current InterviewType value was the same as the existing autonumber (e.g. type 3 was ID 3). It seems the behaviour of a random autonumber field added to existing data is to make the values sequential at first.

Micron,

When I used your approach it did have the desired outcome of allowing me to use sequential numbers in the PK field. Thank you for putting the time into helping us sort this out!

I realize it was a weird request, because I am aware that a primary key ought not to have value apart from linking fields in different tables. One thing I haven't mentioned before, is, due to my lack of ability (both due to inexperience and the fact that making changes to the design master takes our DB out of use) I have been querying the DB and using excel pivot tables to generate reports. My pivot tables and formulas have used the sequential PK as a meaningful value. Also, I believe I mentioned in a thread above, that the when looking at the VBA code, which does a series of manipulations to the database when we progress a particular farm from one stage to the next, my understanding (or lack there of) led me to believe that it was incrementing a variable that corresponds with the value of the PK. The majority of the other tables in the DB were switched (prior to my involvement) to having replication IDs as the PK value, however this table was not. This is additional evidence that the original developer used this PK value in a manner that wasn't technically correct. Regardless, thank you ever so much for your effort towards helping me with this problem.

All the replies to my original post were thoughtful and astute. I will consider my original question solved. If I decide to tackle the bigger systemic issues with our DB, I will start a new thread. Thanks again everyone!