Autonumber doesn't start at 1

[Dorci]

Access 2000 - I always include the autonumber ID field
when I create tables, and they always start with the
value of 22 instead of 1. This occurs in existing
databases and brand new ones. Is there an option
somewhere to set the default starting number of
autonumber fields? Thanks!

 

[Jeff Boyce]

Dorci

Why? As in "why do you care what value Access uses when it creates a row
identifier?" That's what Autonumber is designed to be, a unique row
identifier.

So, why does it matter? (the business need)

 

[david epsom dot com dot au]

That is really interesting, because no, there is no option
to set the default starting number <for a database>! It
would be really cool if you could work out how you've done
that, but I expect you will just have to try re-installing
Access/Office.

(david)

 

[Jeff Boyce]

David

FYI -- there are ways to end up with an Autonumber of 1 without
re-installing Access.

My question relates to whether the tool (Autonumber data type) was being
used in the manner it was designed to be used.

Jeff Boyce
<Access MVP>

 

[Dorci]

Jeff/David
I'm not referring to any specific database use at the moment. It's just intriguing (and annoying) that autonumber starts at 22 every time. When I use the little trick of appending a starting record to the table to reset the autonumber at 1, the next record created is autonumber 23! (Is there another trick to reset autonumber to 1?) Note: This isn't urgent, just a nagging curiosity

----- Jeff Boyce wrote: ----

Dorc

Why? As in "why do you care what value Access uses when it creates a ro
identifier?" That's what Autonumber is designed to be, a unique ro
identifier

So, why does it matter? (the business need

 

[david epsom dot com dot au]

That is also an interesting and important question, and I'm
glad you were able to raise that point.

(david)

 

[david epsom dot com dot au]

On the current copy of Jet, it is possible to 'set', but not 'reset' the
autonumber of a table by appending a starting number and compacting.
Unless the table is empty, when it should reset to 1. But it is possible
by using ADOX:
http://allenbrowne.com/ser-40.html
or DDL:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;202117

(david)

Jeff/David,
I'm not referring to any specific database use at the moment. It's just

intriguing (and annoying) that autonumber starts at 22 every time. When I
use the little trick of appending a starting record to the table to reset
the autonumber at 1, the next record created is autonumber 23! (Is there
another trick to reset autonumber to 1?) Note: This isn't urgent, just a
nagging curiosity.

 

[Paul Johnson]

I would like to see my tables' autonumber fields reset to 1 when I delete
all the records in them, but they still pick up at the last autonumber used.
I am running Access 2000 on a Windows 2K platform. Is there a setting I
need to change to get the autonumbers to reset?

Paul Johnson

 

[tina]

run a Compact/Repair on your database after deleting all the records. that
should do it.

 

[Paul Johnson]

Yes, that does it. Thank you.

run a Compact/Repair on your database after deleting all the records. that
should do it.


When just

 

[Jeff Boyce]

Tina's provided a mechanism for you to do what you asked about.

And my question is still "why?" Why does it matter what value an autonumber
field holds?

The Access Autonumber is intended for use as a unique identifier. Using it
to denote sequence, or a user-viewable value is asking for issues later on,
and is a little like using a screwdriver to pound in nails -- you can do it,
but:

 

[Paul Johnson]

I understand and agree completely with everything you say. My only concern
is from my developer's standpoint, I use the field in WHERE conditions like
"ReportID IN (12345, 12346, 12347, 12377, 23466, 43566, 23434, 54545, 56565,
.... etc)" that may have up to a hundred records (hand-picked by the user in
a multi-select listbox). I'm not sure if there's a maximum string length to
that WHERE condition string, but double- and triple-digit numbers keep it
shorter than 4- and 5-digit ones.

Also, as I develop this app, it makes it much easier for me to debug when I
can see the ReportIDs in the string, and I can recognize if they are coming
out of a particular set of records that are related by a Foreign Key field.
When I de-populate and re-populate the tables, I like to recognize the
patterns the same way I've seen them before.

To answer your question: I have no plans to reset the autonumber once the
application is developed/deployed.

Thanks for your insights. If you know about a maximum string size for the
WhereCondition (or maximum for an ordinary SQL statement), I am curious to
know it.

Thank you.

Paul Johnson

 

[Jeff Boyce]

Paul

(see in-line comments)

I understand and agree completely with everything you say. My only concern
is from my developer's standpoint, I use the field in WHERE conditions like
"ReportID IN (12345, 12346, 12347, 12377, 23466, 43566, 23434, 54545, 56565,
... etc)" that may have up to a hundred records (hand-picked by the user in
a multi-select listbox). I'm not sure if there's a maximum string length to
that WHERE condition string, but double- and triple-digit numbers keep it
shorter than 4- and 5-digit ones.

I believe there is a maximum string length for a SQL statement, so I can see
how saving 1 (or 3) digits could help with your approach. I'm wondering
(speculation only) if another approach could eliminate the need for that
portion of the string altogether... My thoughts (untested) are slanted
toward the use of a temporary table that holds the (multi-)selected listbox
row IDs. Then it seems you could use a join to that temp table (list) of
IDs. Just a thought...

Also, as I develop this app, it makes it much easier for me to debug when I
can see the ReportIDs in the string, and I can recognize if they are coming
out of a particular set of records that are related by a Foreign Key field.
When I de-populate and re-populate the tables, I like to recognize the
patterns the same way I've seen them before.

I can see this, working "behind the curtain" myself. So the "business need"
I asked about is YOUR business need!

To answer your question: I have no plans to reset the autonumber once the
application is developed/deployed.

Thanks for your insights. If you know about a maximum string size for the
WhereCondition (or maximum for an ordinary SQL statement), I am curious to
know it.

Thank you.

Paul Johnson

JOPO (just one person's opinions)

Jeff Boyce
<Access MVP>

 

[Paul Johnson]

Jeff,

That's a great idea. I may try pushing the limits of the present system, by
populating the table into 5-digit ID nos and selecting more than my typical
user is ever expected to select, just to see if I exceed the capacity of the
Where condition string. If I can't break it, I'll leave it alone, but if I
can crash it, I'll use a table. It makes more sense to let the numerous
records be identified in the USUAL data-storage medium (a table), rather
than a lengthy string expression that gets tagged to a form or report's
Filter property (a bit unorthodox, I'll admit). Heck, I'll probably end up
going with the idea even if I don't reach the practical limit of the string
expression, because it's so logical.

Thanks for the suggestion.
Paul Johnson

 

[Tim Ferguson]

is from my developer's standpoint, I use the field in WHERE conditions
like "ReportID IN (12345, 12346, 12347, 12377, 23466, 43566, 23434,
54545, 56565, ... etc)" that may have up to a hundred records
(hand-picked by the user in a multi-select listbox).

A much better solution is to put the required IDs into a (temp) table and
joining it to the first one -- this is faster and gets round all the
arbitrary string length stuff.

PS -- how do you train your staff to handpick a hundred records with a
mouse?

HTH


Tim F

 

[Paul Johnson]

Selection can go quickly when you sweep the mouse through the listbox.

Paul Johnson

 

[david epsom dot com dot au]

We have a fundraiser out here, where a cow is used to select
a numbered square on a football oval...

(david)