Convert Autonumber back to Increment 1

[mkasson]

Despite Access telling us that once you convert an Autonumber from Increment to Random you can't go back, we converted to Random. Now we really want to go back. I'm not sure how.

We have a bunch of code and forms/reports written, so I can't just start using a copy of the db because all the references will be wrong.

Anyone have any ideas on how to convert back?

Thanks.

- MSK

 

[LittleSmudge]

Only a knife and fork approach I think

Sort the table as you want it listed
Change old AutoNumber to LongInteger
Add new field in master table of type AutoNumber, Increment and call it NewAutoId
Save it to populate all the records with new AutoNumber values

Add new field in each table on the Many end of the Relationships and call them NewAutoRef ( of type Long Integer )

For each of these tables write an update query that, for each record, takes the OldFK value, looks up the equivalent in the Master table - finds the NewAutoId equivalent and then undates NewAutoRef

UPDATE tblMaster 
INNER JOIN tblSubOne 
ON tblMaster.OldAutoId = tblSubOne.OldRef 
SET tblSubOne.NewAutoRef = NewAutoId

Replace tblSubOne with each of your sub tables in turn


Then delete the OldId and OldRef fields and rename the new ones and remake your Relationships page



'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[mkasson]

Thanks.

Followed your first few steps. Don't have any relationships established. It was actually easier than I thought because access changed surprisingly little when I changed the name of the KeyField to OldKeyField. Created KeyField as new autonumber field. Just really had to change the forms. Reports were fine.

Thanks again

- MSK