Re: Access - ODBC - Oracle Integrity?

From: Tomas Erlingsson <tomase_at_rhi.hi.is>
Date: 21 Mar 1995 18:04:14 GMT
Message-ID: <3kn4eu$p1m_at_eldborg.rhi.hi.is>


In <3kgbn9$d6p_at_warp.cris.com> schmegma_at_cris.com (PS) writes:

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

I agree it is stange. The number 44 is the only number I know that behave like that. I have't jet wrote a program to systematic try all number. You ask about datatype in Oracle, like I said erlier the datatype is NUMBER definded in this case as NUMBER(3).

It happen both with a older table and a brand new one. It also happen when I export table from Access to Oracle and then Access create new table in Oracle.

Yes you are right, MS has posted a fix, they call it Microsoft Access Version 2.0 Service Pack and it includes a new Jet Layer, Jet Layer 2.5 where this error doesn't ocur. It doesn't ocur either when you use Access 1.0 or when you use SQL_PASSTHOUGH queries in Access 2.0 that as we know skip the Jet Layer.

My theory is like before that Jet Layer 2.0 is full of ODBC errors and this one of them.

We have tried many times to talk to our "Solution provider", the company we buy MS-Access from, but they just point out that we can have a new Jet Layer, noting about the errors in the database cause of this.

Tomas Erlingsson			|
National Physical Planning Agency     	| emil:
Reykjavik				|   tomase_at_rhi.hi.is
Iceland					|   tomas_at_islag.is
Received on Tue Mar 21 1995 - 19:04:14 CET

Original text of this message