ACC: Conversion of ORACLE Data Types to Microsoft Access

ID: Q104977

The information in this article applies to:

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

Column, or field, data types are different for each database management system. This means that when you are accessing table structures from other database management systems, a conversion, or data type mapping, must occur. This article describes the data type mapping between ORACLE and Microsoft Access data types.


MORE INFORMATION

The following table demonstrates data type mapping between ORACLE and Microsoft Access data types: 


 ORACLE data type      Maps to Microsoft Access data type
 --------------------------------------------------------
 Char(n)               Text(n)
 VarChar(n)            Text(n)
 Long                  Memo
 Date                  Date/Time
 Long Raw              Ole Object
 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 Access data type
 ---------------------------------------------------
 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.

Additional query words: convert 


Keywords          : kbusage TblDatyp 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo

Last Reviewed: March 27, 1999