Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Autonumber Primary Keys in MS-Access

 
Dave Turner
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1070
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic