Thread: [INTERFACES] MSAccess problem deleting rows

Hello,

    I have a problem running M$Access on a PostgreSQL database,
deleting rows from 'raw' Access, i.e. doing an open on a table
selecting a hole row and then deleting this row. The problem
arises when the table includes a float:

create table effektivitet
  (
    id serial ,
    value float,
    primary key (id)
  );
create unique index ix_effekt_uniq on effektivitet (id);

    Consider a row with the following values:
id  |  value
5   |  1.1666666666666666666....

Depending on how wide my float column is displayed, M$Access will
do something like: 'DELETE FROM effektivitet WHERE id = 5 AND
value = 1.6667;'. Of course there is no such row and M$Access
displays some (stupid) message like "Others have been changing ...."
and aborts the operation. This also goes for an update on such a
table. I can of course manually delete the row by doing a
'delete from effektivitet where id = 5;' either in the SQL creator
within Access or directly on the Linux box.

   This also happens with following piece of VB code:

Set rstEffective = UT32_recordset("Select * From [Effektivitet] Where [Id] = '" & Employee & "' ;")
With rstEffective
    .Edit
    !Value = rstEffective!Value + NewValue
    .Update
End With


   How can I make M$Acess use the unique index column for pointing
out this specific row in stead of generating a where clause containing
all fields in the table?

I am running PostgreSQL 6.4.2 on RedHat 5.0 and kernel 2.0.32. (I
downloaded and installed it just a few weeks ago. This goes for
psqlODBC as well).


    Greetings,

  Leif
  (leif@danmos.dk)

leif@danmos.dk wrote:

>    Hello,
>
>     I have a problem running M$Access on a PostgreSQL database,
> deleting rows from 'raw' Access, i.e. doing an open on a table
> selecting a hole row and then deleting this row. The problem
> arises when the table includes a float:
>
> create table effektivitet
>   (
>     id serial ,
>     value float,
>     primary key (id)
>   );
> create unique index ix_effekt_uniq on effektivitet (id);
>
>     Consider a row with the following values:
> id  |  value
> 5   |  1.1666666666666666666....
>
> Depending on how wide my float column is displayed, M$Access will
> do something like: 'DELETE FROM effektivitet WHERE id = 5 AND
> value = 1.6667;'. Of course there is no such row and M$Access
> displays some (stupid) message like "Others have been changing ...."
> and aborts the operation. This also goes for an update on such a
> table. I can of course manually delete the row by doing a
> 'delete from effektivitet where id = 5;' either in the SQL creator
> within Access or directly on the Linux box.
>
>    This also happens with following piece of VB code:
>
> Set rstEffective = UT32_recordset("Select * From [Effektivitet] Where [Id] = '" & Employee & "' ;")
> With rstEffective
>     .Edit
>     !Value = rstEffective!Value + NewValue
>     .Update
> End With
>
>    How can I make M$Acess use the unique index column for pointing
> out this specific row in stead of generating a where clause containing
> all fields in the table?
>

Just have a unique index on the table and have the "recognize unique index" flag on in the driver
settings.    You could even use the "oid" as the unique index, by turning on that flag in the driver
settings.

If you don't have an actual index, Access should ask you what fields are unique.

***** BUT, I think if I recall you may need to use the "row versioning" feature.  I think Access will
always use all the fields in an update unless you use this.

You can check the faq on this too.

Byron