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
> Auto Number Not Correct, Access 2016    
 
   
River59
post Apr 15 2019, 10:46 AM
Post#1



Posts: 1,708
Joined: 7-April 10
From: Detroit, MI


I have been working on a rather complicated and involved procedure that requires me to add a record to a primary table with AutoNumber ID's.
Then I create copies of this record on a temporary table (I have to do bunch of other stuff with these records).

There is then a need for me to delete the original record created on the primary table and append the records from the temporary table to that primary table.

If the last recordID on the primary table is 100 and I add a new one, it is record 101.
When I delete this record 101 and append the records from the temp table, they append as 102, 103, 104, 105, etc.
It wants to skip the recordID of the original record that I wrote to the table!

Why does this happen and is there a way for the records being appended to be AutoNumbered as 101, 102, 103, 104 like they should be?

I know that this is 'supposed' to be the way autonumber works. I question this because if I delete records then compact/repair, the table will advance to the next existing number.
I guess I would like to know if there is another way to do this without having to compact/repair.'


This post has been edited by River59: Apr 15 2019, 11:00 AM

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Apr 15 2019, 10:54 AM
Post#2


Access Wiki and Forums Moderator
Posts: 74,985
Joined: 19-June 07
From: SunnySandyEggo


Hi. This is "normal" behavior for Autonumber fields. If you delete the original record before appending the temp ones, just include the Autonumber field values in your APPEND query to reassign them.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
River59
post Apr 15 2019, 11:02 AM
Post#3



Posts: 1,708
Joined: 7-April 10
From: Detroit, MI


So if the temp table autonumber fields are 103, 104, 106, 107, etc. If I append these to the primary table, it will change these to the correct auto number?
Hmmm, gonna try that ... unless I totally misunderstand what you are saying.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
theDBguy
post Apr 15 2019, 11:03 AM
Post#4


Access Wiki and Forums Moderator
Posts: 74,985
Joined: 19-June 07
From: SunnySandyEggo


Hi. What I was saying is if the original record was 101, then the temp table should also be 101. So when you put it back to the original table, it will still be 101.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2019, 11:06 AM
Post#5


UA Admin
Posts: 34,825
Joined: 20-June 02
From: Newcastle, WA


One additional thing to consider.

The whole POINT to AutoNumbers is exactly what you see here. Each one is supposed to be unique and is supposed to be unique. And, theoretically, they should never be reused. At least, that's the design. If one does get repeated, or reused, that's generally associated with a corrupted table.

If you rely on an unbroken sequence of values for some reason OTHER than to serve as the Primary Keys for a table, then the AutoNumber is not the right choice. There have been a number of discussions of this point over the years, and a number of code examples are available in the code archives to produce such sequential values where appropriate. Here, where it seems to be important to other business purposes to have the unbroken sequence, I'd strongly encourage looking that direction and not trying to preserve AutoNumbers like this.

While it is possible to reuse one, as theDBGuy explains, it is probably better not to do so as a standard practice because of the risk of a clash and possible corruption. I've done so to recover a corrupted record, actually. But it's not something I would do as a common thing otherwise.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
River59
post Apr 15 2019, 11:24 AM
Post#6



Posts: 1,708
Joined: 7-April 10
From: Detroit, MI


I have always followed that principal. The thing that is strange to me is that while building and testing, I add and delete records all day long. They still always continue in correct numbering order.
If I go directly to the table and add a record, the next number is as it should be. If I append a record, then deleted numbers are skipped as it re-numbers the new appended record.
Compacting and repairing restarts the auto numbering in correct order?????



--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2019, 11:41 AM
Post#7


UA Admin
Posts: 34,825
Joined: 20-June 02
From: Newcastle, WA


If you add a record directly to the table, then delete that same record, then add another new record, does THAT actually replace the AutoNumber for that just deleted record?

A compact & Repair can have the result. I don't rely on it.
This post has been edited by GroverParkGeorge: Apr 15 2019, 11:41 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
pere_de_chipstic...
post Apr 15 2019, 12:09 PM
Post#8


UtterAccess Editor
Posts: 10,411
Joined: 8-November 07
From: South coast, England


PMFJI

If you allow the automatic addition of number into an autonumber field then it will ALWAYS add the next number in the sequence regardless of whether the record added previously has been deleted or not. Ie if you add record 101 and 102, the next record added will always be 103, even if record 102 has been deleted.

The only way around this, as DBguy suggests, is to calculate the next record number and append this to the autonumber field with the rest of the record bring added.

A compact and repair will only reset the autonumber field if the table is empty.

As George implies, an autonumber field is really for Access' internal working and you should avoid using an autonumber field for anything that is user meaningful!

hth

--------------------
Warm regards
Bernie
Go to the top of the page
 
River59
post Apr 15 2019, 02:44 PM
Post#9



Posts: 1,708
Joined: 7-April 10
From: Detroit, MI


I usually use max + 1 to number records but this database is what it is.
I got around my problem by figuring out a way to not delete the record so things seem to be doing what they want.

Thanks to all of you for your input!

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd April 2019 - 01:36 AM