By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

How to reset a Key field password

P: 6
I have a database with a lot of table relationships set and need to reset the main table's (ConceptID which is an auto numberand a Key field ). Is there a way to reset the ConceptID without removing all the relationships then altering the table field then reset all the relationships?
I tried this code but wasn't able to do it because of relationship.
Alter table [Concept]Alter Column [Conceptid]Counter(4000); When i run this i get the error message:
"Cannot change field 'ConceptID'. It is part of one or more relationships. Also, currently there are no records in any of the tables.
Jan 27 '14 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
Leno01: If you don't mind me asking; why?
Also, I just about deleted this thread based on the title:
"Key field password " in that it reads as if you are asking for a password (which doesn't appear to be the case).

The entire point of the autonumber is that it is never repeated, ever and that it is not suposed to be really used for anything meaningfull. For example, in the lab, there are lot numbers, these are non-repeating and sequential; however, I do not use these as the primary key because the lot numbering system might (and has) changed. Each record has the autonumber assigned as the primary key and it is this primary key that I have linked. Let them change the lot numbers all they want, I still have a fool proof tracking on the records.
  • Backup your database.
  • Open the table you wish to reset, delete all records. As this is in a relationship, all records in all other tables that depend on this one will have to have all of the related records deleted as well or you risk data orphans.
  • Close the table
  • Make a copy of your table that you wish to "reset"
  • Open only the copy in design view
  • Change the autonumber to numeric(long)
  • Save
  • Open/change the copy in/to normal view
  • In the newly changed field enter the value of 0 leave remaining fields empty. There should be no other records.
  • Save and Close the copy
  • Using the wizard create a simple select query on the copy
  • Save and run - it should only have the one record
  • Switch to design view for the query
  • Change the query to an "Append" query selecting the original table to append to.
  • Run the append query
  • Open your original table and delete the "0" record.
  • Your next new record will start with "1" in the autonumber field.
Jan 27 '14 #2

P: 6
I am using the auto number as a key field. however, I was in development and need to start the autonumbers at a specific autonumber for a type of tracking i am doing.
I know it doesn't make sense but a lot of things in our life done and we have to work around them.
Thank you so much for the work around - it certainly beats having to remove all the relationship and add them back.

I dont know who to tell but maybe you can pass this on.
I have tried a lot of access help sites and none of them can lite a candle to this site.
Thanks again
Jan 27 '14 #3

Expert Mod 5K+
P: 5,397
Glad I could help.

Found this technique some, oh, well, ages ago in a textbook for ACC1997 I think... been way too many moons, beers, and kids (^_^)

The Site Admins, other Moderators, and Experts will be along shortly... it's always nice to have positive feedback.
Jan 27 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.