ansaurus

Question

Answer 1

+2 A:

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.

Josip Medved 2009-07-29 22:36:38

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.

Tony Toews 2009-07-30 02:12:35

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 :>)

EJB 2009-07-30 10:31:08

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.

Tony Toews 2009-07-30 18:30:34

@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.

Josip Medved 2009-07-30 18:43:38

Answer 2

A:

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 *

Eric Brown 2009-07-29 22:50:09

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.

Josip Medved 2009-07-29 23:39:16

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 Cat2 Dog5 Horse3 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 Cat2 Dog3 Horse4 Mouse5 RabbitWrong IDs

Eric Brown 2009-07-30 19:54:25

Answer 3

+1 A:

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

Seth Spearman 2009-07-29 23:30:19

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

David-W-Fenton 2009-07-30 01:28:09

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.

Tony Toews 2009-07-30 01:57:15

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.

Tony Toews 2009-07-30 02:03:33

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

Seth Spearman 2009-07-30 12:08:33

"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.

onedaywhen 2009-08-01 14:30:50

@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.

onedaywhen 2009-08-02 14:09:28

Answer 4

+1 A:

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.

Tony Toews 2009-07-30 02:01:14

ansaurus

tags:

views:

answers:

MS Access autonumber problem

related questions