December 12, 2008 at 1:26 pm
I have setup an Identity column with an increment of 1 on a table. We are using an Access 2007 form to enter record data into that table, and once saved, it will automatically show the number assigned to the record. However, it somehow jumped 4 numbers today when we saved a new record. What would cause this? Is there a way to script in the missing records? Any help will be appreciated.
December 12, 2008 at 1:32 pm
If you attempt an insert into a table with an identity column and the insert fails the identity value is incremented. That's because the identity value is incremented BEFORE the insert takes place and is not part of any roll back.
As far as replacing the missing identity values there is a way to do that but I wouldn't recommend it. If you need to be guaranteed sequential values in that column you should not use identity.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 12, 2008 at 2:45 pm
Is there any other way around this? Maybe some code in the Access form? Why would you not recommend populating the lost data. What is the script to insert in case we decide that's the way we want to go? Thanks for all your help.
December 12, 2008 at 2:57 pm
bpowers (12/12/2008)
Is there any other way around this?
There is no way around this that I am aware of. It is the way Identity works
Maybe some code in the Access form?
If you are going to do this you should just generate your own sequential numbers which I suggested anyway.
Why would you not recommend populating the lost data.
First of all because you initially need to have the code to determine which values are available. Then because you can't know if it was because there was an error on insert or of someone intentionally deleted the value. Then because it means running the Set Identity_Insert tablename On command and you need to remember to turn it off too and if you forget to turn it off you could open yourself up to some issues.
Basically if you use Identity you typically live with this issue and if for some reason you MUST have sequential values you write custom code to do it as that is usually safer than they trying to "rig" identity.
What is the script to insert in case we decide that's the way we want to go?
It's more work than it's worth to provide the script. As I said above the "hard" part is determining the available key value then you need to Set Identity_Insert
On, do the insert, Set IDentity_Insert
Off.
Again, I would recommend just leaving it alone. It is highly unlikely you will run out of identity values so leave it as is.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 12, 2008 at 4:00 pm
Ok you convinced me. What is the easiest way to convert this back to a standard column rather than an identity column?
December 12, 2008 at 4:14 pm
My goal wasn't to steer you away from using Identity, just from trying to fill in gaps.
Anyway I'd use SSMS to make the change. Just open the table in Design mode and set identity to off/falsek and sace the change.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 12, 2008 at 4:56 pm
Just to echo what Jack was trying to convey: Why do your Identity/Key values have to be sequential with no gaps? There is rarely a reason good enough to justify the very real costs of trying to do this.
The most efficient and cost-effective approach is almost always to use Identities and just live with the gaps. Those gaps are a direct result of the speed, safety and ability to scale that Identities give you. All other approaches cost significantly more (in terms of hours/effort) and must sacrifice one or more of those advantages.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 12, 2008 at 5:05 pm
Ok thanks. Is there a way I can keep users from entering duplicate records now that identity is turned off? If I have two users in the form at once how do I keep them from using the same number? Your help is greatly appreciated.
December 12, 2008 at 6:02 pm
you can set a unique index, but that only prevents it, it doesn't help the users.
I'd turn identity on, and not worry about gaps in the sequence.
December 12, 2008 at 6:05 pm
bpowers (12/12/2008)
Ok thanks. Is there a way I can keep users from entering duplicate records now that identity is turned off? If I have two users in the form at once how do I keep them from using the same number? Your help is greatly appreciated.
It will be easier to answer this if you answer my question: Why do you need a key sequence with no gaps?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 14, 2008 at 12:32 pm
After discussing the record keeping process with our QA manager, we concluded that sequential numbering is not a requirement. I am just one of those picky people who would rather visually see sequential numbers. I also worried about explaining the missing record numbers to auditors in the future. I am going to leave the identity turned on and just move forward. I appreciate everyone's help and guidance..
December 14, 2008 at 1:29 pm
I think that most of us have been there at one time or another and it usually takes a little bit to get over the idea that we will get gaps, but that it is not really a problem. Granted, having to explain it all the time (to auditors, users, bosses, etc.) is a pain though.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply