By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,217 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

A97: Autonumber values growing by leaps and bounds?

P: n/a
MLH
I have an A97 database table (tblCorrespondence] in a database
installed at 2 sites. The table's key field [CorrespID] is not a
natural key - its an autonumber field. Here are the values at the
2 sites. First, site #1. Then, site #2. If you plot them, you'll see
why I'm worried - I'm afraid I'll be running out-a-numbers soon.

For the life of me, I have no clue why they're not incrementing
by 1. Hope someone can help.

Site #1
CorrespID
1
4
5
6
7
8
9
10
11
12
14
15
16
17
18
19
20
22
23
24
25
26
27
28
29
30
31
32
33
34
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
164
165
166
170
204
240
263
264
291
331
332
333
345
348
349
393
433
434
435
436
501
599
602
623
624
634
635
636
637
654
655
677
678
679
700
722
724
726
727
771
772
773
780
781
788
804
805
807
814
816
820
824
825
826
828
859
861
892
907
915
971
972
990
998
1000
1001
1002
1003
1012
1013
1022
1040
1042
1079
1088
1089
1090
1097
1098
1099
1100
1101
1111
1130
1135
1144
1145
1149
1150
1151
1152
1153
1154
1185
1186
1237
1248
1274
1327
1356
1413
1470
1485
1486
1599
1614
1629
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1868
1869
1887
1888
1906
1907
1980
2185
2186
2187
2189
2192
2261
2262
2263
2264
2350
2351
2352
2353
2371
2372
2373
2374
3173
3174
3175
3176
3177
3178
3179
3201
3202
3203
3204
3205
3206
3207
3229
3230
3231
3232
3233
3234
3235

Site #2
CorrespID
916
917
920
928
931
944
945
948
949
950
951
952
953
954
955
956
957
958
960
962
963
964
965
966
967
968
969
970
981
982
1003
1004
1005
1016
1017
1018
1019
1020
1022
1037
1038
1039
1056
1169
1170
1171
1188
1206
1222
1223
1224
1226
1243
1244
1299
1300
1301
1302
1347
1370
1371
1416
1417
1418
1421
1442
1457
1467
1468
1469
1586
1591
1592
1593
1644
1645
1646
1696
1697
1722
1723
1724
1749
1750
1776
1777
1778
1779
1807
1834
1836
1837
1865
1866
1869
1870
1871
1872
1905
2031
2035
2036
2067
2071
2072
2073
2074
2140
2141
2145
2215
2219
2254
2255
2256
2260
2261
2262
2263
2264
2303
2306
2389
2390
2391
2473
2555
2556
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2858
2958
2959
2960
2961
2962
2994
2995
2996
2997
2998
3641
3642
3748
3784
3929
4002
4003
4076
4149
4185
4186
4222
4223
4483
4521
4522
4745
4894
4895
4896
4897
5009
5010
5011
5049
5050
5051
5132
5333
5374
5539
5540
5705
5706
5829
5870
5871
5995
6037
6542
6669
6712
6713
6714
6844
6888
6889
6890
7023
7024
7069
7070
7071
7116
7161
7242
7243
7244
7285
7286
7327
7328
7581
7624
7667
8012
8055
8098
8787
8831
8875
9140
9186
9201
9232
9233
9280
9327
9516
9517
9518
9519
9567
9615
9616
9617
9666
9667
9716
9717
9936
9941
9990
9995
10350
10351
10352
10353
10354
10355
10356
10357
10414
10415
10416
10417
10418
10475
10476
10477
10478
10479
10480
10542
10604
10853
10854
10855
10856
10857
10858
11311
11312
11313
11314
11377
11378
11379
11380
11443
11444
11445
11446
Mar 4 '06 #1
Share this Question
Share on Google+
21 Replies


P: n/a
MLH wrote:
I have an A97 database table (tblCorrespondence] in a database
installed at 2 sites. The table's key field [CorrespID] is not a
natural key - its an autonumber field. Here are the values at the
2 sites. First, site #1. Then, site #2. If you plot them, you'll see
why I'm worried - I'm afraid I'll be running out-a-numbers soon.

For the life of me, I have no clue why they're not incrementing
by 1. Hope someone can help.


AutoNumbers have many (completely normal) reasons why gaps will develop. They
should be used to provide UNIQUE numbers, not necessarily sequential ones. If
no gaps is a requirement you should not use an AutoNumber.

For example, every record that you start and then cancel uses a number and
produces a gap in the sequence. If you run an update query to insert 1000 rows
and cancel it at the confirmation prompt those 1000 numbers are still consumed.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Mar 4 '06 #2

P: n/a

MLH wrote:
I have an A97 database table (tblCorrespondence] in a database
installed at 2 sites. The table's key field [CorrespID] is not a
natural key - its an autonumber field. Here are the values at the
2 sites. First, site #1. Then, site #2. If you plot them, you'll see
why I'm worried - I'm afraid I'll be running out-a-numbers soon. 11446


You'd better fix this; if you keep using up numbers at 11446 per day
you'll run out in just over 500 years.

Don't come back and ask me for help then!

Mar 4 '06 #3

P: n/a
MLH
Hey, that's pretty funny. Thx.

Anyone else care to comment more specifically
as to what I may be doing that could be causing my Autonumber
fields not to exhibit uniformity when incrementing? You can easily
see the trend is not linear with a quick glance at the data. If you
plot it, the proof is obvious.
Mar 4 '06 #4

P: n/a

"MLH" <CR**@NorthState.net> wrote in message
news:90********************************@4ax.com...
Hey, that's pretty funny. Thx.

Anyone else care to comment more specifically
as to what I may be doing that could be causing my Autonumber
fields not to exhibit uniformity when incrementing? You can easily
see the trend is not linear with a quick glance at the data. If you
plot it, the proof is obvious.


Rick gave you an answer. Try this out for a test. Create a table with 2
fields; ID (AutoNumber) and Test (Text 20). Save the table and go into its
datasheet view. For the first record enter "MLM" in the Test field for the
first record and press the Tab key. You'll notice that the number "1" will
appear automatically in the ID field as soon as you type the "M". Tab to the
Test field for the second record and start typing "Golf". You'll notice that
the number "2" will appear automatically in the ID field. If you haven't
saved the record by tabbing out, press the Esc key to cancel the entry.
Other wise click on the second record and go to Edit > Delete Record. Now
you should only have the first record. Start entering "Boat" in the Test
field to create a second record. You'll notice that the number "3" will
appear in the ID field as soon as you start typing "B".

This was what Rick was getting at when he said about when a user cancels the
record. If you have lots of records being added and cancelled through
automation then you'll bound to have big gaps in your numbers. DON'T use
AutoNumbers in a way which humans are meant to read and understand. Use them
ONLY to provide UNIQUE numbers for your records.

Jeff
Mar 5 '06 #5

P: n/a
MLH wrote:
Hey, that's pretty funny. Thx.

Anyone else care to comment more specifically
as to what I may be doing that could be causing my Autonumber
fields not to exhibit uniformity when incrementing? You can easily
see the trend is not linear with a quick glance at the data. If you
plot it, the proof is obvious.


Maybe a user pressing on the AddRecord button on the navigation buttons
creating a series of numbers and then deleting the records.

Running a query that deletes records.

Going into a record but canceling before save

An append then delete query is run from some code.

You have a sub/function where think you are adding records to a temp
table but you are actually using a production table to add/delete
records for computations.

You are making copies of tables and working on them and forgetting to
compact the table

There doesn't seem to be any pattern. But since some gaps are 10-50
numbers I'd look at temp table updates you may be using...look for the
word Delete in you code and look at queries that delete. You spend some
time debugging but you'll find your logic error.
Mar 5 '06 #6

P: n/a
MLH wrote:
Hey, that's pretty funny. Thx.

Anyone else care to comment more specifically
as to what I may be doing that could be causing my Autonumber
fields not to exhibit uniformity when incrementing? You can easily
see the trend is not linear with a quick glance at the data. If you
plot it, the proof is obvious.


This is a shot in the dark. One thing to check for would be dependency
on network reliability. A flaky network might somehow cause Access to
attempt to create a new record yet fail after the autonumber is
assigned. Access might keep trying until it gets a good enough
connection to save the record. I don't know enough about the specifics
of how Access creates a new record to know if that scenario is even
possible. Try a test database that uses local tables to see if the
network is a factor. Also try adding a timestamp to the record in the
real database to see if the gaps happen at a time when the network is
particularly busy. I've seen network people string network cable like
extension cords around power cables and other electromagnetic sources.
I've seen that slow Access down especially on networks that use hubs
rather than switches. I've never seen Access do what it's doing to you
though.

James A. Fortune
CD********@FortuneJames.com

Mar 5 '06 #7

P: n/a
MLH
I missed Rick's answer. I'll google for it now.

Rick gave you an answer. Try this out for a test. Create a table with 2
fields; ID (AutoNumber) and Test (Text 20). Save the table and go into its
datasheet view. For the first record enter "MLM" in the Test field for the
first record and press the Tab key. You'll notice that the number "1" will
appear automatically in the ID field as soon as you type the "M". Tab to the
Test field for the second record and start typing "Golf". You'll notice that
the number "2" will appear automatically in the ID field. If you haven't
saved the record by tabbing out, press the Esc key to cancel the entry.
Other wise click on the second record and go to Edit > Delete Record. Now
you should only have the first record. Start entering "Boat" in the Test
field to create a second record. You'll notice that the number "3" will
appear in the ID field as soon as you start typing "B".

This was what Rick was getting at when he said about when a user cancels the
record. If you have lots of records being added and cancelled through
automation then you'll bound to have big gaps in your numbers. DON'T use
AutoNumbers in a way which humans are meant to read and understand. Use them
ONLY to provide UNIQUE numbers for your records.

Jeff


Mar 6 '06 #8

P: n/a

Rick Brandt wrote:
MLH wrote:
I have an A97 database table (tblCorrespondence] in a database
installed at 2 sites. The table's key field [CorrespID] is not a
natural key - its an autonumber field. Here are the values at the
2 sites. First, site #1. Then, site #2. If you plot them, you'll see
why I'm worried - I'm afraid I'll be running out-a-numbers soon.

For the life of me, I have no clue why they're not incrementing
by 1. Hope someone can help.


AutoNumbers have many (completely normal) reasons why gaps will develop. They
should be used to provide UNIQUE numbers, not necessarily sequential ones. If
no gaps is a requirement you should not use an AutoNumber.

For example, every record that you start and then cancel uses a number and
produces a gap in the sequence. If you run an update query to insert 1000 rows
and cancel it at the confirmation prompt those 1000 numbers are still consumed.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Mar 6 '06 #9

P: n/a
Thx, Rick. Gaps in autonumber fields don't bother me.
Autonumbers being used up exponentially are of some
concern. If a graph of the autonumber field shows the
values at the rate of a 2nd order equation, I've got
a 46,340-record limit to deal with. Some other customers
are likely to exceed that number of records in just a little
over 2 years.

Mar 6 '06 #10

P: n/a
MLH
Thx. Yes, I know about that behavior. I mentioned to Rick that
gaps in autonumber fields don't bother me as much as
autonumbers being used up exponentially. If a graph of my
autonumbers were to approximate y=x^2, then I could run out
of autonumbers after 46,340 records. T'would be of no concern
if autonumbers wrapped around, filling in the holes 2nd time around.
I'm sure that ain't gonna happen. Guess I'll have to check to see
if I have lots of records being added and cancelled through
automation. I suppose that would be poor programming practice.

O.T. - I get the feeling I don't always see all the posts on this
forum with Free Agent and my ISP. I found Rick's post you
mentioned via Google search. Anyone using Free Agent ever
noticed similar problem?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Try this out for a test. Create a table with 2
fields; ID (AutoNumber) and Test (Text 20). Save the table and go into its
datasheet view. For the first record enter "MLM" in the Test field for the
first record and press the Tab key. You'll notice that the number "1" will
appear automatically in the ID field as soon as you type the "M". Tab to the
Test field for the second record and start typing "Golf". You'll notice that
the number "2" will appear automatically in the ID field. If you haven't
saved the record by tabbing out, press the Esc key to cancel the entry.
Other wise click on the second record and go to Edit > Delete Record. Now
you should only have the first record. Start entering "Boat" in the Test
field to create a second record. You'll notice that the number "3" will
appear in the ID field as soon as you start typing "B".

This was what Rick was getting at when he said about when a user cancels the
record. If you have lots of records being added and cancelled through
automation then you'll bound to have big gaps in your numbers. DON'T use
AutoNumbers in a way which humans are meant to read and understand. Use them
ONLY to provide UNIQUE numbers for your records.

Jeff


Mar 6 '06 #11

P: n/a
MLH
Thx, James.
No, all are local apps 'n tables. Still a mystery.
Mar 6 '06 #12

