On 02/17/2016 03:27 PM, John Van De Giessen wrote:
> Hi Adrian,
>
> Thank you very much for your time and effort in looking at this. I will send
> this on to the list as you suggest.
>
> My concern is that using the ODBC with Access 97 gives us very little
> control of what can actually happen.
The above is part of the problem, Access 97 is 19 years old and falling
behind every day. My guess is you will start seeing more
incompatibilities crop up as you go forward.
> We are not given a choice on how data types are mapped from PostgreSQL to
> Access. A money field in PG will become a double field when linked in Access
> 97 and we have no way that I know of the make this any different.
> I am also not aware of any way that we can structure a query in access that
> will satisfy the requirement of item 12. that you have noted below. I do not
> believe that it is possible in Access to do this using the ODBC.
>
> That leaves us with one of two conclusions. Either the changes in psqlODBC
> 09.05.0100 that break this are intentional and therefore the PG ODBC can no
> longer be used with Access if money fields are used, or there is a bug in
> the ODBC and needs to be fixed.
>
> Our other option is to avoid using the money data type altogether and
> instead use the Numeric data type.
> Numeric(15,4) maps in Access as a double but doesn't cause the same error
> that using the money field does. However, when using this you get a "Two
> users are attempting to changes the data at the same time" error when trying
> to update data in a recordset query. (Note: This error also occurs in the
> older versions of the ODBC driver.)
Inherit to casting from double precision to numeric and back. There is
loss of precision and Access sees this as an update, where the value it
calculates for the update field does not match exactly the value stored
in the database and returned to it.
> Numeric(18,4) maps as Text(18) in Access and seems to work just fine but it
> would be nicer to be mapped at least to a number field of some sort. However
> at the moment this seems to be our only option.
>
> If anyone knows of a way to make a PG "Money" or "Numeric(x,x)" data type
> map in Access to its "Currency" data type then we would be very pleased to
> know how.
If you are going to stay with Access 97 I'm thinking you will need to
stay with older versions of the ODBC driver.
>
> Again, thank you very much.
>
> Regards,
>
> John Van De Giessen
> Thyme Software
>
--
Adrian Klaver
adrian.klaver@aklaver.com