Autonumber as primary key

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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

Just a little bit more to the simple way.

"Allen Browne" wrote:

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.


--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"J E Jensen" wrote in message
...
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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"J E Jensen" wrote in message
...
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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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.html) 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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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.html) 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

"Allen Browne" wrote in message
...
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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"J E Jensen" wrote in message
...
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