Thread: NUMERIC type makes trouble in MS Access
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);
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);
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
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
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)?
Senior Postgres Architect
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
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 <tobias.wendorff@tu-dortmund.de>
Sent: Sunday, May 27, 2018 11:36 AM
To: Jan Wieck <jan@wi3ck.info>
Cc: Inoue, Hiroshi <h-inoue@dream.email.ne.jp>; pgsql-odbc@lists.postgresql.org
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
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).
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
Senior Postgres Architect
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
> 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.
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
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.
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.
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
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
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 >
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
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 >
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
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
Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi: >>> Could you please try the test drivers 10.03.0110 on Windows at >>> https://winpg.jp/~inoue/psqlodbc/index.html I think, I might have found the problem: The driver version after installing the latest version is 10.03.01.00, which could be 10.03.0100 only... I've downloaded again, manually uninstalled and reinstalled it. Still 10.03.01.00 without new varchar setting :(
Interesting detail: It's not an Access problem. I've tested it with Python and Libre Office Base. NUMERIC without precision gets set to 28,6 => like in Access. Would it perhaps better to use MEMO instead of VARCHAR? MEMO allows more than 8,000 chars in "Base" and some gigabytes in Access. But there's not enough space for an additional toggle switch :(
Oops sorry, Please download it again and retry. regards, Hiroshi Inoue On 2018/05/29 9:43, Tobias Wendorff wrote: > Am Di, 29.05.2018, 00:39 schrieb Inoue, Hiroshi: >>>> Could you please try the test drivers 10.03.0110 on Windows at >>>> https://winpg.jp/~inoue/psqlodbc/index.html > I think, I might have found the problem: > The driver version after installing the latest version is 10.03.01.00, > which could be 10.03.0100 only... > > I've downloaded again, manually uninstalled and reinstalled it. > Still 10.03.01.00 without new varchar setting :(
Am Di, 29.05.2018, 03:39 schrieb Inoue, Hiroshi: > Oops sorry, > > Please download it again and retry. It works. There are some little layout issues with the label. They're intersecting with the radiobuttons. What do you think about my MEMO approach?
Am Di, 29.05.2018, 03:39 schrieb Inoue, Hiroshi:Oops sorry, Please download it again and retry.It works. There are some little layout issues with the label. They're intersecting with the radiobuttons. What do you think about my MEMO approach?
Could you please try the test drivers 10.03.0120 on Windows at
https://winpg.jp/~inoue/psqlodbc/index.html
?
I added *memo* and removed *numeric* because default means
*numeric*.
regards,
Hiroshi Inoue
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
Senior Postgres Architect
Hi Hiroshi, Am Di, 29.05.2018, 14:20 schrieb Inoue, Hiroshi: > > I added *memo* and removed *numeric* because default means > *numeric*. Thanks it works nice. What do you think about extending this feature to always apply it to NUMERIC - even when it has a scale and precision? When having a column with NUMERIC(60,40), it doesn't fit into DECIMAL, but it fits into MEMO and VARCHAR. Using the current switch, the user could either keep it as default or cast it to anything else. That feature would be VERY complete then. Best regards, Tobias
Hi there, your website seems to have some cipher hick-ups. Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi: > > Could you please try the test drivers 10.03.0120 on Windows at > https://winpg.jp/~inoue/psqlodbc/index.html Do you have a mirror of the test driver or even a never version? Best regards, Tobias
Hi Tobias, Thanks for the report. I would contact the site manager. On 2018/11/02 23:01, Tobias Wendorff wrote: > Hi there, > > your website seems to have some cipher hick-ups. > > Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi: >> Could you please try the test drivers 10.03.0120 on Windows at >> https://winpg.jp/~inoue/psqlodbc/index.html > Do you have a mirror of the test driver or even a never version? We are planning to make a new release in a few weeks. We have some troubles with missing 32-bit libpq. regards, Hiroshi Inoue > Best regards, > Tobias --- このメールは、AVG によってウイルス チェックされています。 http://www.avg.com
Hi Tobias, Connection to the website has been restored. Psqlodbc version of the site is 10.03.0200. regards, Hiroshi Inoue On 2018/11/03 9:30, Inoue, Hiroshi wrote: > Hi Tobias, > > Thanks for the report. > I would contact the site manager. > > On 2018/11/02 23:01, Tobias Wendorff wrote: >> Hi there, >> >> your website seems to have some cipher hick-ups. >> >> Am Di, 29.05.2018, 13:20 schrieb Inoue, Hiroshi: >>> Could you please try the test drivers 10.03.0120 on Windows at >>> https://winpg.jp/~inoue/psqlodbc/index.html >> Do you have a mirror of the test driver or even a never version? > > We are planning to make a new release in a few weeks. > We have some troubles with missing 32-bit libpq. > > regards, > Hiroshi Inoue > >> Best regards, >> Tobias --- このメールは、AVG によってウイルス チェックされています。 http://www.avg.com