Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/251136

From BetaArchive Wiki

Article ID: 251136

Article Last Modified on 2/12/2007



APPLIES TO

  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q251136

SYMPTOMS

  1. 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.

CAUSE

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.

RESOLUTION

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.

STATUS

This behavior is by design. Microsoft has confirmed this to a design limitation of the Jet database engine.

MORE INFORMATION

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 Behavior

NOTE: Use SQL *PLUS to run the following SQL Statements.

  • Case 1: Problematic table creation

    Create table MyNumTest1 (col1 number not null primary key, col2 varchar(10));
    
    insert into MyNumTest1 values (1.234, 'MyTest');
    insert into MyNumTest1 values (10.234, 'MyTest');
    
                            
  • Case 2: Using workaround syntax

    Create table MyNumTest2 (col1 number(16,3) not null primary key, col2 varchar(10));
    
    insert into MyNumTest1 values (1.234, 'MyTest');
    insert into MyNumTest1 values (10.234, 'MyTest');
    
                            

Once this is done, create an ODBC DSN (preferably a SYSTEM DSN) using the Microsoft ODBC for Oracle (version 2.573.4202) driver to connect to the Oracle server on which the above mentioned tables were created. Link these tables from Access 97 SR2 and Access 2000 using this DSN. #Delete entries are seen when opening the table MyNumTest1. No such problems are encountered with the other table.

The table definition has to be changed to get around this limitation of Jet 3x/4x with numbers of large precisions.

Keywords: kbdatabase kbjet kbnofix kboracle kbprb KB251136