Changes to Autofill Numbering

[Rob]

Hi,

I have a database that has a key field that is a unique number, where
records are added, this field increments the number by one. There are
several thousand records with that run from 1 to 9654 albeit many of the
records have been deleted and thus there are huge gaps in the number. We
have a requirement to renumber the existing records from 1 to 2010 (2010 is
the number of live records) but because the table that the number is held is
linked to other tables on the field and Autofill is set in the properties,
we don't appear to have a means to alter the numbering.

Any ideas would be most welcome.

Thanks, Rob

 

[Jeff Boyce]

Rob

If the only reason why you want to renumber is so you'll know how many
records you have, don't use (and don't change your) Autonumbers. Instead,
run a simple Totals query that counts the number of records ... or use the
DCount() function.

Access Autonumbers are intended to be used as unique row identifiers, to
help in relationships among tables (as it sounds like you've done).
Modifying a table's key ID could open a mess of work making sure the changes
"ripple" out to the child records. Or you could look at the Cascading
Updates setting in the relationship window.

Before you do any of that, make a backup!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Rob]

Thanks for the advice Jeff.

I need to renumber as we want to use the id no. as a physical file reference
number that already exists. I tried adding new field for this purpose but
because of the links, this also failed.

Rob

 

[James A. Fortune]

Thanks for the advice Jeff.

I need to renumber as we want to use the id no. as a physical file reference
number that already exists. I tried adding new field for this purpose but
because of the links, this also failed.

Rob

The other posters have given good advice. If you are in a situation
where you cannot follow their advice perhaps the following method will
work (make a backup first):

Suppose you have the following tables (two records being deleted from
tblMain have caused gaps):

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
2 D1
4 D2

tblSubMain
SMID AutoNumber (PK)
MID Long (Foreign Key)
SubData Text
SMID MID SubData
1 2 SD1
2 2 SD2
3 4 SD3
4 4 SD4

You'd like to end up with:

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
1 D1
2 D2

tblSubMain
SMID AutoNumber (PK)
MID Long (Foreign Key)
SubData Text
SMID MID SubData
1 1 SD1
2 1 SD2
3 2 SD3
4 2 SD4

Starting with:

tblMain
MID AutoNumber (PK)
Data1 Text
MID Data1
2 D1
4 D2

Take the primary key off MID, change the data type to Number Long and
rename it OldKey (delete the Default Value as well). Save the design.

Add a new AutoNumber primary key called MID. Save the design again.

tblMain should now look like:

tblMain
MID OldKey Data1
1 2 D1
2 4 D2

Then run the following query:

UPDATE tblMain INNER JOIN tblSubMain ON tblMain.OldKey = tblSubMain.MID
SET tblSubMain.MID = tblMain.MID;

Note: It is important that the update query is only run once.

Then delete the OldKey field and put any relationships back .

James A. Fortune
(e-mail address removed)