Re: Access - ODBC - serial problem...

Philippe,

I believe the problem is that when you linked the table in Access, you
selected code as the unique field, instead of the id field.  Try unlinking
the table, then relinking, making sure you select the id field (or
combinations of fields) that is unique.

Thanks.

Michael


""Philippe Lang"" <philippe.lang@xxxxxxxxxxxxxx> wrote in message
news:6C0CF58A187DA5479245E0830AF84F420802A0@xxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> -----Message d'origine-----
> De : pgsql-odbc-owner@xxxxxxxxxxxxxx
[mailto:pgsql-odbc-owner@xxxxxxxxxxxxxx] De la part de Philippe Lang
> Envoyé : mercredi, 7. avril 2004 08:40
> À : pgsql-odbc@xxxxxxxxxxxxxx
> Objet : Re: [ODBC] Access - ODBC - index strange bug
>
> Hello,
>
> I was able to reproduce the bug, here it is:
>
> ---------------------------------
> CREATE TABLE public.test
> (
>   id serial PRIMARY KEY,
>   code int4,
>   f1 int4,
>   f2 varchar(50),
>   f3 text
> ) WITHOUT OIDS;
>
> CREATE INDEX test_code ON public.test USING btree (code);
>
> INSERT INTO public.test(code, f1, f2, f3) VALUES(10, 1, 'test1', 'ok1');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(20, 12, 'test2', 'ok2');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(30, 123, 'test3', 'ok3');
> ---------------------------------
>
> - Link this table to an Access database, with the "Postgresql" normal
driver. (Others don't work for me)
>
> - Open the linked table.
>
> - Type 20 in column code of the "insert" line, and insert the record.
> All values are mixed up, and the newly inserted line seems to have the
same values as the second record. (Because they have the same code?)
>
> - Close the table, and reopen it: the last inserted line has correct
values.
>
> - Type 31 in the same column now, things are fine. (Because code 31 was
not used before?)
>
> - Take away the index, and the bug disappears. No need to refresh the
table links or whatever.
>
>
> Can anyone reproduce that?
>
>
> Philippe
>
>
> My configuration:
> -----------------
> Postgresql 7.3.4 / 7.4.2
> Latest snapshot from psqlodbc.dll
> MS Access 2000
> MDAC 2.7 / 2.8
>
>
> -----Message d'origine-----
>
> Hello,
>
> I experience a strange bug when inserting a row into a table in which I
have recently added a few indexes.
> This table is linked to a MS Access database through ODBC (Latest known
snapshot from Hiroshi).
>
> When I insert a row, the serial id and different other columns are
completely mixed up. Onscreen, the inserted line inherits some values from
past lines (that have similar values), even the primary key! Things get back
to normal when I do a complete requery of the table, all values are correct,
as they should always have been.
>
> Everything works fine if I delete the indexes.
>
> I made a try with PG 7.3.4 and 7.4.2, same problem.
>
> I have tried playing with the driver options, especially the one related
to indexes, without success.
>
> Did anyone have the same problem already?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
>       message can get through to the mailing list cleanly
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings