Problem With Autonumber & Append Query

[Al]

Hi,

I have a table with an autonumber field which users rely on as a sequential
reference for table entries. Some complete entries, including their
autonumber, were accidentally deleted from the table but I have a back-up
copy of the database so my plan was to create an append query to append the
deleted entries from the back-up copy of the table to the original table. I
want the append query to add the entire entry, including it's autonumber,
back into the original table but I can't get it to work, I get a "duplicate
field" message and the append query won't run. I suspect it's something to do
with the autonumber field but I don't know what to do to sort it. Any help
appreciated.

TIA

 

[CompGeek78]

Hi,

I have a table with an autonumber field which users rely on as a sequential
reference for table entries. Some complete entries, including their
autonumber, were accidentally deleted from the table but I have a back-up
copy of the database so my plan was to create an append query to append the
deleted entries from the back-up copy of the table to the original table.I
want the append query to add the entire entry, including it's autonumber,
back into the original table but I can't get it to work, I get a "duplicate
field" message and the append query won't run. I suspect it's something to do
with the autonumber field but I don't know what to do to sort it. Any help
appreciated.

TIA

You should never rely on an Autonumber for meaningful data, that is
not what it is intended for. I wish Microsoft would change Autonumber
fields to Random only, not Sequential so people would stop trying to
use it like this.

Keven Denen

 

[John W. Vinson]

Hi,

I have a table with an autonumber field which users rely on as a sequential
reference for table entries.

That is not the purpose of an autonumber and it is NOT SUITABLE for that
purpose. Deleted records will leave a gap; even hitting <Esc> to cancel an
addition will leave a gap; loading data with an append query can leave a BIG
gap. An autonumber should be used *only* as a meaningless unique ID, and as
such should usually not even be visible to the user.

Some complete entries, including their
autonumber, were accidentally deleted from the table but I have a back-up
copy of the database so my plan was to create an append query to append the
deleted entries from the back-up copy of the table to the original table. I
want the append query to add the entire entry, including it's autonumber,
back into the original table but I can't get it to work, I get a "duplicate
field" message and the append query won't run. I suspect it's something to do
with the autonumber field but I don't know what to do to sort it. Any help
appreciated.

An append query is the only way that you can write to an autonumber field. If
you're determined to do this, please post the SQL view of your query and the
actual error message; it should work if done correctly.

However, you need to change the structure of your table to use a Long Integer
primary key rather than this autonumber, and provide code to increment and
assign it.