This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Autonumber Primary Keys in MS-Access Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Autonumber Primary Keys in MS-Access" Watch "Autonumber Primary Keys in MS-Access" New topic

Autonumber Primary Keys in MS-Access

Dave Turner
Ranch Hand

Joined: Mar 13, 2001
Posts: 60
I have an MS Access database which my java app connects to using JDBC. Some of the Table's Primary Key Fields are 'Autonumber'. While i have been testing the app, records have been created and later removed, however the Autonumber remembers the last number it stored in the database, so when i add a record to my now empty table, it is given a value greater than 1 which is what i would like the first record to have (a value of 1). Does anyone know of a way that i can reset the Autonumber count when i have emptied a table?
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
If your asking for a jdbc way to do it, I don't know if there is one.
If you go into access and use the compact utility, it will reset it to one (as long as you emptied the database
bill bozeman
Ranch Hand

Joined: Jun 30, 2000
Posts: 1070
Yeah, I don't think there is a way to do that. In theory it shouldn't matter, and if it does matter, then I wouldn't use an autonumber. Unless I really don't care about the primary key and it is only used for relationships, I don't use autonumber and have a stored procedure that gets the highest number and add one to it before I do the insert on the next row. Of course you can't do stored procedures in Access, but you get the idea.
If you want to do it in Access, if the table is empty, you can create a copy of it, delete the original, and then rename the copy over the original. There is probably a function that will reset it, but I don't it offhand.
Dave Turner
Ranch Hand

Joined: Mar 13, 2001
Posts: 60
Thanks guys,
I'll try compacting my database then!
I was working with a number field and calculating the next index value before, but it seemed easier to let access handle the uniqueness etc, so i am doing.
It doesnt really matter if the index starts at 1 or 1001 programmatically, so long as each number is unique, but it will be less confusing for the end users of my java app to have numbers starting at 1.
Thanks again
Have you checked out Aspose?
subject: Autonumber Primary Keys in MS-Access
It's not a secret anymore!