Hello,
I'm working with a database system that is based on an Access frontend
and a postgresql backend, connected via the psqlODBC driver.
That works quite nicely, but there are some problems.
We use updatable views extensively, because otherwise many forms would
be very slow.
Some of these views have calculated columns.
We often use the type numeric for these, especially if the column is an
amount of money.
Recently we saw a strange bug in these columns, the values would by
shown multiplied by 1000 or 10000, depending on the precicion of the
column (e.g. *1000 for a numeric(20,3)).
Strangely this happens only on some client computers, an only after
doing something that triggers the bug, like clicking on a hyperlink in
the access application.
As a work-around, I've converted the datatypes of these columns to
double, and the bug disappears. But I also had to compile the psqlODBC
driver for this to work, as we had other problems with double columns
due the precision problems reported by Jon Smith. Access would emit
errors like 'someone else has changed this record' when one tries to
update a record with a calculated column as double, because Access would
try to compare all fields in a row to the previous values.
That would fail due to rounding errors, but Access takes it as if
someone else has edited the data.
Anyway, now this seems to be fixed in commit
a5fed2338b59ae16a2d3a8d2744b084949684775.
Maybe the numeric bug has something todo with client locale,
because it looks like the decimal point is lost in the numbers,
I'm not quite sure how numeric is converted from the server to the
client, but we run a german locale, so on the client we use a comma as
decimal point. No idea why this sometimes fails. Maybe someone else has
a hint where I could look further...
best regards,
Oliver Freyd
P.S:
versions:
Access frontend (Access 2013 runtime)
postgresql server 9.1.13
psqlODBC driver 09.03.0300-1 (now self-compiled commit
d212612c7fd62853fc92da22eaf0790d22730eae
from 26 Sep 2014