Easiest way I've found is to read NextVal on the relevant sequence, and use
the value you get for the key field.
Since the field will be loaded, the "default" function that assigns the
number, kind of like an Access Autonumber - will *not* override the value
you manually insert.
So:
Get nextval on sequence
.addnew
recordset!idfield = thenextvalyougot
At 11:38 AM 8/22/06, Andreas wrote:
>Hi,
>
>I'd like to write to PG from Access2000 with ADODB.
>I got it running, but I'd like to know the sequence-nr that gets used when
>a new record is created.
>
>I open a recordset with
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
>then I call its AddNew method, the columns get filled and the recordset is
>closed.
>The primary key is a SERIAL.
>Is there a way to read the pkey before I commit the record?
>Access knows it's Autonumbers after AddNew when I use it's JET db-engine.
>
>A "select currval...." after the AddNew yields
>Error while executing the query;
>ERROR: currval of sequence "personen_person_id_seq" is not yet defined in
>this session
>
>I'd rather not use currval anyways. I'd prefer to have the program not
>know to much of the structure that sits in the db backend, like the
>sequence's name in this case.
>
>Can I get something like this :
>
>rs.AddNew
> lngID = rs!id
>
> rs!field1 = value1
> rs!field2 = value2
>....
>rs.update
>rs.close
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster