While Microsoft Jet does its best to hide differences between different ODBC data sources, there is inevitably specific information about different backends that is useful. This section describes some of the common caveats that you may encounter when connecting to an Oracle database.
The first source of information like that given below is the vendor that supplied you with the ODBC driver. However, for common data sources like Oracle, the Knowledge Base articles available for Microsoft Access and Microsoft Basic often describe common scenarios. These articles can be found on the Microsoft Developer Network (MSDN) CDs, or at the usual Microsoft online locations.
Oracle tools reference all database objects (such as table names) in uppercase letters. If you export a Microsoft Access table whose name contains mixed uppercase and lowercase letters, ORACLE tools such as SQL*Net and SQL*Plus will not recognize the table. You can work around this in the Oracle tools by quoting all table names – or you can rename all the tables in Access before exporting them to Oracle. See the Access Knowledge Base article Q119135 for more details.
Microsoft Jet will try to map to the closest data type available on an ODBC server without sacrificing precision. In particular, this means that numeric data may be converted to text to avoid losing digits. This has some interesting effects for servers that may use high precision numbers by default. For example, the Oracle data type "smallint" is precise to 38 digits. As Jet only allows 15 digits of precision this smallint will get mapped to text – probably not what you were expecting! The cure is to examine your server data declarations with an eye to finding a compromise between your server's efficient manipulation of built-in types and Jet's handling of those same types.
The following table demonstrates data type mapping between Oracle and Microsoft Jet data types:
Oracle data type |
Maps to Microsoft Jet data type |
Char(n) |
Text(n) |
VarChar(n) |
Text(n) |
Long |
Memo |
Date |
Date/Time |
Long Raw |
Long Binary |
Float |
Double |
RowID |
Text(18) |
Smallint |
Text(255) |
Note that the Smallint data type is the same as an Oracle data type of Number(38,0).
In Oracle, the creator of the table can define the precision (the total number of digits) and scale (the number of digits to the right of the decimal point) for columns with Number data types. If a Number data type is specified without a precision or scale, the column defaults to a data type of Number(9,2). The scale limit is 38, and a scale without a precision is allowed. Values with different precisions and scales are mapped as below:
Oracle number |
Microsoft Jet |
Scale = 0 and |
|
precision <= 4 |
Integer |
precision <= 9 |
Long Integer |
precision <= 15 |
Double |
Scale > 0 and <= 4 |
|
precision <= 15 |
Double |
Scale > 4 and/or |
|
precision > 15 |
Text |
If an Oracle data type is defined with a precision and scale that does not map to any data type in Microsoft Access, it will be mapped to a text data type. For example, an Oracle Smallint data type, which is actually a Number(38,0) data type, will map into Microsoft Access as a text data type because Microsoft Access does not have a number value of 38 precision.
See the Access Knowledge Base article Q104977 for more details.