Duplicate values in autonumber field with unique index

[Ravensleach]

Access 2000. After doing an append query and stupidly including the autonumber id, I find that I can’t now add new records because it’s trying to use a duplicate autonumber. MS KnowledgeBase 257408 suggests updating the Jet ServicePack but I find I’m already uptodate.

The db compacts automatically on close. And yes, I have a backup, but it's a bit old, I can redo if I have to but it would be messy.
Backup, backup, backup. I know! Any suggestions? thanks

 

[PHV]

i = 1 + DMax("myID", "myTable")
CurrentDB.Execute ("ALTER TABLE myTable ALTER COLUMN myID COUNTER(" & i & ",1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ravensleach]

PHV, thanks for coming back so quickly ... looks like it might do the job but I'm being very slow this morning (it's morning where I am) ... what do I do with it?