CODEWITHSUNDEEP
postgresqlms-accessodbclinked-tables
user3851177
user3851177

Reputation: 45

How to change data type default from Decimal to Double when linking external tables in Access?

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.

Upvotes: 2

Views: 1426

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123799

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.

Upvotes: 2

Related Questions