No I have a better one... CREATE GENERATOR (or CREATE SEQUENCE) depending on
your SQL dialect... Interbase uses Generator, Postgre uses Sequence, for
example
then execute a passthrough query SELECT NEXTVAL('sequencename') in Access
which does the whole thing in one go
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jeremy Coulter
> Sent: Monday, 5 March 2001 21:42
> To: Multiple recipients of list database
> Subject: RE: [DUG-DB]: Getting unique ID through SQL
>
>
> Patrick. Under SQLServer, there is a system Stored proc. called
> "LOCK" that
> from memory will lock the record until you call unlock. I THINK this might
> help you.
>
> i.e. call the storedproc. LOCK do the select, then the update then
> unlock....or better still do the select AND the update in one
> Storedproc....this means only one connection
>
> Jeremy Coulter
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Patrick Dunford
> > Sent: 05 March, 2001 8:11 PM
> > To: Multiple recipients of list database
> > Subject: [DUG-DB]: 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 offfor 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/
> >
> > ------------------------------------------------------------------
> > ---------
> > New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> > Website: http://www.delphi.org.nz
>
> ------------------------------------------------------------------
> ---------
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
