Re: Access - ODBC - Oracle Integrity?

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.

Pete Received on Sun Mar 19 1995 - 05:22:49 CET