Is it possible to insert a record between autonumbered records?

[Plec]

I'm using an autonumbered field, incrementing by 10, and will need occasionally to insert a record between those autonumbered e.g. record 15, between 10 and 20. Is this possible? I'm using Access 2002. Many thanks.

[vb5prgrmr]

Yes it is possible but depending upon how many records are in that table, the last part of this solution may take some time to execute...

You will need to issue an alter table statement to drop the primary key constraint. Then you will be able to insert the record, which will actuall be inserted at the end of the recordset but if you pull the data out ordered by the primary key, then it will come out in the order you want. Which means of course that you will have to issue another alter table statement to reapply the primary key...



Good Luck

[Plec]

Yes it is possible but depending upon how many records are in that table, the last part of this solution may take some time to execute...

You will need to issue an alter table statement to drop the primary key constraint. Then you will be able to insert the record, which will actuall be inserted at the end of the recordset but if you pull the data out ordered by the primary key, then it will come out in the order you want. Which means of course that you will have to issue another alter table statement to reapply the primary key...



Good Luck

Thanks Vb5 for your speedy reply. As I'll probably be inserting more records than I originally thought and the final record set will only amount to 400 or so, it might be easier to forgo the autonumber option and enter the key manually.

Many thanks again.