UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Primary Key Not Assigning Correct Value, Access 2016    
 
   
mmchaley
post Feb 7 2018, 04:28 PM
Post#1



Posts: 25
Joined: 26-April 16



I have a clients and contacts form

I have a clients table and a contacts table.

Until a week ago things were working.

Now, I am getting primary key conflicts in my contacts table

I have 108 records and the final record has a ContactID of 111

When I attempt to add a new record, it assigned a primary key (autonumber, incermental) of 14. Trying again, it stepped to 15, then 16.

I don't have a "hole" in my auto number until 31.

This is a split database, I tested this directly in the back end portion, attempting to add a record to the Contacts table directly. I have compacted and repaired the database

What causes this, and how do I fix it? Can it be fixed without deleting the primary key and resetting it?

Thanks,

Mark
Go to the top of the page
 
orange999
post Feb 7 2018, 04:52 PM
Post#2



Posts: 1,856
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Mark,

See this re autonumbers.
Good luck.

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Feb 7 2018, 05:15 PM
Post#3


UA Admin
Posts: 33,509
Joined: 20-June 02
From: Newcastle, WA


"Now, I am getting primary key conflicts in my contacts table"

Are you actually getting conflicts that prevent saving new records, or are you merely seeing the impact of having some AutoNumbers allocated but not saved? The link provided explains how AutoNumbers work. However, your question does seem to indicate there might be more going on.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
redpenner
post Feb 7 2018, 06:05 PM
Post#4



Posts: 193
Joined: 16-July 10
From: Shelton, WA


Is your primary key still showing as Indexed (no duplicates)? If not, it sounds like the table is corrupted. I had this happen with the same symptoms you describe - Access was assigning autonumbers that were duplicates because the indexing was no longer in effect.
Go to the top of the page
 
mmchaley
post Feb 7 2018, 08:50 PM
Post#5



Posts: 25
Joined: 26-April 16



I have it set up as no duplicate and indexed

The problem with doing the auto-renumber is I will have to re-assign the contacts to companies and projects

Go to the top of the page
 
GroverParkGeorge
post Feb 8 2018, 11:29 AM
Post#6


UA Admin
Posts: 33,509
Joined: 20-June 02
From: Newcastle, WA


I'm confused I think.

Are you saying that you now have records in related tables with Foreign Keys that should reference a Primary Key in the Parent table, but don't?

How did that happen? Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 10:39 AM