How to update ID (1 Viewer)

MK1999

New member
Local time
Today, 14:34
Joined
Mar 30, 2022
Messages
24
hello,
i have a table with a relationship with other tables.. i want to update all table records id to be in order (it is already autonumber but i needed to delete some records so there is a gap) i dont want to keep the gap
is there is anyway code to update all the records to be in order?
 

plog

Banishment Pending
Local time
Today, 06:34
Joined
May 11, 2011
Messages
11,613
Why? And you are doing it wrong.

Autonumber primary keys sole purpose is to maintain a relationship between tables. Who cares if they are "pretty"? You are trying to solve a problem that doesn't exist.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Jan 23, 2006
Messages
15,364
Autonumbers (from a no longer accessible UtterAccess thread)

What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers.
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table.
12. Indicative of or related to any TimeStamp field that may also be in the table row.

What they are: !!
1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access.
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row
 

MK1999

New member
Local time
Today, 14:34
Joined
Mar 30, 2022
Messages
24
the problem is that the other tables have incorecct infomation because the number is diffrenet from this table to the others
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
27,001
First, that is a mis-use of the autonumber capability. IF you actually need numbering to be contiguous AND it has to be that way after a record gets deleted, you are doing something that Access won't do for you. You will have to code this up for yourself. An autonumber is an internal feature of Access (and most other DB's have a similar ability) to give you a unique - but not actually predictable - record identifier. If you are changing PKs in a database that has relationships based on those PKs then they should not have been PKs in the first place. A PK is, by most definitions, a value that does not change for the lifetime of the record, used solely to identify the record, and having NO OTHER MEANING.

If you are doing this for some fiduciary requirement or some other business issue, there are ways to generate a "temporary local" sequence number that would look like the same thing as you are requesting - but the real PK would still be the autonumbers with gaps as they occur. Further, if you have inter-table relationships (and you said you did) then you are looking at a massive set of queries for something that is cosmetic in nature.

It is, of course, your DB, so we have to take at face value that you think you want this. However, from the comments you are getting, I think you can see that we have a problem with the request. So let's do this another way. WHY do you think you need this? What is the business reason? Or it is really just a matter of cosmetics?
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Jan 23, 2006
Messages
15,364
I appears that your tables and relationships may have been set up incorrectly and/or that Referential Integrity has not been set. Improper use of autonumber.

Might be time to show us the database involved.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
27,001
the problem is that the other tables have incorecct infomation because the number is diffrenet from this table to the others

That is a problem, but the question then is "How did it get that way?"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 19, 2013
Messages
16,553
but the question then is "How did it get that way?"
My bet is the OP is trying to have a 1-1 relationship based on PK's - record is created/deleted in one table and then they are out of sync.
 

Sarah.M

Member
Local time
Today, 14:34
Joined
Oct 28, 2021
Messages
335
hello,
i have a table with a relationship with other tables.. i want to update all table records id to be in order (it is already autonumber but i needed to delete some records so there is a gap) i dont want to keep the gap
is there is anyway code to update all the records to be in order?
First make sure you make your relationship correct
You can't link Auto number with Auto number
You can link Auto number with Number

Then Go to your relationship and then Relationship properties and then marke Chekbox Referential integrity to Yes and cascade Update Yes
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:34
Joined
Feb 19, 2002
Messages
42,981
Cascade Delete isn't relevant in this case since Cascade Delete only cascades changes from the primary key to any foreign keys and since you can't change the value of an autonumber, there is never any update to cascade.
 

Users who are viewing this thread

Top Bottom