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

Philippe,
Thanks for your response.  See below for comments.

--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> Hello,
>
> 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?

>
> There are 2 long threads regarding this, in this
> mailing-list:
>
> [ODBC] Access - ODBC - serial problem...
> and
> RE: [ODBC] @@IDENTITY (Was: Access - ODBC - serial
> problem)
>
> I suggest you have a look at them.

I've read various threads on this subject.  I don't
believe that I have seen the possible solutions
covered well so far.

>
> My only solution to this problem would be, at the
> moment:
>
> 1) From the client: after an insert in a bound
> subform, requery the whole subform.
> 2) From the server: use a stored procedure that
> inserts data, and returns the id to the client. That
> does not allow you to insert data into a linked
> table transparently, though...
>
>
> 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.

>
> Solution 2) is quite elegant...

I believe that this one (adding a timestamp field to
the table, and setting the value every time a record
is inserted or updated) is canonical in SQL
Server-land.  I found this solution less aesthetically
pleasing, because adding a column to a table just to
get around an Access problem seemed to me overkill,
and knowing the primary key value up front is an
unequivocal solution.

I guess all of this is very much OT for a PostgreSQL
group, but I am including it anyway in case this is of
interest to other readers.  I didn't find much useful
stuff when I searched the Access groups, perhaps I
just didn't use the right search terms.

>
> Bye
>
> Philippe
>
>
> -----Message d'origine-----
> De : pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] De la part
> de Jeff Eckermann
> Envoy� : mardi, 20. avril 2004 16:57
> � : psql-odbc@postgresql.org
> Objet : [ODBC] Dealing with Insert Problems with
> Access
>
> I have just deployed a MS Access client-server
> application with a PostgreSQL 7.2.4 backend (not
> that the version matters in this case).  I am
> bumping up against the famous "#Deleted#" in every
> field issue that comes up on this list from time to
> time.
>
> The problem is that, in this application, the data
> can be quite sparse for some records, with the
> result that some records only differ in their
> primary key value, which is a serial in PostgreSQL.
> When a new record gets created, Access fails to find
> a unique record with the same data values, so the
> error.
>
> I don't want to move to unbound forms/controls, so I
> am looking for a minimalist way to solve the
> problem, which would involve adding some "before
> insert" code which will specify a unique value to be
> inserted.  I am looking at two choices:
>
> * Do a "nextval" call to get the next serial value,
> and specify it explicitly.  This involves an extra
> network round trip (and an extra connection), but
> has the benefit of being unequivocal.
>
> * Add a timestamp field to the table, and specify
> the applicable value in my vba code.
>
> This app is not intensively used, so either solution
> would probably work fine in practice.  Is there any
> reason why I should prefer one over the other?  Or
> is there another kind of solution altogether that I
> could try?
>
> TIA
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for
> 25� http://photos.yahoo.com/ph/print_splash
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>





__________________________________
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25�
http://photos.yahoo.com/ph/print_splash