Autonumber field, restart at 1 for a copy

  • Thread starter Thread starter David Portwood
  • Start date Start date
D

David Portwood

The office where I work uses an app that I developed. Another office which
does similar work wants me to make a copy of the app for them.

The main table of my app has an autonumber field which is up over 30,000
now. In the copied db I'd like that table to have no records initially and
the autonumber field should restart from 1. How can I make this happen?
 
The office where I work uses an app that I developed. Another office which
does similar work wants me to make a copy of the app for them.

The main table of my app has an autonumber field which is up over 30,000
now. In the copied db I'd like that table to have no records initially and
the autonumber field should restart from 1. How can I make this happen?
Click to expand...

Shouldn't matter. You can have well over four billion autonumber values before
they start to repeat, and they shouldn't be exposed to the user anyway!

You can control the start; different versions do it differently, but try
Compacting the database with no records in the table, I believe that starts it
at 1 for all versions.

John W. Vinson [MVP]
 
What John says, is correct. You can reset an autonumber if you delete all
records from the table and then run Compact and Repair.

But, the main issue is whether an autonumber should have meaning. I like
this article by Richard Hunt, PhD on the subject:

http://www.access-programmers.co.uk/forums/showthread.php?t=128935
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.
 
Hi Bob,

Thanks for the link, it's good, so good in fact I've bookmarked it. Richard
Hunt though does seem to get a little convoluted avoiding the term View.
Views are formalised in Adabas and Oracle (and doubtlessly in other dbms of
which I know nothing). In Access, Queries can be/are Views and as Richard
Hunt rightly points out, views are the 'outside world's' way of looking at
and using the data.

May I add just a footnote regarding resetting the PK. Some years ago I
thought I was very clever in devising a scheme whereby I would save a
record/row and give the user the option of restoring it. I simply negated
the autonumber PK value and copied the row back onto the table. Good idea -
e.g. modified row is 555 and saved original is -555 - but it did not work
since the effect was to reset the autonumber sequence to start from the
negated value. So it seems possible to force the first inserted row to have
the autonumber value of 1 and then let things be.

But I agree with John, you and Richard Hunt; such a PK should have no
meaning other than to identify the row so why does it need to start from 1?

Regards,

Rod
 
A flood of documents comes through the office in which I work. These are
sales reports, submitted daily by each salesperson. The sales forms do not
have a preprinted, unique ID. Thousands of important documents, with no
unique identifier. Unbelievable.

I wanted to develop an Access application to track these reports through
their processing cycle. Obviously, the first thing I needed was a unique
identifier for each document. For this purpose I am using the value
generated by the autonumber field of the main table. The data entry people
write this value on the front page of each sales report.

My app is getting a lot of favorable attention and I suspect that in the
near future someone will think to add an ID to the sales report forms. In
the meantime, for convenience and to minimize error, I'd like to keep the
values as small as possible, for as long as possible.
 
David said:
A flood of documents comes through the office in which I work. These
are sales reports, submitted daily by each salesperson. The sales
forms do not have a preprinted, unique ID. Thousands of important
documents, with no unique identifier. Unbelievable.

I wanted to develop an Access application to track these reports
through their processing cycle. Obviously, the first thing I needed
was a unique identifier for each document. For this purpose I am
using the value generated by the autonumber field of the main table.
The data entry people write this value on the front page of each
sales report.
My app is getting a lot of favorable attention and I suspect that in
the near future someone will think to add an ID to the sales report
forms. In the meantime, for convenience and to minimize error, I'd
like to keep the values as small as possible, for as long as possible.
Click to expand...

Then don't use an AutoNumber.

The second you care about the value in ANY way besides uniqueness then
AutoNumber is not the tool to use.
 
Not sure I can agree, Rick. As I recall, one of original intended uses of
the autonumber field was for things like Purchase Order numbers, where you
want a unique sequence but don't want to ever reuse previous values. If this
is a valid use (beyond uniqueness) then maybe other valid uses exist as
well?
 
David said:
Not sure I can agree, Rick. As I recall, one of original intended
uses of the autonumber field was for things like Purchase Order
numbers, where you want a unique sequence but don't want to ever
reuse previous values. If this is a valid use (beyond uniqueness)
then maybe other valid uses exist as well?
Click to expand...

Intended by whom?

AutoNumbers can be used for Purchase Orders, Invoices, Order Numbers, etc.,
provided that all you care about is that the number be a Long Integer that is
not duplicated within the table it is used in.

However; *most* people want to impose more rules on such numbers. They want
sequential numbers without gaps within a certain range and most certainly would
not want negative values. An AutoNumber does not meet those additional
requirements. There are even cases where "no gaps" is a legal requirement.

I am not in the camp that believes AutoNumbers should never be used in a
capacity that it visible to the user. While I typically avoid that, I have done
it on occassion, but ONLY when I (and the user) had zero expectations or
requirements about the value other than uniqeness.
 
Hi Rick,

Couldn't agree more. If you want a scheme that ensures contiguity of a
number then program the ... thing yourself. I've known Bean Counters latch
onto numbering systems and conclude that any gaps in the sequence indicate
fraud. Hey ho.

Rod
 
David this might be too late but I solved the problem be creating a Data
Definition Query the example code is as follows:-

Alter Table tblname Alter Column RecordID Counter(1,1)
 
Back
Top