Thread: Dealing with Insert Problems with Access

Dealing with Insert Problems with Access

From
Jeff Eckermann
Date:
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

Re: Dealing with Insert Problems with Access

From
Jeff Eckermann
Date:
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

Re: Dealing with Insert Problems with Access

From
"Philippe Lang"
Date:
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

Re: Dealing with Insert Problems with Access

From
Jeff Eckermann
Date:
--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:


>
> 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

No.  The "nextval" function returns the next available
sequence number, and advances the sequence at the same
time.  So a subsequent call will never get the same
number.

> 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

With MVCC, it is almost never necessary to lock a
table (I've never needed to).

> 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...

Yes, I am sure this solution would work fine.  Which
is why I had to think a bit before choosing.

>
>
> Philippe Lang





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

Re: Dealing with Insert Problems with Access

From
Jeff Eckermann
Date:
--- Greg Campbell <greg.campbell@us.michelin.com>
wrote:
> I am not absolutely sure about this, so I hope those
> who are don't
> crucify me.
> I think using underlying tables with OIDs and/or
> using a timestamp
> (default value of  Now())
> help the ODBC driver keep track of which record is
> which.
> I think the #DELETED# is because after some change -
> it doesn't know
> which record is which.
> I do not think the VBA code even needs to refer to
> the OID or the
> timestamp for this to work.

You are thinking of a different case, probably more
frequent in practice.  That one happens with updates:
the story IIRC is that, before saving an update,
Access fetches back the record and compares it with
the saved "old" values, to make sure that the record
has not been altered since it was first displayed to
the user.  Some oddities can cause Access to be fooled
into giving spurious errors.  I know that checking
"row versioning" in the driver fixes it, apparently by
substituting a guaranteed unique row id (ctid? I
forget).  There is more on this in the psqlodbc
project site.

>
> Jeff Eckermann wrote:
>
> >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
> >
> >
> > begin:vcard
> fn:Greg Campbell
> n:Campbell;Greg
> org:Michelin North America;ENG-IT
> adr;dom:;;2420 Two Notch Road;Lexington;SC;29071
> email;internet:greg.campbell@us.michelin.com
> title:Systems Engineer
> tel;work:803-951-5561
> tel;fax:803-951-5531
> x-mozilla-html:FALSE
> version:2.1
> end:vcard
>
>





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