MS Access autonumber problem

Question

A client of mine accidentally deleted about 500 records from an access table that has a primary ID field which was created as "autonumber". By turning off the autonumber column (changing back to a integer), I was able to restore the missing 500 records from a backup, but now of course the autonumber cannot be turned back on...

What are some possible solutions? The ID field is used as a link to other tables, so I can't just renumber everything without also renumbering all of the tables that reference this number (a pain in the neck, but possible).

Is there any "trick" to turning autonumber back on, using the max(id) as the starting point if data already exists in the table?

Aquilar

I can confirm that Josip's method works, but I have to ask. Don't they have a backup of the DB for this very occurrence? – Pandybat

Answer 1

Make newTable with ID field as AutoNumber (all fields must be same as in original table - except ID). Copy all data from originalTable to newTable:

INSERT INTO newTable SELECT * FROM originalTable

Once data is filled, delete originalTable and rename newTable to originalTable.

This way all "holes" in auto-numbering are preserved and newTable has Auto-Numbering turned on.

P.S. Always try to add foreign keys to your IDs. In that case, even if some data is deleted, you will at least have consistent state.

Burkhardt

Don't forget to remove and recreate the relationships when deleting the original table and after renaming the new table. Your last sentence doesn't make a lot of sense to me. There are some tables that don't have foreign keys. Although these would generally be referred to as "master" tables or lookup tables. For example a transaction type, country or province/state table. And I'm not quite sure what you mean by "consistent state" in this case. – Leitmotif

I agree, had this DB been setup correctly the mistake could not have been made (at least not so easily), alas, as with many projects, I get called in after the damage is done. Access is one of those tools that will allow even non-programmers to get a fairly decent "solution" going, until it falls apart because it was not setup correctly. I get a lot of work upsizing clients to SQL server solutions once they realize that "joe in the mail room" can't really program after all :>) – Aquilar

Sure, Access gets a lot of disrespect because folks are doing strange things with it without knowing what they are doing. But hey, it's better than using Excel or, even worse, Word tables. – Leitmotif

@Tony Toews: Since he did say that that table links to other records, I would assume that foreign key is needed. It would not necessary prevent deletion (cascade), but it would definitely leave either everything deleted or everything available - I would call that consistent. – Burkhardt

Answer 2

The ideal solution, although it's now too late, wouuld've been to restore the missing 500 records into a working table. Then do an Append query into the main table. This would've included the Autonumber field.

Leitmotif

Answer 3

If I could add to the answers given.

A little known fact about Access autonumber fields is that the counter on them is reset when you compact and repair the database.

I am also pretty sure that if you do an insert it WILL use the numeric that is supplied rather than the next number in the autonumber counter as long as it is > (greater than) the internal counter kept by the auto-number field (does that make sense at all?)

In other words you CAN do something like this in an brand new access table where the counter should be set to 1...

INSERT INTO myTable (myAutoNumber,myOtherField) VALUES (10000,'other data')

The other solutions mentioned here are better because they would do a better job of guaranteering the result so I mention it almost for academic reasons.

Seth

Pleadings

From Jet 4 on, you can also set the Autonumber seed in ADO code. – Casey

In Jet 3.5 (for Access 97) the autonumber seed was reset to the highest autonumber id in the field on compact. However in Jet 4.0 (Access 2000 and newer) this only happens when there are no records in the table and the seed is set back to 0. – Leitmotif

Actually you can insert a record with a value in the autonumber field into the "middle" of a table. So long as that autonumber value isn't already in the table of course. I just tested currentdb.Execute ("INSERT INTO Table1 ( ID, textfield ) VALUES (2, 'Record ID 2')") in the Immediate window where ID is the autonumber field. – Leitmotif

Guys, Thanks for the clarification...I'm rusty. Seth – Pleadings

"From Jet 4 on, you can also set the Autonumber seed in ADO code" -- but you cannot add the IDENTITY property to a column unless it contains no data. – Distress

@Tony Toews: Actually you can insert a record with a value in the autonumber field into the "middle" of a table even if that value is already in the table. There's nothing inherent about autonumber that guarantees uniqueness, of course. – Distress

Answer 4

Agree, but may want to add an ORDER BY to ensure that the AutoNumber is in the correct order. Otherwise your other tables will have the wrong ID association.

INSERT INTO newTable SELECT * FROM originalTable ORDER BY ID

You will also have to explicitly name the fields instead of using *

Bellini

You don't need explicit names if you make second table identical to first one (except for ID field). Ordering by ID does not do anything here since Auto-numbering is "overriden" with ID from SELECT statement (ID is not freely choosen). Since all IDs are taken from original table, no new IDs are introduced and no wrong ID association will happen. – Burkhardt

Agree with *, didn't see your "except for ID" comment in original post. But you will still need the ORDER BY. When he puts back the deleted 500 records, they are going to go into the table as the last rows. So his original table has the correct ID's but are not in the correct order. For instance, Original Table with deleted rows added back: 1 Cat 2 Dog 5 Horse 3 Mouse (added back) 4 Rabbit (added back) If you copy these over to the new table without doing an ORDER BY on the ID and with AutoNumber turned on, you will get: 1 Cat 2 Dog 3 Horse 4 Mouse 5 Rabbit Wrong IDs – Bellini

Answer 5

You make a new field and make it auto-number and then delete id field and rename new field to id

Radiocarbon