2

I am using PostgreSQL backend with linked tables in Access. On using the wizard to link to the linked tables, I get errors:

Scaling of decimal value resulted in data truncation

This appears to be the wrong scale for numeric data types being chosen as the default by Access: the Postgresql data type being linked is Numeric with no precision or scale defined, and is being linked as Decimal with precision 28 and scale 6 as default.

How can I get Access to link it as Double?

I see here MS Access linked tables automatically long integers that the self-answer was:

Figured it out (and I feel dumb): When linking tables you can choose the desired format for each field when going through the linked table wizard steps.

But, I see no option in Access to choose the desired format during linking.

Community
  • 1
  • 1
user3851177
  • 45
  • 1
  • 9
  • Are you talking about **linking** or **importing** tables? These are two rather different operations. – Andre Dec 05 '16 at 16:39
  • I meant linking: I've edited the question. – user3851177 Dec 05 '16 at 16:43
  • Ah, ok. All I can say is that there is indeed no option to select data types when linking tables. The other answer must have talked about importing. Basically Access tries to find the best match to the datatype the ODBC driver reports. – Andre Dec 05 '16 at 17:32
  • 1
    Check this out: https://www.postgresql.org/message-id/AANLkTimZNd6WSqXk9q_lOGOcbQUMz04u6Z_pFSKG8KQz%40mail.gmail.com – Andre Dec 05 '16 at 17:35
  • Yes, I had read that, but it does not appear to give a solution as to how to remedy. – user3851177 Dec 05 '16 at 17:54
  • Well it does suggest to do an ALTER TABLE to set the `numeric` column to a specified precision (e.g. `numeric(28,14)`) - have you tried that? – Andre Dec 05 '16 at 18:17
  • I have multiple columns in multiple tables, with multiple variations in precision and scale that would all need to change, so I would like a solution that does not involve changing the PostgreSQL db's data type. I just need Access to not use Decimal, and use Double instead. – user3851177 Dec 05 '16 at 18:26

1 Answers1

2

If there is anything like a "default" data type when creating an ODBC linked table in Access, that type would be Text(255). That is, if the ODBC driver reports a column with a data type that Access does not support (e.g. TIME in SQL Server) then Access will include it as a Text(255) column in the linked table.

In this case, for a PostgreSQL table

CREATE TABLE public.numeric_test_table
(
  id integer NOT NULL,
  text_col character varying(50),
  numeric_col numeric,
  CONSTRAINT numeric_test_table_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

the PostgreSQL ODBC driver is actually reporting the numeric column as being numeric(28,6) as confirmed by calling OdbcConnection#GetSchema("columns") from C#

GetSchema.png

so that is what Access uses as the column type for its linked table. It is only when Access goes to retrieve the actual data that the PostgreSQL ODBC driver sends back values that won't "fit" in the corresponding column of the linked table.

So no, there is almost certainly no overall option to tell Access to treat all numeric (i.e., Decimal) columns as Double. The "best" solution would be to alter the PostgreSQL table definitions to explicitly state the precision and scale, as suggested in the PostgreSQL documentation:

If you're concerned about portability, always specify the precision and scale [of a numeric column] explicitly.

If modifying the PostgreSQL database is not feasible then another option would be to use a pass-through query in Access to explicitly convert the column to Double ...

SELECT id, text_col, numeric_col::double precision FROM public.numeric_test_table

... bearing in mind that pass-through queries always return read-only recordsets.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I've done a test on one column using ALTER TABLE t1 ALTER COLUMN l TYPE numeric(20, 15) USING (l::numeric(20, 15));. According to [https://www.postgresql.org/docs/9.1/static/datatype-numeric.html] "Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations." - but for my altered values there are trailing zeros (as viewed in PG Admin). Any reason for this? – user3851177 Dec 07 '16 at 10:46
  • Trailing zeros have been discussed before, ref: [here](http://stackoverflow.com/q/26920157/2144390) (and also [here](http://postgresql.nabble.com/cast-numeric-with-scale-and-precision-to-numeric-plain-td1919797.html)). It seems to me that there may be some confusion regarding how values are *"physically stored"* vs. how they are *displayed*, i.e., PostgreSQL may *store* a value without trailing zeros but default to displaying them with trailing zeros (according to the defined scale) for consistency between various rows. – Gord Thompson Dec 07 '16 at 12:34
  • "confusion regarding how values are "physically stored" vs. how they are displayed" - thanks for clearing that up – user3851177 Dec 07 '16 at 17:57