Re: Access - ODBC - Oracle Integrity?

In article <odysscci.239.000A9ADB_at_teleport.com> odysscci_at_teleport.com (Jim Kennedy) writes:
>From: odysscci_at_teleport.com (Jim Kennedy)
>Subject: Re: Access - ODBC - Oracle Integrity?
>Date: Sun, 19 Mar 1995 10:36:13
 

>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?

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

This has to be a bug in your ODBC driver. I'd talk to Oracle myself. I've implemented an ODBC connection to Rdb (DEC's RDBMS) and ran into similar problems too. However, they never were Access's problems! One had to do with using real values in Rdb instead of integers or scaled integers. Another had to do with a problem in the ODBC driver and was fixed with an upgrade. In fact, with the old ODBC driver, we could not enter numbers between 8.2 to 8.4 and 9.2 to 9.4. Never pass real values back and forth. That's like passing binary information from one machine to another. You always use ASCII instead.  In this case, use scaled integers (they convert to double in Access),

Hope this helps. Received on Mon Mar 20 1995 - 07:55:05 CET