[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5127
  • Last Modified:

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.
0
aehare70
Asked:
aehare70
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 WoodCommented:
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 MVP, Access and Data Platform)Commented:
The new Q is completely different than this Q.  Nothing vague about this Q.

mx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now