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

--- 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 7.03.02.00 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!
http://my.yahoo.com