Re: Autonum problem

Reuse of an "obsolete" number could indicate a corruption problem in the
database. Make a backup copy of the database, then run compact and repair on
the database. If that does not work, create a new database file and import
all the objects and data into that new file from the old file.

Another way that you might get this problem is if you append a record by a
query and that query has the autonumber field in the field list. ACCESS /
Jet will let you assign numbers to an autonumber field by an append query,
and this resets the seed of the autonumber field, which means it could then
start using old numbers if your query appended an old value into that field.

--

Ken Snell
<MS ACCESS MVP>


"Cathy" <Cathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DED5989F-BF87-49E5-BEA1-14E83D45D274@xxxxxxxxxxxxxxxx
Ken,

The append query does not have the autonumber field as an output field. I
expected to assign a new number. Instead it kept trying to assign an old,
deleted number. I've never had an autonum behave this way before... have
you?

Thnks

"Ken Snell (MVP)" wrote:

Does the append query contain the autonumber field as one of the output
fields? If so, remove that field from the output field list of the append
query -- the autonumber field will be assigned a value by the table when
a
new record is added.

--

Ken Snell
<MS ACCESS MVP>


"Cathy" <Cathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C4FC406E-9948-408B-9D9A-D8DEE8EC0443@xxxxxxxxxxxxxxxx
I have an autonum field in table1 that is the relational field to
table2.
It
is an indexed field in table1 that will not allow duplicates, but is
not
the
primary key. I need to add and delete records from both table from
time
to
time. Recently I ran into a problem when I needed to add several
records
to
table1. The append query kept trying to reuse a deleted autonum and
would
error out. I had to rebuild the tables to make the process move
forward
again. Why would it attempt to reuse a number instead of choosing the
next
highest number? Right now I don't have a lot of records so it wasn't
too
much of a problem. It will be a problem if it does it again in the
future.
How can I prevent this error from happening in the future?

Thank you,
Cathy





.

Relevant Pages

  • RE: Showing data based on other values
    ... Importing and linking have two different purposes. ... data not in the local database and that you do not want in the database. ... To create an append query you start out creating a regular ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Append Query or update query?
    ... An append query isn't going to overwrite - that takes an update query. ... you would have a backend database where the main table (I call tblTarget below) resides. ...
    (microsoft.public.access.externaldata)
  • Re: Append Query for records restore
    ... You do not need to import the data that you will use as the source data. ... Then build your query just as you normally would. ... database window and pressing Delete key. ... >> use an append query to copy those data into your permanent tables. ...
    (microsoft.public.access.queries)
  • Re: Retrieve
    ... setting up a system whereas we have two main databases and a website hosted ... query" and then "append query" to stop the replication of data which are both ... Well, if you don't want the autonumber, don't use an Autonumber field in your ...
    (microsoft.public.access.queries)
  • Re: Unable to save recordset into an access database
    ... > update it in the database. ... You do realize that if two users run this query simultaneously, ... JobID to an autonumber field and let Jet assign values to it. ...
    (microsoft.public.inetserver.asp.db)