Recreating a deleted record

[eellenoff]

Hello, I've got a table that looks something like this:
deviceID | name | ...
1 | a
2 | b
3 | c
4 | d
6 | e

where deviceID is an autonumber. Clearly device 5 got deleted, but if I go to "add record" I end up adding record 7. There are still other records in the db which need to link to device5, and I have all the info needed to recreate them, how do I override the autonumber and create a record with a specific deviceID?

Thanks,
-Eric

 

[pbaldy]

You should be able to use an Append query:

INSERT INTO TableName (deviceID , Name)
SELECT 5 AS NewID, "test" AS DeviceName

 

[eellenoff]

Thank you!

 

[RoyVidar]

Be careful that this might reseed the Autothingie, so that you end up getting dupe numbers.

Contrary to common belief, the functionality of the Autonumber field isn't to give unique numbers, but provide numbers in some sort of order (or lack thereof). Either incremental or random. What provides uniqueness, is indexing the column (primary key, or simply unique).

When doing a

INSERT INTO TheTable (ID, FullName)
Values (42, 'Arthur Dent')

Your next number, if you've got incremental Autos, is most likely 43, regardless of whether that number exists or not.

The possibility of reseeding the aotonumber was introduced in Jet 4.0. Due to the consequences, some chose to consider it a bug

Here are a couple of links describing the issue, and methods of resolving, should it become a problem
http://support.microsoft.com/kb/287756, http://support.microsoft.com/kb/812718, http://support.microsoft.com/default.aspx?scid=kb;en-us;884185

 

[Pat Hartman]

You need to define relationships and enforce referential integrity. That will prevent future deletes when dependent records exist.