Auto number in well-established DB starts using random numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have been using the same databases (admin & personnel) for 2 years now and just this week the Auto Number function has started throwing out what seem to be random numbers

On the form, they show up as ****E + ****. on the table they show as *********** (* = digit)

This has happened on both data bases which are totally separate to each other

Around the same time as the numbers changing, a yellow circle with a blue and a red arrow running anticlockwise, showed up next to the icons for everything, all the tables, forms, queries and reports, in both databases

Can anyone shed some light on this

Thanks for your tim

Susan
 
Susan,

The "yellow circle with a blue and a red arrow running anticlockwise"
suggest someone has, deliberately or accidentally, made a replica of the
datatbase (Tools > Replication > Create Replica). Not sure if your
autonumber problem is connected with this (I don't have much experience with
replicating), but I can suggest a way of checking if this is indeed the
source of the probelm: create a new, blank database and import all objects
from the original. You will get a new datatbase that is a "clean" copy of
your original without the replication. If that solves your autonumber
problem (and assuming the replica was not created deliberately), then just
delete your original datatbase and keep the new, clean copy.

HTH,
Nikos

Craftywench said:
Hi,

I have been using the same databases (admin & personnel) for 2 years now
Click to expand...
and just this week the Auto Number function has started throwing out what
seem to be random numbers.
On the form, they show up as ****E + ****. on the table they show as *********** (* = digit).

This has happened on both data bases which are totally separate to each other.

Around the same time as the numbers changing, a yellow circle with a blue
Click to expand...
and a red arrow running anticlockwise, showed up next to the icons for
everything, all the tables, forms, queries and reports, in both databases.
 
It is applicable to all versions.

I'm not sure that the advice to import into a new database will work,
though. When the replica was created, the Autonumber was explicitly changed
from Sequential to Random as part of the conversion. Importing the table
into a new database will not change that property: it cannot be changed
back.

It may be necessary to create new versions of the tables in the new database
(making sure the Autonumber is set to Sequential, not Random), and import
the data into new table. Note that any existing random numbers will still be
there.

Of course, this shouldn't really matter. Autonumbers exist for one purpose
only: to provide a (practically guaranteed) unique value that can be used as
a primary key. This purpose is still served by random numbers. Seldom, if
ever, should your users even be aware of the autonumber value.
 
I used to set the New Values property of all AutoNumber fields to Random, to
make it clear to anyone who might view the design that no assumptions should
be made about the value. I only stopped doing that because we've started
using SQL Server for some apps, and I don't see any equivalent option for
SQL Server Identity fields.
 
Back
Top