Autonumber problem

[Guest]

I have a autonumber field to generate consequtive numbers that will be used
to generate a report query. Somehow the autonumber sequence has been
corrupted in the datebase which resulted in a very large number being
generated. I followed the Help instructions, but I can't seem to get the
autonumber to pickup where it should. Any ideas?

 

[Rick B]

An autonumber is simply a unique key used in the database. It should not
matter to you what the number is. If you want a meaningful number
associated with a record (PO#, Employee#, Invoice#, etc.) then you should
not be using autonumber, you should create a field and create code to
increment it when adding new records.

 

[Joseph Meehan]

I have a autonumber field to generate consequtive numbers that will
be used to generate a report query. Somehow the autonumber sequence
has been corrupted in the datebase which resulted in a very large
number being generated. I followed the Help instructions, but I
can't seem to get the autonumber to pickup where it should. Any
ideas?

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.
See:
http://www.lebans.com/rownumber.htm

 

[Guest]

The only user is me. If you had read the message, you would have noted that
I am using the autonumber to generate a query report.

 

[Guest]

That is fine, but I just need a simple number to use as key to generate a
query report. Since the function of autonumber suits my purpose, I see no
need to create a field and create code which would yield the same results.

 

[Rick B]

Then why do you care that the number is "very large"? If it is just you,
and the number is not a relevant number, then just use what Access provided.
To reset your autonumber is a pretty complex process that basically involves
creating a new autonumber field.

If the actual number is not important, then you should be okay with what you
currently have.

 

[Guest]

1) Backup your database.
2) Delete the record with large number in the original table.
3) Compact your database.
4) Try to add a new record to the original table and see if there's still
the gap. In A97 compacting could resolve your problem. Not sure if it works
OK in newer versions of Access.

Try and see what happens. If it does not work then:
5) Create and run query to create new table, eg.:
SELECT [tblYourTable].* INTO newTable
FROM tblYourTable;
6) Delete tblYourTable. Remove all references before you delete the table.
7) Rename newTable to tblYourTable.
8) Re-create references.

9) Optionally you can create and run and additive query to add a record
(records) that you deleted at 2).
Don't include an autonumber field in the query.

HTH

Vlado

Try to compact the database.

 

[Guest]

What do you mean by "query report"?

The only user is me. If you had read the message, you would have noted
that
I am using the autonumber to generate a query report.

 

[Guest]

There's a simpler way how to do it but it may be done (without risk) only if
there are no references between tables, ie. references on your autonumber.

1) Backup your database.
2) Open the table in design view.
3) Remove autonumber field and save the table.
4) Add a new autonumber field and save the table.

Bear in mind that this is a RISKY BUSINESS!!!

Vlado

1) Backup your database.
2) Delete the record with large number in the original table.
3) Compact your database.
4) Try to add a new record to the original table and see if there's still
the gap. In A97 compacting could resolve your problem. Not sure if it
works OK in newer versions of Access.

Try and see what happens. If it does not work then:
5) Create and run query to create new table, eg.:
SELECT [tblYourTable].* INTO newTable
FROM tblYourTable;
6) Delete tblYourTable. Remove all references before you delete the table.
7) Rename newTable to tblYourTable.
8) Re-create references.

9) Optionally you can create and run and additive query to add a record
(records) that you deleted at 2).
Don't include an autonumber field in the query.

HTH

Vlado

Try to compact the database.

The only user is me. If you had read the message, you would have noted
that
I am using the autonumber to generate a query report.

 

[Joseph Meehan]

The only user is me. If you had read the message, you would have
noted that I am using the autonumber to generate a query report.

I am sorry you seem to have taken some offence. None was intended.

My response was a canned response I use since this question comes up so
often. You are far from the first person to discover this. Maybe Access
should pop up a warning notice anytime someone chooses to use autonumber for
the first time.

However I may suggest that the advice stands even if you are the only
user. It is best not to use autonumber anytime that it may seen by the user
even if it is you. It should be without any meaning other than to link data
in Access. It is close to worthless as a tool to order data.

 

[Guest]

Joseph:

I've been searching this KB for a month on how to consecutively number rows
in a query, and everything I have found does not work. I have tried the
"Serialize" function below, and it ends with an error. I have tried using
DMax with no luck. There were a few other things I have tried but they have
not worked either. Why is this such a difficult task to perform? It seems
so simple. Here is my scenario:

I have a query that I use to select and format street address data from a
main table to provide me a subset of addresses that I can port into a mapping
program. In addition to the formatting, I add a field for today's date to
each record (done easily using "Now()") which I call DateID. Finally, I
would like to add a unique sequential number to each record starting with 1
and incrementing by 1 for the whole query, which I call the SequenceID. This
would result in the following fields of data (with example data):

PrimaryID, Address, DateID, SequenceID
2356, 123 Main ST, 3/8/2007, 1
3789, 456 Oak RD, 3/8/2007, 2
1348, 789 Elm CT, 3/8/2007, 3
....

Where PrimaryID is the primary key brought over from the main table.

How can I do this?! Thanks in advance.

Steve

 

[Guest]

I too have this problem in my access 2007 table....the auto number field does
not reset to the lowest number avaliable for a new record. In my table I have
about 800 records, and the next new record comes in at 615784. This would not
mater to me as it's just a key identifier. but it me this suggests coruption
and problems with the database

running access 2007 jet 4 version 4.0.8618.0 (I reference the jet 4 thing as
this was and old issue that was fixed for older access versions by updating
jet4)
I have also ran the compact repair tool several times with the database open
and closed with no change in the number (ever time i test the database to see
if it's fixed the number gets bigger too)

 

[M Skabialka]

Have you tried to decompile it then recompile it? Make a copy first of
course as this is an undocumented feature of Access.

msaccess.exe /decompile "C:\YourDB.mdb"

Mich

 

[Guest]

I have not tried to decompile and recomple, I'll see if that helps

 

[Guest]

tried decomple and recompiling with no change in the new record start number
each one still comes in at 615490+1 (+1 for each time i test it)

 

[Guest]

I think A2002 has the same problem. Problem = bug.

Vlado