ACC: Conversion of ORACLE Data Types to Microsoft Access

This article was previously published under Q104977
This article has been archived. It is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

Column, or field, data types are different for each database managementsystem. This means that when you are accessing table structures from otherdatabase management systems, a conversion, or data type mapping, mustoccur. This article describes the data type mapping between ORACLE andMicrosoft Access data types.
The following table demonstrates data type mapping between ORACLE andMicrosoft 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 totalnumber of digits) and scale (the number of digits to the right of thedecimal point) for columns with Number data types. If a Number data type isspecified without a precision or scale, the column defaults to a data typeof Number(9,2). The scale limit is 38, and a scale without a precision isallowed. 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 notmap to any data type in Microsoft Access, it will be mapped to a text datatype. For example, an ORACLE Smallint data type, which is actually aNumber(38,0) data type, will map into Microsoft Access as a text data typebecause Microsoft Access does not have a number value of 38 precision.

Article ID: 104977 - Last Review: 12/04/2015 09:45:19 - Revision: 2.1

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbinfo kbusage KB104977