How To - Reindex/AutoNumber Tabel In Access DB?

Is there a way to re-index an AutoNumber field in an access 2003 table?  Basically I have this table called "Events" and it uses AutoNumber type for one of its fields for the records in the table.  The AutoNumber skips around due to records being added/deleted.

The problem I am faced with is my form shows/sorts the records by AutoNumber from table Events and so now all the data is mixed up.  I want to be able to show the records on the form sorted by date bot record AutoNumber ID.
LVL 1
aehare70Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
One way:

1) Backup your MDB
2) Delete the Auto Number Field from your table
3) From the Access menu ... >>Tools>>Database Utilities>>Compact and Repair
4) Open the table in design view
5) Add a new Auto Number and save the table design.
Done.

mx
0
 
Chuck WoodConnect With a Mentor Commented:
The only way I have found to do this is:

1. Create a copy of the table-Structure Only.
2. Append the data in the table to the copy.
3. Delete the original table.
4. Rename the copied table to the name of the original.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The new Q is completely different than this Q.  Nothing vague about this Q.

mx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ALaRivaCommented:
I could be looking at this entirely wrong, but I don't think the OP is asking to reset the Autonumber.

What it sounds like is that the OP just needs to add an ORDER BY to their recordsource of their form, to sort their data by date.

Could be wrong though.

- Anthony
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Seems clear to me:

"Is there a way to re-index an AutoNumber field in an access 2003 table? "

mx
0
 
Chuck WoodCommented:
The first question in this question is:
"Is there a way to re-index an AutoNumber field in an access 2003 table? "
0
 
ALaRivaCommented:
That may be the first question, but if you read the detail, it's clear that he simply needs to order his data.

But, that could be because of the question being edited.
0
 
Chuck WoodCommented:
If his problem is ordering his data, the fact that some Autonumbers are missing because of deletions would have not affect the ordering. The order remains the same even if records are deleted.
0
 
ALaRivaCommented:
Yes, but he says . . .
"I want to be able to show the records on the form sorted by date bot [not] record AutoNumber ID."

- Anthony
0
 
Chuck WoodCommented:
Yes but why did he title the question: How To - Reindex/AutoNumber Tabel In Access DB?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Hey ... why not head to the new Q ...

mx
0
 
aehare70Author Commented:
Sorry for the confusion.  Yes, I wanted to know if there was a way to reindex the AutoNumber, but it seems that even if I had resolved that or reindex the AutoNumber that would not have fixed the real issue.  The real issue was the data being sorted on the form, which was answered in the other question.  Thank you for your responses, as always, it's much appreciated.
0
 
aehare70Author Commented:
Please cancel deletion. I will award points and close.  Thanks! :p)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.