Article ID: 104977 - Last Review: January 18, 2007 - Revision: 2.1

ACC: Conversion of ORACLE Data Types to Microsoft Access

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q104977
Novice: Requires knowledge of the user interface on single-user computers.

Expand all | Collapse all

SUMMARY

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.
APPLIES TO
  • 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
Back to the top
Keywords: 
kbinfo kbusage KB104977
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top