Access Primary Key - Starting Number 1

[ProjectExplorer]

Is there a way of starting the Primary Key on an Access table from a certain number, say 5000, rather than from 1 (other than just creating 5000 dummy records and deleting them).

Any help appreciated.

Thanks

 

[alvechurchdata]

other than just creating 5000 dummy records and deleting them

I'm afraid that's what I did when I was faced with this problem. I created a 5000-row Excel table then imported it into a new table. Job done in under a minute and a happy marketing man who wanted his references to start from an impressively large number rather than having to send out mailshot ref 0000001.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Ed2020]

Yes, there is. Google for "access change autonumber start". The top two results have solutions that work.

Ed Metcalfe

Please do not feed the trolls.....

 

[ProjectExplorer]

Thanks for your help...

For any one who needs to do this....

1. Create your table with a primary key field
2. Create a copy of the table
3. Open the copy table and turn the primary key field into a long number (i.e. remove the little yellow key so it is no longer a primary key field).
4. Create a record in the copy table and enter a number one less than your required starting number (i.e. if you want your prmiary key to start at 5000 enter 4999).
5. Run an append query on the copy table - appending the record (4999) in the copy table to your original table.
6. Open your original table and delete the appended record (4999). Now the next record in your original table will start at 5000.

Job done - sounds long winded but is very quick in practice and works a treat.

Regards

 

[PHV]

A simpler way in SQL:
ALTER TABLE yourTable ALTER COLUMN yourField COUNTER(5000,1);

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ed2020]

Nice.

Have a star!

Ed Metcalfe.

Please do not feed the trolls.....