Prev: Rounding difference in Access 2003 text exports
Next: Attaching a 2003 Access data base to an email for use with Office
From: John W. Vinson on 30 Mar 2010 19:02
On Tue, 30 Mar 2010 15:54:04 -0700, Sideshowmom
<Sideshowmom(a)discussions.microsoft.com> wrote:

>Thank you for your answers, they satisfied my curiosity just fine. No, I
>haven't been running append queries, just typing information into the forms
>and tables. I did try to import from an Excel copy of a table to see if I
>could get it to reset, once, but after the numbering jump. It seemed to
>happen when I switched from just using the tables to the newest set of forms
>I made, about 2 months ago. I've upgraded every chance there was and I did
>see A2003 on Amazon a bit ago. It's in my price-range, much more than 2007
>is!

You may at some point (perhaps after you get 2003) want to create a new, empty
table and run an Append query, appending all the fields EXCEPT the autonumber.
That will give you a clean new sequential autonumber series. This gets
snarkier if you have child tables linked to this one, which would need their
foreign keys updated as well.
--

John W. Vinson [MVP]
From: Tony Toews [MVP] on 30 Mar 2010 20:52
Sideshowmom <Sideshowmom(a)discussions.microsoft.com> wrote:

>The numbering in one of my tables jumped from 374 to 37949458 one day in the
>course of normal useage and is now in negative numbers.

This sounds like the autonumber ID field New Values property was
changed to Random. Or the database was accidentally replicated or
dropped in Windows Briefcase which would also change the above New
Values property.

If you don't have any child records you can delete and recreate this
field to reset the New Values property to Increment. If you do have
child records when it becomes more of a pain.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
From: david on 30 Mar 2010 22:47

"John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message
news:5ve4r5dorbs1d608v9tqgvel5nmj2iom9i(a)4ax.com...
> On Tue, 30 Mar 2010 09:58:04 -0700, Sideshowmom
> <Sideshowmom(a)discussions.microsoft.com> wrote:
>
> you're getting probably come from running Append queries - Access seems to
> reserve enough autonumber "slots" for the worst case scenario in terms of
> the

Access effectively appends all records, then checks the unique indexes for
clashes and DRI.

It has to use the autonumber to base the index on before it can check the
index and do DRI.

This uses up autonumbers, and bloats the database to the extent of any
records which are rejected.

Doesn't sound like that happened here.


(david)


From: John W. Vinson on 30 Mar 2010 22:54
On Wed, 31 Mar 2010 13:47:15 +1100, "david" <david(a)nospam.au> wrote:

>Access effectively appends all records, then checks the unique indexes for
>clashes and DRI.
>
>It has to use the autonumber to base the index on before it can check the
>index and do DRI.
>
>This uses up autonumbers, and bloats the database to the extent of any
>records which are rejected.

Thanks David! I'd wondered how that worked.

Agree that it doesn't seem to be the cause of this particular problem unless
Sideshowmom is running some very strange queries.
--

John W. Vinson [MVP]
From: Risse on 2 Apr 2010 02:41

"Sideshowmom" <Sideshowmom(a)discussions.microsoft.com> kirjoitti
viestiss�:1C87158C-217F-4407-9EC6-C3270FEC0952(a)microsoft.com...
> My questions about this are: What is the upper limit of an AutoNumber
> field?
> and are the jumps in numbering going to reduce the capacity of my
> table/database (already have 1000 records, anticipate several million over
> the course of the database's life), ultimately? (Access 2000)
>
> The numbering in one of my tables jumped from 374 to 37949458 one day in
> the
> course of normal useage and is now in negative numbers. I do plan to
> implement the "compact the database, make a new table and run an append
> query" method. I did want to know about the limits, still.
>
> Thank you.


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Rounding difference in Access 2003 text exports
Next: Attaching a 2003 Access data base to an email for use with Office