Problem with imported table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vivian Carroll - 24 Jan 2004 00:10 GMT

I created an Access 2000 database (lets call it "Original") which the users
all access from the company server (I know, not the ideal way to do it). I
needed to make improvements, so I copied Original, renamed it to Copy and
made changes to Copy. Then I when everyone was out of Original, I moved it
to a holding location and put Copy in its place. I then imported the table
of data from Original to Copy. All works well except..... The form where
users enter new records has an AutoNum field. The last number used in
Original was 2730. Now in Copy, when the user adds a record to the imported
table, the AutoNum inserts an 8-digit number (8087138).

What caused it to jump to such a high number? Is there something I can do to
make the AutoNum field insert 2731 into the next new record?

TIA,
Vivian Carroll

Reply to this Message

Larry Linson - 24 Jan 2004 00:53 GMT

AutoNumbers are intended for "internal use only" -- to uniquely identify
records, to use as surrogate keys, to use for joining related tables. The
value is not intended, as many think, to be "monotonically increasing", but
only to be unique. There are a number of things that can cause gaps in
AutoNumber sequences.

But, when you talk about adding a data table to a multiuser application and
"when everyone was out of ...", implies to me that you may have a potential
problem. If you do not mean that multiple users were _linked to_ the shared
Original, then you should be aware that having multiple users logged into
the same front end or (what I understood to be the case) monolithic database
significantly increases your chances of corruption.

If the users have added a number of records with the higher numbers, you
can't, in fact, do anything about it. If you can start over with bringing in
the Copy, and do a Compact after you import it but before you work with it,
that might do what you want. But, of course, you'll have to re-add to that
copy all the records that were added.

But, you really need to take a look at my introductory presentation to the
subject of Multiuser, which you can find at http://accdevissues.tripod.com,
and then review the detail links and info (best I have found) at MVP Tony
Toews' site, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

> I created an Access 2000 database (lets call it "Original") which the users
> all access from the company server (I know, not the ideal way to do it). I
[quoted text clipped - 11 lines]
> TIA,
> Vivian Carroll

Reply to this Message

Vivian Carroll - 24 Jan 2004 14:08 GMT

Thank you very much for replying Larry. I will check out the links you sent
me.

I am aware that having everyone logged into the same front end increased
chances of corruption. Actually, I didn't create the database, I modified
one that was already there and had been used for years. Luckily, it isn't
heavily used (2500 records over many years). The dept manager did not want
me to put the front end on each user's computer (I assume that this is what
the links will say). However, maybe after reading the links, I will be able
to convince him!

Vivian

> AutoNumbers are intended for "internal use only" -- to uniquely identify
> records, to use as surrogate keys, to use for joining related tables. The
[quoted text clipped - 41 lines]
> > TIA,
> > Vivian Carroll

Reply to this Message

Larry Linson - 25 Jan 2004 00:18 GMT

In the meanwhile, there are only two times you need to make backups -- EARLY
and OFTEN. <GRIN>

They say in the airlines industry that just one crash can ruin your whole
day. The same is true in the database business, but sometimes it takes more
than a whole day to recover.

Larry Linson
Microsoft Access MVP

P.S. Access 2003 (and maybe 2002 and 2000, also, because it may be a
function of the Jet 4.0 database engine and Service Packs thereto) do not
reset the next AutoNumber to the next higher than the last one in the table
on a Compact as it did in the past. If that does work for you, do the
updates of Service Releases/Packs for both Access and the Jet database
engine after you've reformed your AutoNumber.

> Thank you very much for replying Larry. I will check out the links you sent
> me.
[quoted text clipped - 67 lines]
> > > TIA,
> > > Vivian Carroll

Reply to this Message