P: n/a
You could always use a Decimal Field in place of your Auto-Number Field
and populate it with a UDF.
That way you won't run out of numbers until you have 281 trillion true,
actual, real records (yes, even with the y=x^2 extravagaza).

----

On the other hand you could search your code and query strings for the
name of the table where the rabbits are breeding, check all the script,
find out where it's screwing up and fix it.

Mar 6 '06 #13

P: n/a
<cr**@northstate.net> wrote in message
news:11**********************@p10g2000cwp.googlegr oups.com...
Thx, Rick. Gaps in autonumber fields don't bother me.
Autonumbers being used up exponentially are of some
concern. If a graph of the autonumber field shows the
values at the rate of a 2nd order equation, I've got
a 46,340-record limit to deal with. Some other customers
are likely to exceed that number of records in just a little
over 2 years.


They will not be used exponentially. You will see gaps occasionally big ones,
but those gaps will not grow as your database grows unless you have some very
strange things going on.

Did you account for the fact that the AutoNumbers will wrap around? They will
not wrap to zero and fill in gaps, but they will wrap around and use all of the
negative numbers that a Long Integer supports.

Be comforted in the fact that I have never seen a post from an Access user that
had run out of AutoNumbers. You would likely hit the file size limit way before
that happened.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Mar 6 '06 #14

P: n/a
On Mon, 06 Mar 2006 02:02:42 GMT, "Rick Brandt"
<ri*********@hotmail.com> wrote:

Wrap around? Do the math. Not likely in the app's lifetime.
Or in ours. Deo volente.
No seriously, it's hard to fully appreciate how BIG 2^31 is. You can
imagine counting to a thousand if you really wanted to. A million?
That would be a lot of work. A billion?

-Tom.

<cr**@northstate.net> wrote in message
news:11**********************@p10g2000cwp.googleg roups.com...
Thx, Rick. Gaps in autonumber fields don't bother me.
Autonumbers being used up exponentially are of some
concern. If a graph of the autonumber field shows the
values at the rate of a 2nd order equation, I've got
a 46,340-record limit to deal with. Some other customers
are likely to exceed that number of records in just a little
over 2 years.


They will not be used exponentially. You will see gaps occasionally big ones,
but those gaps will not grow as your database grows unless you have some very
strange things going on.

Did you account for the fact that the AutoNumbers will wrap around? They will
not wrap to zero and fill in gaps, but they will wrap around and use all of the
negative numbers that a Long Integer supports.

Be comforted in the fact that I have never seen a post from an Access user that
had run out of AutoNumbers. You would likely hit the file size limit way before
that happened.


Mar 6 '06 #15

P: n/a
MLH
Hi Salad. Long time no talk to.

Here's why I'm concerned: I see a table with
an autonumber field and 340 records. The
autonumber field value begins at 916 and
ends at 11446 on the 340th record. If I'm
using up autonumbers exponentially, I'd
like to put an end to it now instead of
waiting 'til I have a problem.
y=814.75e^0.008x is a pretty good curve
matching the data trend where x is the record
number and y is the autonumber value. I'm not
saying its an accurate prediction of the future,
but it pretty much describes the first 4 months
of use at the client site
Mar 6 '06 #16

P: n/a
MLH
I'll probably go with the latter. Its just kickin my ass because
there's a lot of code and I'm uncertain where to start lookin'

On the other hand you could search your code and query strings for the
name of the table where the rabbits are breeding, check all the script,
find out where it's screwing up and fix it.


Mar 6 '06 #17

P: n/a
If Gaps don't bother you then it might be helpful to change the New
Value Property of the Field to Random. By doing so you should ensure
that all 4294967296 or 4294967295 (Do Random AutoNumbers allow Zero?
They should, but who knows?) Longs, [including those less then zero]
will be available to you immediately. If Randoms work as Incrementals
do, the status of those numbers previously used but now free should be
returned to available after each Compact of the DB.

Mar 6 '06 #18

P: n/a
MLH
>They will not be used exponentially. You will see gaps occasionally big ones,
but those gaps will not grow as your database grows unless you have some very
strange things going on. I sure hope you're right on that. That's all I need is for this f'ing
app to run out-a-numbers as a result of something strange that
I probably do have going on. That would be just my luck.
Did you account for the fact that the AutoNumbers will wrap around? They will
not wrap to zero and fill in gaps, but they will wrap around and use all of the
negative numbers that a Long Integer supports. I was just talking to Jeff Smith about that very thing a few minutes
ago. Its a comfort to know that it will wrap.
Be comforted in the fact that I have never seen a post from an Access user that
had run out of AutoNumbers. You would likely hit the file size limit way before
that happened.

