Getting unique ID through SQL

People will have seen my post on problems with PostgreSQL ODBC driver and MS
Access 97.

Access 97 has some problems when a record is added that contains a primary
key field of type SERIAL. This has something to do with the fact that the
value of the primary key is not actually generated until the record is sent
to the server.

It seems it is easiest for me to get the unique ID from the server myself
and insert it into the record when Access creates it.

In the realm of file based databases on a local machine it is easy to do
this: store the unique variable into a special table, read it out, increment
it and store it back. Very quick and there may only ever be one user.

Things become different on an SQL server because there may be multiple users
simultaneously accessing the database. Two SQL operations are required to
retrieve the variable's value and update it: a SELECT and UPDATE. Depending
on how fast your connection is, between the SELECT and UPDATE, someone else
could have run the same SELECT and got the same value back. Then when both
records are sent to the server with duplicate values in the same primary
key, one will fail.

What I need is some foolproof way of getting and updating the variable in
one operation. Is it going to be an Int4 stored in a special table, or can
it be a serial? Do I use a stored procedure or what? How do I get its value
from Access?

Whatever you think of Access, the alternative seems to be clunky PHP forms
with lots of code behind them for data entry and editing.

=======================================================================
Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/
  Peter replied, “Repent and be baptized, every one of you, in the
name of Jesus Christ for the forgiveness of your sins. And you will
receive the gift of the Holy Spirit.   The promise is for you and
your children and for all who are far off—for all whom the Lord our
God will call.”   -- Acts 2:38
http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304
=======================================================================
Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/