Problem using Oracle NUMBER datatype in ADO
Using NUMBER would force oracle to preserve a large allocation and ADO's
limitations would come
to the fore.
Just specify the exact precision for the NUMBER column in the table like
NUMBER(2) or NUMBER(10,2) instead of just NUMBER.
That will take care of your problem.
Quote:
>Actually the problem is not in ADO. The Oracle NUMBER type is just too
large
>to convert it to any known type. Variant/Decimal is ADO's best fit.
>Try this:
> ' Assign the field's value:
> vntValue = Recordset(lngIndex - 1).Value
> ' Excel cannot handle Decimal field types, so
> ' this needs to be converted to a double.
> If VarType(vntValue) = vbDecimal Then
> vntValue = CDbl(vntValue)
> End If
>Note that you should expect a runtime error if the Oracle table contains
>very large values.
>Regards,
>Arvid
>>Hello,
>>I am using ADO 2.0 in Excel VBA, accessing data from an Oracle database.
>The
>>problem is that ADO does not convert the Oracle NUMBER datatype correctly.
>In
>>the Locals Window in VBA I see that ADO converts the NUMBER datatype
>>incorrectly to "Variant/Decimal" whereas the FLOAT datatype is converted
>>correctly to "Variant/Double".
>>The result is that it is impossible to work with the Field containing the
>>NUMBER datatype. The only workaround I have found is to use the FLOAT
>>datatype in all tables which I use in the database instead of the NUMBER
>>datatype. However, when you begin to work with legendary tables which use
>the
>>NUMBER datatype you are in trouble...
>>Does anyone know how to circumvent this problem?
>>Henrik
