File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Autonumber Primary Keys in MS-Access" Watch "Autonumber Primary Keys in MS-Access" New topic
Author

Autonumber Primary Keys in MS-Access

Dave Turner
Ranch Hand

Joined: Mar 13, 2001
Posts: 60
hi
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?
Dave
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
Dan
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.
Bill
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
Dave
 
 
subject: Autonumber Primary Keys in MS-Access