Re: Access - ODBC - serial problem...

Having fought with this before myself, I can tell you that this can be made
to work, but it's not exactly ideal. With an Access front-end, you try to
make the back-end plug-and play. You don't want to have to execute a
pass-through query to be able to add a row through a bound form. It would
certainly be nice if the ODBC driver for PostgreSQL would work with Access
like other drivers do (hovever that works), and allow Access to see the new
Serial value after adding a row.

On Thursday, April 08, 2004 10:12 AM, Ray Aspeitia
[SMTP:aspeitia(at)sells(dot)com] wrote:
>
> I haven't tried this in Access, but why not fetch the record
> identifier first then insert the record like below. Retrieving the id
> first makes sure that you get the right id, because you might be in a
> pooled connection environment, and you might not get the right id
> after the insert.
>
>
> SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
>
> INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
>
> or
>
> INSERT INTO test (record_id,code) VALUES
> (currval('schema.sequence_for_table_record_id'),20);
>
>
> Ray A.
>
>
> >Hello,
> >
> >I have made some more tests regarding my precedent post "[ODBC]
> >Access - ODBC - index strange bug", and in fact it is not
> >index-related. The bug is just more visible when an index is used,
> >apparently.
> >
> >The problem comes from the serial identifier, which is not know from
> >the client at the time the record is being inserted.
> >
> >With the table described below, imagine I do, from the client:
> >
> >insert into test (code) VALUES (20);
> >
> >How does the client know the id that has been given to the record?
> >With ethereal, I could see Access fetches the id by doing a
> >
> >select id from test where code = 20"
> >
> >Of course, another record has the same code, and the wrong id is
> >being fetched back. This explains what I have noticed, and that is
> >explained below...
> >
> >Is there a solution to that, except inserting records with PL-PGSQL,
> >which I already do in some cases, by the way...?
> >
> >Is that an Access-related problem? Does it work with SQL Server?
> >
> >
> >Thanks for your feedback...
> >
> >Philippe
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend