In addition to Dave's suggestion for using an Identity field in SQL
Server, I'll add that the behavior is slightly different. In Access,
the new AutoNumber key value is generated the moment you start typing
in the form. In SQL Server, the Identity isn't generated until later,
when the record is actually saved.

If you have code that depends on the Access behavior, it will need to
be changed. If you haven't already, check out my PowerPoint
presentation on techniques for using Access as a client-server
front-end to SQL Server databases. It's called "Best of Both Worlds"
at www.JStreetTech.com/Downloads. It includes some thoughts on when
to use SQL Server, performance and security considerations,
concurrency approaches, and techniques to help everything run
smoothly. It also shows some code to retrieve the just-generated
Identity value.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com