How to Disable all AutoNum in Access 2003

Question

Hi,

I have an Access database that is corrupted beyond all repair (don't ask me how, it's a client database). However, we can, using code like .NET ODBC, access the data. Since we have a clean copy of the database, we are planning to transfert all data from the corrupted to the new one.

We only have one problem, it's that the database contains autonums and lots of links. When we transfer the data, we want to make sure all autonum ids are exactly the same as they were before. Othewise, we'll just end up with more problems.

Is there a way to do this ?

Thanks

Answer 1

I believe ACCESS Select into's will keep the autoincrement unchanged. After that, you can continue working in your clean db.

Answer 2

Not sure if you can disable the AutoNum. You might be able to reset/adjust it if that helps.

http://www.bluemoosetech.com/microsoft-access-functions.php?jid=1&title=Microsoft%20Access%20Autonumber%20Increments

Answer 3

I don't understand about disabling an autonumber. Your new table can accept autonumber values from the old table even if the destination field is also an autonumber. In this example, the id field is an autonumber in both tables:

INSERT INTO tblFoo_new ( id, foo_text, date_assigned, parent_id )
SELECT old.id, old.foo_text, old.date_assigned, old.parent_id
FROM tblFoo AS old;

tblFoo_new is an empty table, and the database was compacted before executing the INSERT INTO statement ... compact "resets" the autonumber counter.

If tblFoo_new was not empty to begin with, execute could have failed. Same if I had deleted rows from the table, but not compacted to reset the counter.

What's the deal with the links you mentioned? How do they fit into this?

Answer 4

Many people misunderstand the Jet/ACE Autonumber field. It is really just a long integer field with a default property and a few special properties (such as noneditability, and limited to one per table).

There is nothing at all preventing you from simply inserting the existing values into new empty tables. The Autonumber values will be maintained because when creating the records, you've provided an explicit value, rather than relying on the default value to populate the field.

The only issue would be if referential integrity is defined, in which case you have to do the appends in order such that parent records are inserted before child records, or you remove RI for the insert, and then put it back afterwards.

I'd likely do the latter, because I have code that can copy relationships from one database to another, so I'd just keep as backup template.

Last of all, you need to determine what caused the corruption and remedy that problem. Otherwise, you're just going to have to do this again someday.