Re: Dealing with Insert Problems with Access

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