DevHeads.net

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);