Skip site navigation (1) Skip section navigation (2)

Re: Access/Postgresql problem with text fields recognized as memo

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: Justin Dahl <justind_lst(at)ussonet(dot)net>, pgsql-odbc(at)postgresql(dot)org
Subject: Re: Access/Postgresql problem with text fields recognized as memo
Date: 2005-02-09 16:54:26
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
--- Justin Dahl <justind_lst(at)ussonet(dot)net> wrote:

> I've ran into this problem before and then abandoned
> it out of 
> frustration, I'm coming back to it again.
> Using Access 2000
> Postgresql 7.3.4
> ODBC driver on WinXP
> All tables I create have a unique index called "id"
> that is of type 
> "serial".
> Setting "Row Versioning" in odbc driver does not
> make any difference.
> If a table is created with 3 columns
> id (serial)
> field1 (varchar(255))
> field2 (text)
> This table is linked into an access file.
> If data is typed in at the table view of the file.
> Scenario 1:
>     Only a value is filled in for field1.
>      The information is inserted normally and
> everything is happy and good.
> Scenario 2:
>     Information is filled in for both field1 and
> field2.
>     The information is inserted normally and
> everyting is happy and good.
> Scenario 3:
>     Only a value is filled in for field2.
>     The information is inserted normally, but all
> fields will display 
> #Deleted across them appearing to the user to have
> failed.

My understanding (admittedly limited) is that, after
making an insert, Access will attempt to fetch the
record back again, in order to verify that the insert
worked as expected.  Not knowing the primary key value
(because it is an autonumber id), Access uses the
known column values to identify the record that needs
to be fetched back.  Since Access cannot compare Memo
values with each other, the action fails where only a
Memo value is available.  This will also be fragile
where a field1 value is supplied, unless you are
enforcing uniqueness on that field.

My workaround for this problem was to fetch the
primary key value at the time of data input, i.e. have
the BeforeInsert event trigger code that, if the
primary key is unassigned, will run a "select
nextval()" on the appropriate sequence, and assign the
returned value to the primary key field.

> Scenario 3 is what is bothering me the most. I have
> done quite a bit of 
> testing with this and this only occurs in
> conjunction with fields 
> recognized as type "memo" by access.
> Please keep in mine that I have a unique index field
> called "id". The 
> same thing occurs wheather "Row Versioning" is
> turned on or off on the 
> driver.
> I lean towards this being a MSAcess issue, can
> anyone give any input on 
> this and how i could correct it? Let me know if more
> info is needed.
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)

Do you Yahoo!? 
The all-new My Yahoo! - Get yours free! 

In response to

pgsql-odbc by date

Next:From: laurie.burrowDate: 2005-02-09 18:23:16
Subject: Re: Access/Postgresql problem with text fields recognized as
Previous:From: Dave PageDate: 2005-02-09 16:54:16
Subject: FW: [pgsql-www] Thanks plus three observations & some questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group