Without doubt, I won't run out of them as the result of the actual
record count. Even the biggest customer will probably have no more
than 50-100 thousand recs over a few years use. But if after a year or
two, I'm wasting them by the millions because of something stupid I
did in the code - its gonna come back to haunt me.

Since no one's ever posted the problem, I'm probably worrying over
nothing. But I hope I never have to face coding to reassign smaller
values to base tables and their related counterparts. That would be
a bear.
Mar 6 '06 #19

P: n/a
MLH
Man, you're a genius. I thnik that'll probably work. Plus, I can opt
to do it pretty much anytime - don't see any reason why not. So I'll
just watch the trend over time, following back up with the NG some
months down the road - reporting the problem again if it seems to be
of genuine concern. The moment it is a real concern, that property
change ought to take care of the problem immediately.

If Gaps don't bother you then it might be helpful to change the New
Value Property of the Field to Random. By doing so you should ensure
that all 4294967296 or 4294967295 (Do Random AutoNumbers allow Zero?
They should, but who knows?) Longs, [including those less then zero]
will be available to you immediately. If Randoms work as Incrementals
do, the status of those numbers previously used but now free should be
returned to available after each Compact of the DB.


Mar 6 '06 #20

P: n/a
MLH wrote:
Hi Salad. Long time no talk to.
Yes. I wonderered where you wandered off to.
Here's why I'm concerned: I see a table with
an autonumber field and 340 records. The
autonumber field value begins at 916 and
ends at 11446 on the 340th record. If I'm
using up autonumbers exponentially, I'd
like to put an end to it now instead of
waiting 'til I have a problem.
Is this a "production" database where you are also modifying at the same
time? (I'm thinking A97). Or development? Is this specific tables or
all tables?

I suggest looking at your query list for any Append, Update, or Delete
queries (you can determine by their icon). See if they affect the
tables that have the gaps.

Next, look in your code for .Addnew and .Delete.

You could do a test. Create a table (Table1) with one field named ID,
type autonumber

Dim rst As Recordset
Dim i as Integer
set rst = currentdb.openrecordset("Table1",dbopendynaset)
For i = 1 to 1000
rst.addnew
rst.update
next i
rst.close
set rst = Nothing

Are there any gaps? If not, I'd think the gaps are created in your
code...via calls to a query or some code in a loop.
y=814.75e^0.008x is a pretty good curve
matching the data trend where x is the record
number and y is the autonumber value. I'm not
saying its an accurate prediction of the future,
but it pretty much describes the first 4 months
of use at the client site

Mar 6 '06 #21

P: n/a
MLH
On Mon, 06 Mar 2006 22:43:06 GMT, salad <oi*@vinegar.com> wrote:
MLH wrote:
Hi Salad. Long time no talk to.
Yes. I wonderered where you wandered off to.
Here's why I'm concerned: I see a table with
an autonumber field and 340 records. The
autonumber field value begins at 916 and
ends at 11446 on the 340th record. If I'm
using up autonumbers exponentially, I'd
like to put an end to it now instead of
waiting 'til I have a problem.


Is this a "production" database where you are also modifying at the same
time? (I'm thinking A97). Or development? Is this specific tables or
all tables?

It is a production database installed in 5 locations.

I suggest looking at your query list for any Append, Update, or Delete
queries (you can determine by their icon). See if they affect the
tables that have the gaps.

Next, look in your code for .Addnew and .Delete.

You could do a test. Create a table (Table1) with one field named ID,
type autonumber

Dim rst As Recordset
Dim i as Integer
set rst = currentdb.openrecordset("Table1",dbopendynaset)
For i = 1 to 1000
rst.addnew
rst.update
next i
rst.close
set rst = Nothing

Are there any gaps? If not, I'd think the gaps are created in your
code...via calls to a query or some code in a loop.

Tested. Nope, no gaps.
I think you're right. Will look for suspicious places in which I
do the appends.
Mar 7 '06 #22

This discussion thread is closed

Replies have been disabled for this discussion.