Re: Wrong scale for numeric data types in MS Access and ADODB

Oh, I did find a simple workaround by modifying my database:

ALTER TABLE chem ALTER atomic_weight TYPE numeric(28,14);

I guess my case for a bug is if the numeric data type has an
unspecified scale, then it defaults to 6 for some unknown reason.

-Mike

On 15 June 2010 19:37, Mike Toews <mwtoews(at)gmail(dot)com> wrote:
> Hi,
>
> I'm mostly convinced this is a bug, but I just need to verify (and
> maybe find a workaround??). I am trying to link a postgres table with
> a numeric column into Microsoft Access 2003 using the PostgreSQL
> Unicode ODBC driver, version 8.04.02.00 (12/27/2009). Here is the
> table and data in PostgreSQL 8.4.
>
> CREATE TABLE chem
> (
>  id serial NOT NULL,
>  "name" text NOT NULL,
>  atomic_weight numeric,
>  CONSTRAINT chem_pkey PRIMARY KEY (id),
>  CONSTRAINT chem_name_key UNIQUE (name)
> );
>
> INSERT INTO chem("name", atomic_weight) VALUES
> ('Arsenic',74.92159),
> ('Sodium', 22.98976928),
> ('Tritium',3.016049);
>
>
> The data looks normal in pgAdmin, so there is no problem on the server end.
>
> On my Microsoft Windows test laptop, I've set up a System DSN using
> the PostgreSQL Unicode ODBC driver to the database using all defaults,
> setting only the required fields. In MS Access 2003, I can link
> public.chem as public_chem, but viewing the linked table shows me
> this:
>
> id      name    atomic_weight
> 1       Arsenic 74.92159
> #Error  #Error  #Error
> 3       Tritium 3.016049
>
> There are three rows, but the second shows #Error across all lines,
> and if I click on that row I see an error dialog "Scaline of decimal
> value resulted in data truncation" eight times. If I insert another
> row (using psql):
> INSERT INTO chem("name", atomic_weight) VALUES ('Cesium',132.9054519);
>
> this also has an error, since there are 7 decimal places used.
> However, I can insert more lines with 6 or fewer decimal places
> without error.
>
> Viewing the MS Access linked table in [read-only] "Design mode" of
> reveals that the numeric field "atomic_weight" has a precision of 28
> and a scale of 6. This is not correct (or modifiable), since there are
> more than 6 decimal places used in the database for my example data.
> This is why the rows with Sodium and Cesium have errors, while Arsenic
> and Tritium are good, since there are 6 or less decimal places used.
>
> Investigating further using VBA/ADODB shows a similar underling scale issue:
>
> Sub test()
>    Dim conn As New ADODB.Connection
>    Dim cmd As New ADODB.Command
>    Dim rs As ADODB.Recordset
>
>    conn.ConnectionString = "Driver={PostgreSQL
> Unicode};Server=192.168.123.1;Port=5432;Database=mydb;Uid=myuser;Pwd=mypass;"
>    conn.Open
>
>    Set cmd.ActiveConnection = conn
>    cmd.CommandText = "SELECT * FROM chem WHERE name='Sodium';"
>
>    Set rs = cmd.Execute()
>    Set cmd = Nothing
>
>    Debug.Print rs("name"), rs("atomic_weight"),
> rs("atomic_weight").NumericScale
>
>    rs.Close
>    Set rs = Nothing
>
>    conn.Close
>    Set conn = Nothing
> End Sub
>
> The console debug output shows:
> Sodium         22.98976928   6
>
> meaning that the NumericScale of the field is also set at 6. Again,
> this scale of 6 is incorrect, but the output is correct and no errors
> are thrown by this sub.
>
> How can I modify the scale for this column? Is this a bug, and with
> what component? Are there any known workarounds besides crippling my
> data to a smaller scale?
>
> Thanks for taking a look!
>
> -Mike
>