Re: Access - ODBC - Oracle Integrity?

From: Jim Kennedy <odysscci_at_teleport.com>
Date: Sun, 19 Mar 1995 10:36:13
Message-ID: <odysscci.239.000A9ADB_at_teleport.com>


In article <3kgbn9$d6p_at_warp.cris.com> schmegma_at_cris.com (PS) writes:
>Path: news.teleport.com!psgrain!ee.und.ac.za!quagga.ru.ac.za!howland.reston.ans.net!news.sprintlink.net!warp.cris.com!localhost
>From: schmegma_at_cris.com (PS)
>Newsgroups: comp.databases.ms-access,comp.databases.oracle
>Subject: Re: Access - ODBC - Oracle Integrity?
>Date: Sun, 19 Mar 95 04:22:49 GMT
>Organization: Concentric Research Corporation
>Lines: 30
>Distribution: world
>Message-ID: <3kgbn9$d6p_at_warp.cris.com>
>References: <3kc3af$kub_at_eldborg.rhi.hi.is>
>NNTP-Posting-Host: crc1.cris.com
>X-Newsreader: News Xpress Version 1.0 Beta #2.1
>Xref: news.teleport.com comp.databases.ms-access:23205 comp.databases.oracle:29568

>tomase_at_rhi.hi.is (Tomas Erlingsson) wrote:
>>We have Oracle databases running on a unix machine and we are using
>>MS-Access as a front end to view, modify and add data via ODBC and
>>Oracle's SQL*Net.
>>Only recently we discovered a bad error in our system.
>>In cases where we have a NUMBER field in an Oracle table and we are putting
>>data in that field through Access. When we put the number 44 in the field
>>the number 46 is stored instead. This happens every time when we put the
>>number 44, except when we use a SQL pass through query. We have not
>>observed/don't know of any other number that behave like that. We tried
>>doing it with update query, recordset in access basic, by exporting data and
>>use datasheet. We also tried Oracle version 6 and 7 and two types of ODBC
>>drivers but same happened. Then we tried to use Visual Basic via ODBC and
>>that worked until we installed Microsoft Jet Database Engine version 2.0
>>/Visual Basic version 3.0 Compatibility Layer. So it looks like the error
>>is in the Jet Layer 2.0.
 

>This has to be one of the strangest things I have ever heard. Just the number 44?
 

>How about some specifics - what datatype is the field defined as in Oracle? I know that Oracle has field types that offer higher specificty than is available in Access (tinyint, for example, has no equal in Access).
 

>And does this happen in a brand new test Oracle DB?
 

>I hate to say it, but I am pretty sure that MS and Oracle would have posted an immedeate fix if it could not accurately store a value of 44. The compatibilty layer has been out for quite some time, so I don't think it could possibly cause the problem yo
> are experiencing.
 

>I would focus in on the data types of the fields, and do some testing in a new DB with different datatypes, and determine which one is causing the 44=46 problem.
>Then call your Solution provider and find out why.
 

>Pete

I worked on a pension app. that used Access as the front end and a variety of backends (including Access mdb's). There is a bug in Access somewhere that does some funny things to numbers. It dod not matter if the machine was a Pentium or a 486. It also did not matter what backend. The case was government CPI numbers (1.05, 1.06...1.035) to tenths of a percent. We kept getting 1.047645 and that type of nonsence. It would get stored in the backend that way also. We even hand typed the numbers in and had the same problem. We did some vodoo and it went away for a while. I do not know if it ever returned.

Given the tests that the original poster did, they make sence, and my experience it appears that there is a bug in the Jet. In my experience MS knows about quite a few bugs and allows subsequent product releases to retain them. Especially, if the bugs are not a visiable part of the GUI. (Word 1 had memory leaks, Word 2 has the same memory leaks, and Word 6 still has them.)

Jim Kennedy Received on Sun Mar 19 1995 - 10:36:13 CET

Original text of this message