Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 251136 - Last Review: February 12, 2007 - Revision: 2.2
PRB: Numeric Field Displays #Deleted with Oracle Linked Tables
This article was previously published under Q251136
On This Page
SYMPTOMS
#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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
- Microsoft Access 97 Standard Edition
| kbdatabase kbjet kbnofix kboracle kbprb KB251136 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please