Access 2007 looses actual increment high value

[Bastian Kraus]

hello,

i've got a confusing problem with increment values of a primary key in
my access 2007 database.

the situation:
i have a table with a running autovalue increment id field. there are
records with the id's 12,13,14,...,30,31 in the table. i delete the
records with the id's 20 to 30 from the table and after that i insert
another record. the increment value starts with the id 20 and not with
32. i insert another amount of records till the increment is by 29. so
if i will insert anoter record the increment uses the id 30, but there's
still a record in the table with the id 30 and access throws an error
message, which is ok because access trys to insert a non-unique key. but
how could this be that the increment goes down from 32 to 20?

all this happens with a self-coded programm in visual basic 2008 with an
OleDB Connection and direct plain SQL commands.

Could someone help me with this?

thanks a lot!
Bastian Kraus

 

[Tom van Stiphout]

On Fri, 24 Jul 2009 11:24:44 +0200, Bastian Kraus

Sounds like your database has some form of corruption. Try compacting
it, and if that does not help copy the data out (to CSV, for example),
drop the table, create a new one, and import the data.

-Tom.
Microsoft Access MVP

 

[Bastian Kraus]

Hello,

thanks tom for your answer!!

i've created a new DB file, copied the structure to the new database
and tried the same thing again.

the primary key goes over 2 columns:
ID: autoincrement
Version (integer): default Value 0

first, the bad thing happened again. after that i compacted and repaired
the database via the repair function then it works. after i'd copied
more records and delete some.. same thing again.

i need this primary key because for one ID are more versions in this
table. does access have problems with such primary key constelations?

strange thing...

thanks a lot,
Bastian Kraus

 

[Jerry Whittle]

i have a table with a running autovalue increment id field.

How is the value incremented? Is it an autonumber field? Is there some code
doing the incrementing?

If it's an autonumber field, you can not depend on it to increment values.
Autonumber fields just create a unique number so that there are no duplicate
values in the table for that field.

If done in code, please show us this code.

 

[Paul Shapiro]

Check Allen Browne's website for suggestions on autonumbering issues and
fixes:
http://allenbrowne.com/ser-40.html

I've found that Access can lose track of the next available autonumber when
you use your own code to manipulate the ID, for example, inserting rows with
specified id values. Access autonumbers seems more reliable if you don't set
any ID values in your code, or use one of Allen's fixes after setting a
value.