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" <(E-Mail Removed)> wrote in message
news:eshB#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eshB#(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:eshB#(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
news:%232%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:eshB#(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
news:#2#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:eshB#(E-Mail Removed)...
>>> 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
>>>
>>>
>
>