Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
PRB: Numeric Field Displays #Deleted with Oracle Linked Tables
Article ID: 251136 - View products that this article applies to.
This article was previously published under Q251136
#Deleted entries are seen when Oracle linked tables containing number datatypes are opened using either Access 97 SR2 or Access 2000. This behavior is seen with both Oracle 7.3.4 server as well as Oracle 8.0.5 server irrespective of the version of the MSORCL32.DLL file used to create the DSN.
In Oracle, the NUMBER datatype by default has a precision of 38; that is it, is equivalent to NUMBER(38). This precision is much greater than what the Microsoft Jet database engine 3x/4x can handle. Thus, in this scenario Jet has to perform lookups using data that it cannot handle. The error is seen because Jet cannot succeed in the lookup and it therefore assumes that the data it's trying to find has been deleted.
The only workaround for this problem is to create the table using numbers of lower precision than the Oracle default precision of 38. With Jet 4.x, a precision of 28 is allowed.
This behavior is by design. Microsoft has confirmed this to a design limitation of the Jet database engine.
This problem is seen on account of the inability of Jet to handle the large precision of Oracle's NUMBER datatype. This is not a driver issue because no problem is encountered when using pass-through queries or any other non-Jet based ODBC application.
Steps to Reproduce BehaviorNOTE: Use SQL *PLUS to run the following SQL Statements.
The table definition has to be changed to get around this limitation of Jet 3x/4x with numbers of large precisions.