 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007Changing to Autonumber Mid-Stream?
I have been working in a database that I assumed had the primary key [Program ID] as an Autonumber field. Turns out the creator of the DB instead put in some VB code that simulates Autonumber using the DMax function to increment the primary key by one when someone creates a new record (he had his reasons for doing this). This is causing havoc when there are multiple users creating new records at the same time. Some users are having their inputs rejected when they try to save them due to overlapping ID numbers. I tried to go into the back end file and change the [Program ID] field to Autonumber, but Access wouldn't let me, since there is already data in the database. I really want [Program ID] to be Autonumber. Does anyone have a nifty, easy way to make that change to my back end file? I understand that I'll need exclusive access to the file to make the change, but what else do I need to do? Thanks, Eric As you found, you can't change the field. But you can create a new table with an AutoNumber, and import the existing records. 1. Select your table on the Tables tab of the Database window. 2. Copy (Ctrl+C), and Paste (Ctrl+V.) Answer the dialog that you want "Structure Only" so you get no records. 3. Open the new copy in design view. Delete the Number field. Add an AutoNumber field. Save. Close. 4. Create a new query, using the old table that has the data. Change it to an Append query (Append on Query menu.) Answer the dialog that you want to append to the new table. Map the fields (or use the wildcard if they have the same name and are in the same order.) Run the query. The new table now has an AutoNumber field, populated with the old values. At this point, you can delete the old table, compact the database, and then rename the new table with the same name as the old one. I strongly suggest you ensure Name AutoCorrect is turned off before you to that: http://allenbrowne.com/bug-03.html You may need to delete any existing relations on that field (Tools | Relationships), before you can delete the table. Then create those relations again to the new table.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >I have been working in a database that I assumed had the primary key >[Program [quoted text clipped - 18 lines] > to > do? That sounds pretty straightforward. Thanks for the input. I'll give a try late tonight when no one is in the database. > As you found, you can't change the field. But you can create a new table > with an AutoNumber, and import the existing records. > > 1. Select your table on the Tables tab of the Database window. > > 2. ... One thing I discovered is that if you have any validation in fields, or certain fields are required, then the append query will leave those fields out if the existing data don't work with the validation, or if there is no existing data for a required field. I wanted to keep all the existing data (correct or not), so I had to strip out the validation and make all fields "not required". Otherwise, works great! I wish there was a way to copy/paste relationships from the old table to the new one... Yes, that would be correct. JET applys the rules when appending the existing (bad) data to the new table. Copy'n'paste relations is an interesting idea. It's not something you do often though. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > One thing I discovered is that if you have any validation in fields, or > certain fields are required, then the append query will leave those fields [quoted text clipped - 9 lines] > the > new one... > One thing I discovered is that if you have any validation in fields, or > certain fields are required, then the append query will leave those fields [quoted text clipped - 4 lines] > > Otherwise, works great! Great work! Those integrity constraints can be a real inconvenience when you just want to stuff a load of data, garbage or otherwise, into a table! Have you considered changing all your columns to MEMO to avoid pesky data typing problems too? What about merging all the data into one table? Forget mdb: I think .ini files could be the way forward for you... Jamie. -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.