NUMERIC type makes trouble in MS Access

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
30 messages Options
12
Reply | Threaded
Open this post in threaded view
|

NUMERIC type makes trouble in MS Access

Tobias Wendorff
Hi there,

I'm experiencing problems with the ODBC driver on Windows 7 (and 10)
on 32 and 64 bit in Microsoft Access 2003, 2010 and 2016 (32, 64 bit).

My PostgreSQL server runs on Debian (64-bit): 9.6.7-1.pgdg80+1
Tested ODBC drivers: psqlodbc_09_06_0500 & psqlodbc_10_01_0000

Summary of the problem:
The current behavior of the ODBC driver creates an error in Access,
since the incoming values are longer than the field type. This leads
into a truncation warning (with many messageboxes) and no values
are shown at all. The expected behavior is either TEXT (bad) or
DOUBLE (without any precision details).

Documentation:
As stated in the docs, I've played with the setting "Unknown Sizes";
they don't help, but give me a different result:
- "Maximum" & "Longest" behave equal (see blow)
- "Don't Know" creates an error in Access (types can't be identified)

Deeper analyis:
1. column type NUMERIC (without further specification)
PostgreSQL's ODBC driver:
- field type: decimal
- precision: 28
- decimal places: 6
- decimal places display: automatic
=> I'm getting the truncation warning, no values shown (only errors).

commercial ODBC driver:
- field type: double
- decimal places display: automatic
=> Display works as expected.

2. column type NUMERIC (40, 32)
PostgreSQL's ODBC driver:
- field type: text
- length: 40
=> All values shown with trailing zeros and it's a string.

commercial ODBC driver:
=> same behaviour

3. column type NUMERIC (26, 18)
PostgreSQL's ODBC driver:
- field type: decimal
- precision: 26
- decimal places: 18
- decimal places display: automatic
=> As expected, truncation to '12345.012345678901234568'.

Lossy workaround:
A view, which casts NUMERIC to FLOAT.

Could anyone have a look please?

Best regards,
Tobias

Tested with this syntax:
DROP TABLE IF EXISTS demo;
CREATE TABLE DEMO (v numeric);
INSERT INTO demo VALUES (1.0);
INSERT INTO demo VALUES (1.0123456789);
INSERT INTO demo VALUES (1.01234567890123456789);
INSERT INTO demo VALUES (12345.012345678901234567890123456789);

ALTER TABLE demo ALTER COLUMN v TYPE numeric(40,35);
ALTER TABLE demo ALTER COLUMN v TYPE numeric(26,18);


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Inoue, Hiroshi-2
Hi Tobias,

On 2018/02/13 2:44, Tobias Wendorff wrote:

> Hi there,
>
> I'm experiencing problems with the ODBC driver on Windows 7 (and 10)
> on 32 and 64 bit in Microsoft Access 2003, 2010 and 2016 (32, 64 bit).
>
> My PostgreSQL server runs on Debian (64-bit): 9.6.7-1.pgdg80+1
> Tested ODBC drivers: psqlodbc_09_06_0500 & psqlodbc_10_01_0000
>
> Summary of the problem:
> The current behavior of the ODBC driver creates an error in Access,
> since the incoming values are longer than the field type. This leads
> into a truncation warning (with many messageboxes) and no values
> are shown at all. The expected behavior is either TEXT (bad) or
> DOUBLE (without any precision details).
>
> Documentation:
> As stated in the docs, I've played with the setting "Unknown Sizes";
> they don't help, but give me a different result:
> - "Maximum" & "Longest" behave equal (see blow)
> - "Don't Know" creates an error in Access (types can't be identified)
>
> Deeper analyis:
> 1. column type NUMERIC (without further specification)
> PostgreSQL's ODBC driver:
> - field type: decimal
> - precision: 28
> - decimal places: 6
> - decimal places display: automatic
> => I'm getting the truncation warning, no values shown (only errors).
>
> commercial ODBC driver:

What is the commercial ODBC driver?

> - field type: double
> - decimal places display: automatic
> => Display works as expected.
>
> 2. column type NUMERIC (40, 32)
> PostgreSQL's ODBC driver:
> - field type: text
> - length: 40
> => All values shown with trailing zeros and it's a string.
>
> commercial ODBC driver:
> => same behaviour
>
> 3. column type NUMERIC (26, 18)
> PostgreSQL's ODBC driver:
> - field type: decimal
> - precision: 26
> - decimal places: 18
> - decimal places display: automatic
> => As expected, truncation to '12345.012345678901234568'.
>
> Lossy workaround:
> A view, which casts NUMERIC to FLOAT.
>
> Could anyone have a look please?
>
> Best regards,
> Tobias
>
> Tested with this syntax:
> DROP TABLE IF EXISTS demo;

> CREATE TABLE DEMO (v numeric);
> INSERT INTO demo VALUES (1.0);

The column v is numeric item without precision.
Are the following insertions possible using the commercial DBMS?

> INSERT INTO demo VALUES (1.0123456789);
> INSERT INTO demo VALUES (1.01234567890123456789);
> INSERT INTO demo VALUES (12345.012345678901234567890123456789);


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Hi Hiroshi,

thanks for your reply!

Am Sa, 26.05.2018, 14:23 schrieb Inoue, Hiroshi:
>> commercial ODBC driver:
>
> What is the commercial ODBC driver?

Devart ODBC Driver for PostgreSQL

> The column v is numeric item without precision.
> Are the following insertions possible using the commercial DBMS?
>
>> INSERT INTO demo VALUES (1.0123456789);
>> INSERT INTO demo VALUES (1.01234567890123456789);
>> INSERT INTO demo VALUES (12345.012345678901234567890123456789);

I don't insert things, it's a read only database. The problem is
about getting the data into Access. The insertions just were an
example for testing, which values or types actually make an issue ;)

Could you perhaps add a switchable function like "cast numeric to
float"?

Best regards,
Tobias


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
In reply to this post by Tobias Wendorff
Some additional information about supported functions and mappings:

https://www.devart.com/odbc/postgresql/docs/supported_data_types.htm
https://www.devart.com/odbc/postgresql/docs/supported_odbc_api_functions.htm

Does PGSQL-ODBC map NUMERIC the same way (to SQL_NUMERIC)?


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Jan Wieck-3
Anything that is exact precision in the database should never be handled as "double" in any driver. That is a serious bug and a possible violation of accounting laws in many countries. 

That is assuming "double" in this case means "double precision floating point" as in C's double type.


Best Regards,
Jan


On Sat, May 26, 2018 at 10:58 PM, Tobias Wendorff <[hidden email]> wrote:
Some additional information about supported functions and mappings:

https://www.devart.com/odbc/postgresql/docs/supported_data_types.htm
https://www.devart.com/odbc/postgresql/docs/supported_odbc_api_functions.htm

Does PGSQL-ODBC map NUMERIC the same way (to SQL_NUMERIC)?





--
Jan Wieck
Senior Postgres Architect
Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Am So, 27.05.2018, 05:18 schrieb Jan Wieck:
> Anything that is exact precision in the database should never
> be handled as "double" in any driver. That is a serious bug and
> a possible violation of accounting laws in many countries.

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

Access supports DECIMAL file type with a range from -9.999... x 10^27
to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to
1.797 x 10^308.

Actually, older Access had problems with DECIMAL fields in the past,
maybe those got fixed already?

Best regards,
Tobias


Reply | Threaded
Open this post in threaded view
|

RE: NUMERIC type makes trouble in MS Access

Danny Severns

Don't know if this helps, and I know almost nothing about Access, but I found this in the MS Docs, https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-access-data-types?view=sql-server-2017.

 

The following table shows the Microsoft Access data types (Col 1), data types used to create tables (Col 2), and ODBC SQL data types (Col 3).

NUMBER (FieldSize= SINGLE)

SINGLE

SQL_REAL

NUMBER (FieldSize= DOUBLE)

DOUBLE

SQL_DOUBLE

NUMBER (FieldSize= BYTE)

UNSIGNED BYTE

SQL_TINYINT

NUMBER (FieldSize= INTEGER)

SHORT

SQL_SMALLINT

NUMBER (FieldSize= LONG INTEGER)

LONG

SQL_INTEGER

NUMERIC

NUMERIC

SQL_NUMERIC

 

From this it appears the Access datatype NUMBER would have to have a qualifier when declared and used to CREATE a table, in order to know to which datatype it would need to be converted when pulled from PG and inserted into Access.  But I certainly may not have understood your problem correctly.

 

HTH

Danny

 

-----Original Message-----
From: Tobias Wendorff <[hidden email]>
Sent: Sunday, May 27, 2018 11:36 AM
To: Jan Wieck <[hidden email]>
Cc: Inoue, Hiroshi <[hidden email]>; [hidden email]
Subject: Re: NUMERIC type makes trouble in MS Access

 

Am So, 27.05.2018, 05:18 schrieb Jan Wieck:

> Anything that is exact precision in the database should never be

> handled as "double" in any driver. That is a serious bug and a

> possible violation of accounting laws in many countries.

 

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

 

Access supports DECIMAL file type with a range from -9.999... x 10^27 to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to

1.797 x 10^308.

 

Actually, older Access had problems with DECIMAL fields in the past, maybe those got fixed already?

 

Best regards,

Tobias

 

 

Reply | Threaded
Open this post in threaded view
|

RE: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Am So, 27.05.2018, 21:35 schrieb Danny Severns:
> But I certainly may not have understood your problem correctly.

Access throws an error when trying to read columns with type NUMERIC,
which don't have a precision limit from linked tables.

I can't remember what happens to fields with a precision limit.
I'll try it soon again (lots of work to install / uninstall the drivers).


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Jan Wieck-3
In reply to this post by Tobias Wendorff


On Sun, May 27, 2018 at 12:35 PM, Tobias Wendorff <[hidden email]> wrote:
Am So, 27.05.2018, 05:18 schrieb Jan Wieck:
> Anything that is exact precision in the database should never
> be handled as "double" in any driver. That is a serious bug and
> a possible violation of accounting laws in many countries.

So SQL_NUMERIC is DOUBLE? Sorry, I'm not an insider ;)

Nope, please read that again and then compare that statement to what you wrote about the "commercial" driver (using double) and your suggestion to have a switch to make the PostgreSQL ODBC driver do the same (nonsense).

 

Access supports DECIMAL file type with a range from -9.999... x 10^27
to +9.999... x 10^27 while DOUBLE goes from -1.797 x 10^308 to
1.797 x 10^308.

But what precision does Access claim to support?

All I understood so far is that PostgreSQL's NUMERIC without explicit precision sometimes overwhelms Access with too many digits after the decimal point. If that is accurate you may want to either complain to Microsoft about their insufficient implementation of DECIMAL or fix the schema of the source database.


Best Regards,
Jan

 



Actually, older Access had problems with DECIMAL fields in the past,
maybe those got fixed already?

Best regards,
Tobias




--
Jan Wieck
Senior Postgres Architect
Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Dear Jan,

Am So, 27.05.2018, 23:22 schrieb Jan Wieck:
>
> Nope, please read that again and then compare that statement to
> what you wrote about the "commercial" driver (using double) and
> your suggestion to have a switch to make the PostgreSQL ODBC
> driver do the same (nonsense).

I'm asking you not to be condescending with people who come to
this mailing list with a problem. Not all the members on this
lists senior postgres architects, some are just normal users.

My idea for this "nonsense" switch just was a workaround for bad
designed databases schemas or for compatibility reasons with rarely
used closed source applications, such as Microsoft Access.

> But what precision does Access claim to support?

Access 2010 at least can handle a precision of 28 digits.
Actually, that's what you can set on its GUI.

> All I understood so far is that PostgreSQL's NUMERIC without explicit
> precision sometimes overwhelms Access with too many digits after the
> decimal point. If that is accurate you may want to either complain to
> Microsoft about their insufficient implementation of DECIMAL or fix
> the schema of the source database.

I don't think that the database design is broken, since PostgreSQL
supports NUMERIC columns without a specific precision. So all I can
do is: create a VIEW, which rounds or casts my data on the fly to
a format, Access can access via the ODBC driver.

Best regards,
Tobias


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Craig Ringer-3
On 28 May 2018 at 05:43, Tobias Wendorff <[hidden email]> wrote:

> All I understood so far is that PostgreSQL's NUMERIC without explicit
> precision sometimes overwhelms Access with too many digits after the
> decimal point. If that is accurate you may want to either complain to
> Microsoft about their insufficient implementation of DECIMAL or fix
> the schema of the source database.

I don't think that the database design is broken, since PostgreSQL
supports NUMERIC columns without a specific precision. So all I can
do is: create a VIEW, which rounds or casts my data on the fly to
a format, Access can access via the ODBC driver.


Some quick research suggests that Access's Decimal data type is what you should be using. It's not floating point. This fits with what you're saying, as the docs say Decimal in Access is limited to 28 digits.

Seems like it probably has a more limited implementation using fixed-point, not PostgreSQL's BCD representation, as they use a fixed 17 bytes in the native storage. 

It should map as SQL_DECIMAL, NOT double or float.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Hi Craig,

Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>
> Some quick research suggests that Access's Decimal data type is
> what you should be using. It's not floating point. This fits with
> what you're saying, as the docs say Decimal in Access is limited
> to 28 digits.

But there's a problem: you can't change the field type of a linked
table in MS Access. Even if the driver delivers the correct type,
I cannot influence the decimal width (or anything else) and the
problem inevitably arises.

So, as Jan and I suggested, the only way is to apply a change
either in the column itself or do it in realtime using a VIEW.

> Seems like it probably has a more limited implementation using
> fixed-point, not PostgreSQL's BCD representation, as they use
> a fixed 17 bytes in the native storage.
>
> It should map as SQL_DECIMAL, NOT double or float.

Sure, I didn't want to force fundamental changes here. I just
wanted to suggest something like a "compatibility switch" for
Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.

Best,
Tobias


Reply | Threaded
Open this post in threaded view
|

RE: NUMERIC type makes trouble in MS Access

Tobias Wendorff
In reply to this post by Tobias Wendorff
Here an update:

by default, MS Access sets linked tables with "unlimited" NUMERIC
to DECIMAL with "precision: 28" and "decimals: 6" (MS Access names).
Trying to read a value [INSERT INTO public.demo VALUES (1.0 / 3)]
breaks with an error.

When creating the column using NUMERIC(28, 24), MS Access shows this:
"precision: 28" and "decimals: 24". And there's no error.

Funny, when creating the column using NUMERIC(32, 28), MS Access
interprets it as TEXT with field length 32.

NUMERIC(28,27) is the last column type, MS Access can read as DECIMAL.

A view on an "unlimited" NUMERIC with casting ::NUMERIC(28, 24)
is accepted by Access. So it seems like I need to create a VIEW as a
work-around.


Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Craig Ringer-3
On 28 May 2018 at 11:50, Tobias Wendorff <[hidden email]> wrote:
Here an update:

by default, MS Access sets linked tables with "unlimited" NUMERIC
to DECIMAL with "precision: 28" and "decimals: 6" (MS Access names).
Trying to read a value [INSERT INTO public.demo VALUES (1.0 / 3)]
breaks with an error.

When creating the column using NUMERIC(28, 24), MS Access shows this:
"precision: 28" and "decimals: 24". And there's no error.

Funny, when creating the column using NUMERIC(32, 28), MS Access
interprets it as TEXT with field length 32.

NUMERIC(28,27) is the last column type, MS Access can read as DECIMAL.

A view on an "unlimited" NUMERIC with casting ::NUMERIC(28, 24)
is accepted by Access. So it seems like I need to create a VIEW as a
work-around.

Sounds like this is something the ODBC driver could work around, by exposing the option to clamp the reported NUMERIC scale and precision.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Inoue, Hiroshi-2
In reply to this post by Tobias Wendorff
Hi Tobias,

Could you please try the test drivers 10.03.0100 on Windows at
    https://winpg.jp/~inoue/psqlodbc/index.html
?
The test drivers have an option
    Numeric(without precision) as
.

regards,
Hiroshi Inoue

On 2018/05/28 11:13, Tobias Wendorff wrote:
Hi Craig,

Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
Some quick research suggests that Access's Decimal data type is
what you should be using. It's not floating point. This fits with
what you're saying, as the docs say Decimal in Access is limited
to 28 digits.
But there's a problem: you can't change the field type of a linked
table in MS Access. Even if the driver delivers the correct type,
I cannot influence the decimal width (or anything else) and the
problem inevitably arises.

So, as Jan and I suggested, the only way is to apply a change
either in the column itself or do it in realtime using a VIEW.

Seems like it probably has a more limited implementation using
fixed-point, not PostgreSQL's BCD representation, as they use
a fixed 17 bytes in the native storage.

It should map as SQL_DECIMAL, NOT double or float.
Sure, I didn't want to force fundamental changes here. I just
wanted to suggest something like a "compatibility switch" for
Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.

Best,
Tobias
Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Hi Hiroshi,

works nice, well done!

Could you perhaps extend the field length of varchar to "max varchar"
from "Miscellaneous" on page 1? Right now it's limited to 28.

That would allow exporting the highest possible precision in Access
(f.e. export to CSV).

Thanks again... really good work!
Tobias



Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:

> Hi Tobias,
>
> Could you please try the test drivers 10.03.0100 on Windows at
> https://winpg.jp/~inoue/psqlodbc/index.html
> ?
> The test drivers have an option
>      Numeric(without precision) as
> .
>
> regards,
> Hiroshi Inoue
>
> On 2018/05/28 11:13, Tobias Wendorff wrote:
>> Hi Craig,
>>
>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>> Some quick research suggests that Access's Decimal data type is
>>> what you should be using. It's not floating point. This fits with
>>> what you're saying, as the docs say Decimal in Access is limited
>>> to 28 digits.
>> But there's a problem: you can't change the field type of a linked
>> table in MS Access. Even if the driver delivers the correct type,
>> I cannot influence the decimal width (or anything else) and the
>> problem inevitably arises.
>>
>> So, as Jan and I suggested, the only way is to apply a change
>> either in the column itself or do it in realtime using a VIEW.
>>
>>> Seems like it probably has a more limited implementation using
>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>> a fixed 17 bytes in the native storage.
>>>
>>> It should map as SQL_DECIMAL, NOT double or float.
>> Sure, I didn't want to force fundamental changes here. I just
>> wanted to suggest something like a "compatibility switch" for
>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>
>> Best,
>> Tobias
>



Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Inoue, Hiroshi-2
Hi Tobias,

Could you please try the test drivers 10.03.0110 on Windows at
   https://winpg.jp/~inoue/psqlodbc/index.html
?

regards,
Hiroshi Inoue

On 2018/05/28 16:32, Tobias Wendorff wrote:

> Hi Hiroshi,
>
> works nice, well done!
>
> Could you perhaps extend the field length of varchar to "max varchar"
> from "Miscellaneous" on page 1? Right now it's limited to 28.
>
> That would allow exporting the highest possible precision in Access
> (f.e. export to CSV).
>
> Thanks again... really good work!
> Tobias
>
>
>
> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>> Hi Tobias,
>>
>> Could you please try the test drivers 10.03.0100 on Windows at
>> https://winpg.jp/~inoue/psqlodbc/index.html
>> ?
>> The test drivers have an option
>>       Numeric(without precision) as
>> .
>>
>> regards,
>> Hiroshi Inoue
>>
>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>> Hi Craig,
>>>
>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>> Some quick research suggests that Access's Decimal data type is
>>>> what you should be using. It's not floating point. This fits with
>>>> what you're saying, as the docs say Decimal in Access is limited
>>>> to 28 digits.
>>> But there's a problem: you can't change the field type of a linked
>>> table in MS Access. Even if the driver delivers the correct type,
>>> I cannot influence the decimal width (or anything else) and the
>>> problem inevitably arises.
>>>
>>> So, as Jan and I suggested, the only way is to apply a change
>>> either in the column itself or do it in realtime using a VIEW.
>>>
>>>> Seems like it probably has a more limited implementation using
>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>> a fixed 17 bytes in the native storage.
>>>>
>>>> It should map as SQL_DECIMAL, NOT double or float.
>>> Sure, I didn't want to force fundamental changes here. I just
>>> wanted to suggest something like a "compatibility switch" for
>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>
>>> Best,
>>> Tobias

Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Dear Hiroshi,

in case you missed my E-Mail from 13 hours ago:

works nice, well done!

Could you perhaps extend the field length of varchar to "max varchar"
from "Miscellaneous" on page 1? Right now it's limited to 28.

That would allow exporting the highest possible precision in Access
(f.e. export to CSV).

Thanks again... really good work!
Tobias



Am Mo, 28.05.2018, 14:59 schrieb Inoue, Hiroshi:

> Hi Tobias,
>
> Could you please try the test drivers 10.03.0110 on Windows at
>    https://winpg.jp/~inoue/psqlodbc/index.html
> ?
>
> regards,
> Hiroshi Inoue
>
> On 2018/05/28 16:32, Tobias Wendorff wrote:
>> Hi Hiroshi,
>>
>> works nice, well done!
>>
>> Could you perhaps extend the field length of varchar to "max varchar"
>> from "Miscellaneous" on page 1? Right now it's limited to 28.
>>
>> That would allow exporting the highest possible precision in Access
>> (f.e. export to CSV).
>>
>> Thanks again... really good work!
>> Tobias
>>
>>
>>
>> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>>> Hi Tobias,
>>>
>>> Could you please try the test drivers 10.03.0100 on Windows at
>>> https://winpg.jp/~inoue/psqlodbc/index.html
>>> ?
>>> The test drivers have an option
>>>       Numeric(without precision) as
>>> .
>>>
>>> regards,
>>> Hiroshi Inoue
>>>
>>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>>> Hi Craig,
>>>>
>>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>>> Some quick research suggests that Access's Decimal data type is
>>>>> what you should be using. It's not floating point. This fits with
>>>>> what you're saying, as the docs say Decimal in Access is limited
>>>>> to 28 digits.
>>>> But there's a problem: you can't change the field type of a linked
>>>> table in MS Access. Even if the driver delivers the correct type,
>>>> I cannot influence the decimal width (or anything else) and the
>>>> problem inevitably arises.
>>>>
>>>> So, as Jan and I suggested, the only way is to apply a change
>>>> either in the column itself or do it in realtime using a VIEW.
>>>>
>>>>> Seems like it probably has a more limited implementation using
>>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>>> a fixed 17 bytes in the native storage.
>>>>>
>>>>> It should map as SQL_DECIMAL, NOT double or float.
>>>> Sure, I didn't want to force fundamental changes here. I just
>>>> wanted to suggest something like a "compatibility switch" for
>>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>>
>>>> Best,
>>>> Tobias
>



Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Inoue, Hiroshi-2
Hi Tobias,

On 2018/05/29 6:19, Tobias Wendorff wrote:
> Dear Hiroshi,
>
> in case you missed my E-Mail from 13 hours ago:

No.
The modified drivers should extend the field length to "max varchar" size.
Please test the driver and tell me the result.

regards,
Hiroshi Inoue

>
> works nice, well done!
>
> Could you perhaps extend the field length of varchar to "max varchar"
> from "Miscellaneous" on page 1? Right now it's limited to 28.
>
> That would allow exporting the highest possible precision in Access
> (f.e. export to CSV).
>
> Thanks again... really good work!
> Tobias
>
>
>
> Am Mo, 28.05.2018, 14:59 schrieb Inoue, Hiroshi:
>> Hi Tobias,
>>
>> Could you please try the test drivers 10.03.0110 on Windows at
>>     https://winpg.jp/~inoue/psqlodbc/index.html
>> ?
>>
>> regards,
>> Hiroshi Inoue
>>
>> On 2018/05/28 16:32, Tobias Wendorff wrote:
>>> Hi Hiroshi,
>>>
>>> works nice, well done!
>>>
>>> Could you perhaps extend the field length of varchar to "max varchar"
>>> from "Miscellaneous" on page 1? Right now it's limited to 28.
>>>
>>> That would allow exporting the highest possible precision in Access
>>> (f.e. export to CSV).
>>>
>>> Thanks again... really good work!
>>> Tobias
>>>
>>>
>>>
>>> Am Mo, 28.05.2018, 09:17 schrieb Inoue, Hiroshi:
>>>> Hi Tobias,
>>>>
>>>> Could you please try the test drivers 10.03.0100 on Windows at
>>>> https://winpg.jp/~inoue/psqlodbc/index.html
>>>> ?
>>>> The test drivers have an option
>>>>        Numeric(without precision) as
>>>> .
>>>>
>>>> regards,
>>>> Hiroshi Inoue
>>>>
>>>> On 2018/05/28 11:13, Tobias Wendorff wrote:
>>>>> Hi Craig,
>>>>>
>>>>> Am Mo, 28.05.2018, 03:34 schrieb Craig Ringer:
>>>>>> Some quick research suggests that Access's Decimal data type is
>>>>>> what you should be using. It's not floating point. This fits with
>>>>>> what you're saying, as the docs say Decimal in Access is limited
>>>>>> to 28 digits.
>>>>> But there's a problem: you can't change the field type of a linked
>>>>> table in MS Access. Even if the driver delivers the correct type,
>>>>> I cannot influence the decimal width (or anything else) and the
>>>>> problem inevitably arises.
>>>>>
>>>>> So, as Jan and I suggested, the only way is to apply a change
>>>>> either in the column itself or do it in realtime using a VIEW.
>>>>>
>>>>>> Seems like it probably has a more limited implementation using
>>>>>> fixed-point, not PostgreSQL's BCD representation, as they use
>>>>>> a fixed 17 bytes in the native storage.
>>>>>>
>>>>>> It should map as SQL_DECIMAL, NOT double or float.
>>>>> Sure, I didn't want to force fundamental changes here. I just
>>>>> wanted to suggest something like a "compatibility switch" for
>>>>> Microsoft Access when using "unlimited" NUMERIC in PostgreSQL.
>>>>>
>>>>> Best,
>>>>> Tobias

Reply | Threaded
Open this post in threaded view
|

Re: NUMERIC type makes trouble in MS Access

Tobias Wendorff
Hi Hiroshi,

Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi:
> The modified drivers should extend the field length to
> "max varchar" size. Please test the driver and tell me the result.

Oops, I didn't see a change in version number and thought it was the
same build.

Sorry, it does NOT work. I've set "max varchar" to 250, but the column
in Access still has a width of 28. I've uninstalled and re-installed
the driver and I've set up a fresh DSN for testing.

Also, there's another bug: When relinking the table from Access'
tablemanager, the column type suddenly gets changed to double
(without having anything changed in the ODBC driver). It's exactly
the same table. There's no way to revert this :(

Best regards,
Tobias


12