Better than tying yourself into a database-specific solution (such as the autonumber data type or sequences), or be restricted by driver implementations,
you should consider using a proper key generation algorithm. For example the high-low algorithm which apes Oracle's sequences. For this you create a high key table for each table in your database. When a process wants to create a new row in a table, the process creates a lock on that table's high key table, reads the value in there and increments it, then releases the lock. Now this process knows that it has a unique value for this primary key, regardless of how many other processes tried to add a new record at the same time. It also means the process has the PK value (something which people have already pointed out is a headache with autonumber).
This is by no means the best, or only, strategy - it just happens to be the one implemented in the application I am currently working on. Google for "key generation patterns"; you are sure to turn up lots of ideas.
[ November 24, 2005: Message edited by: Paul Sturrock ]