re-setting autonumber fields

[CoolDudeeh]

anybody know an easy way to set an autonumber field to a specific value?
I searched MS, and found some info, but have not been able to get it to work.
Access 2000 ..... autonumber field is primary index of table.

 

[lespaul]

You need to look into creating it yourself. There's no way to "reset" the autonumber without recreating the table. There are several FAQ's in this and the other Access forums that talk about when to use an autonumber and how to create your own.

Basically what happens is this: You create a new table and add 1000 records to it; your autonumber is 1000. Your users enter 500 more records, we are now at autonumber 1500. Except the last 250 records entered were errors and need to be deleted. So now we have 1250 records in the database and the autonumber key is 1250. The next record's autonumber will be 1501. Nothing you can do about it, no way to change it.


Leslie

 

[Transcend]

Why do want to reset the auto number field?
It shouldn't matter what the value of your primary key is as this field should never be important to the users anyway.

Transcend

 

[CoolDudeeh]

This database was designed in Access97 some time ago ..... it recently hit the 1Gb limit and crashed ..... I basically created a new database in Access2000 .... transferred the data etc ..... had to change a couple of design issues and all is good so far except that this autonumber is not correct.
Would be nice to have a utility that could accomplish changing the next autonumber to be assigned for a table.

 

[willir]

CoolDudeeh

You can reset the autonumber, but please realize...
- Some want the to use the autonumber to "count" their records. This is not the purpose of the autonumber -- the purpose is to provide a simple method of applying a unique ID to a record, and fascilitate linking parent and child records.
- Autonumber is a long interger (4 bits), -2,147,483,648 to 2,147,483,647, or under certain conditions, as a unique replica ID (16 bits) -- quite a lot of records
- It can be incremented sequentially or randomly

And most importantly,
- If the autnumber is used as a foreign key to other tables, you may have a major conversion issue to address.
- Will you want to retain the existing records, or restart the autonumber with an empty table?

Lastly, you need to backup and backup. You are working with a primary key, and it sounds like a fair chunk of data.

To restart the autonumber
- Delete all records and then the run compact / repair utility


To restart at a fixed number
- Since this process involves writing a dummy record, for the table in question, tempararily
-- Disable primary key settings but leave the autonumber setting
-- Disable any settings for other fields in the table set for no duplicates, no nulls and validation rules (including referential integrity links to other tables)

- Create a temp table with only one field which will have the same name as the autonumber field in question. This field will have to be a long integer field type.
- In the temp table, enter a dummy record which has a number one less than the starting ID number you want to use. For example, if you want autonumber to start at 1000, then enter 999 in the single field.

- Use an append query to write the temp record with the long interger number that was entered to the table in question -- this record will be added to the autonumber field. The append query will be treated the new number without advancing the autonumber.

- Delete the temp table, and delete the dummy record that was appended the table in question

- Reset the primary key setting, and any fields changed earlier including any relationships

The next record to be entered will generate the autonumber you want to use.


Having rambled on this process, is this really what you want? Or do you have a data integrity issue?

Richard