Changing to Autonumber Mid-Stream?

[Guest]

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

 

[Allen Browne]

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.

 

[Guest]

That sounds pretty straightforward. Thanks for the input. I'll give a try
late tonight when no one is in the database.

 

[Guest]

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...

 

[Allen Browne]

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.

 

[Jamie Collins]

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!

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.

--