Autonumber as primary key

[J E Jensen]

Hello NG

I have a huge access database that is maintained with a rather old
application (VB6).
The way the data is maintained is by using RecordSet.AddNew ......
RecordSet.Udate

When i fire the RecordSet.AddNew i can see what number the PrimaryKey will
be. Now i'll get an error when i fire the RecordSet.Update (Err#3022) the
generated number already exists in the table - therefore it can't be saved.
The record has only about 500.000 records - the lowest key is -2147483648
and the highest key is 2147483647 - there is ofcouse a lots of gabs between
the numbers.

The Primary key was set as AUTO NUMBER with the "new values" set to
Increment.

When i changed the "Increment" to "Random" all worked just fine.

Now my question is - is the random OK to use?
Does anyone know of a tool to re-index the table ( so that the auto-number
again start with 1 ).

Kind regards

Johnny E. Jensen

 

[Allen Browne]

Random is okay.

For a sequential autonumber, compact/repair a database to reset the Seed. If
that doesn't work this code will do it:
http://allenbrowne.com/ser-40.html

The simplest way to change the existing table would be:
a) In table design view, change the field to Number instead of AutoNumber.
Save.
b) Then add a new field of type autonumber.

 

[CraigH]

Just a little bit more to the simple way.

Random is okay.

For a sequential autonumber, compact/repair a database to reset the Seed. If
that doesn't work this code will do it:
http://allenbrowne.com/ser-40.html

BACK UP DATABASE

The simplest way to change the existing table would be:
a) In table design view, change the field to Number instead of AutoNumber.
Save.
b) Then add a new field of type autonumber.

c) Go to each related table add new foriegn key field (I assume you use the
autonumber as that key)
d) Run update query to update new foriegn key field with new Autonumber
e) Redo relationships on new keys
f) Make sure everything works and then delete the old fields.

 

[J E Jensen]

Hello Allen

Thanks for your reply.
I think i stay with the RANDOM setting for now.

I tryed the link bellow (allenbrowne.com/ser-40.htm) with no luck because
the seed is set to higest max. number for the long data type in the table so
i get the overflow error.

Is there something i can do to prevent this to happen in future. The
application and database has been on the market for almost 15 years now, and
this is the first time ever that this has happend.
The database was until one year ago a replicate able database, but has been
converted back to a (non-replacte able database) - (by importing data to a
new database)

Anyway thanks again for your reply.

Kind regards

Johnny E. Jensen

 

[Jeff Boyce]

Unfortunately, RANDOM <> unique (just very, very unlikely).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

To prevent problems with autonumbers, you need to understand what causes the
problem so you can take action at the appropriate time. The article had all
the info I'm aware of.