Re: Dealing with Insert Problems with Access - Mailing list pgsql-odbc

Hello Jeff,

> > I have faced the same problem as you, although the result was not a
> > #Deleted# in fields. With my driver version, Access chooses the
first
> > occurrence of a field with the values that have been inserted. If
some
> > previous records contain the same values, the wrong id is being
> > fetched back!
>
> I haven't seen this.  Are you saying that different versions of the
PostgreSQL driver behave differently > in this case?

I happened to see these #Delete# in fields, but only when there was some
sort of conflict during the insert, not when records with the same
values already existed in the table before the insert... I'm not sure
wich driver version you have. I use the latest builds, 208 and 209.

> > Regarding your solutions:
> >
> > Solution 1) must be a problem when used in a multiuser scenario.
When
> > tow different persons insert data in the same time, this can be a
> > problem.
> > Locking from the client won't work, I'm sure.
>
> This is the solution I chose:  I added code to the "before insert"
form event that does a "select >
> nextval" on the sequence, and sets the record id in the form to that
value.  This is multiuser safe,
> BTW.
>
> I was concerned at first about the effect of an extra network round
trip, but since the "before insert"
> event fires at the start of the entry, and entering a new record takes
some time, the extra overhead is
> no issue at all.

Between the moment you have fetched the next id, and the moment Access
does the insert into the table, another user or process could so the
same thing, and fetch the exact same next id, which would result in an
insert conflict, no? If I'm not wrong, in order to have something fully
multiuser-safe, you should use a stored procedure, and even switch to a
serializable isolation level. I personally use LOCK TABLE. Then, with
the id your stored procedure gives you back, you open the record from
Access.

The problem, with that solution, is that it requires a stored procedure
call prior to typing any data. It does not solve the problem we may
encounter with a raw linked table or subform in Access, when the data
being inserted is already present in another row of the table. In that
case, as mentioned in posts last week, the id fetched back may be wrong!
That's really dangerous...

That's why I like your second solution: there must be a way of forcing
Access to generate client-side a timestamp (with miliseconds) or some
sort of unique random number, before each insert. In that case, the id
fetched back by Access is 99.999999% the one you expect. If I'm not
wrong, this kind of "trick" is even advised with an SQL Server backend,
too...


Philippe Lang