ODBC to Jet Data Type Mapping

Microsoft Jet calls the ODBC SQLColumns function to gather information on the fields in the remote table. The SQLColumns function returns (among other things) the following information for each field in the table, which determines how the data type of each field will be mapped to a Microsoft Jet data type.

Argument Description
fSqlType The fSqlType argument returns the SQL data type of the field. Valid SQL data types include a minimum set, which provides basic ODBC conformance, a core set, which is a superset of the minimum set and adds X/Open and SAG CAE (92)-compliant types, and an extended set, which includes the minimum set, the extended set, and additional data types that support specific database servers.
LPrecision The lPrecision argument returns the maximum number of digits used by the data type represented in the field.
WScale The wScale argument returns the maximum number of digits to the right of the decimal point used by the data type represented in the field.

Microsoft Jet uses the fSqlType, lPrecision, and wScale values to determine an appropriate local data type, on a field-by-field basis, and uses that data type to represent the remote data in a linked table. Each time Microsoft Jet executes an action or parameter query against the remote data source, the information stored in the linked table is used to ensure that ODBC calls are made with a valid ODBC data type. The following table lists ODBC data types and the corresponding Microsoft Jet data types.

ODBC data type Microsoft Jet data type
SQL_BIT Yes/No
SQL_TINYINT
SQL_SMALLINT		 Number (Integer)
SQL_INTEGER Number (Long Integer)
SQL_REAL Number (Single)
SQL_FLOAT
SQL_DOUBLE		 Number (Double)
SQL_TIMESTAMP
SQL_DATE		 DateTime
SQL_TIME Text
SQL_CHAR
SQL_VARCHAR		 Varies based on the lPrecision value:
  • For data types where the lPrecision is less than or equal to 255, the Microsoft Jet data type is Text and the FieldSize is equal to the value of lPrecision.

  • For data types where the lPrecision is greater than 255, the Microsoft Jet data type is Memo.
SQL_BINARY
SQL_VARBINARY		 Varies based on the lPrecision value:
  • For data types where the lPrecision is less than or equal to 255, the Microsoft Jet data type is Binary and the FieldSize is equal to the value of lPrecision.

  • For data types where the lPrecision is greater than 255, the Microsoft Jet data type is Long Binary (OLE Object).
SQL_LONGVARBINARY Long Binary (OLE Object)
SQL_LONGVARCHAR Memo

ODBC data type Microsoft Jet data type
SQL_DECIMAL
SQL_NUMERIC		 Varies based on a combination of the lPrecision and wScale values:
  • For data types where the wScale equals 0 and lPrecision is less than or equal to 4, the Microsoft Jet data type is Number (Integer).

  • For data types where the wScale equals 0 and lPrecision is less than or equal to 9, the Microsoft Jet data type is Number (Long Integer).

  • For data types where the wScale is greater than or equal to 0, and lPrecision is less than or equal to 15, the Microsoft Jet data type is Number (Double).

  • For data types where the wScale is greater than 0 and less than or equal to 4, and lPrecision is less than or equal to 15, the Microsoft Jet data type is Number (Double); and the FieldSize is equal to the value of lPrecision; if wScale is greater than 4, the Microsoft Jet data type is Double.

Special Mappings for Microsoft SQL Server

  • For data types where the wScale equals 4 and lPrecision equals 19, the Microsoft Jet data type is Currency.

  • For data types where the wScale equals 4 and lPrecision equals 10, the Microsoft Jet data type is Currency.

See Also For a complete description of data types recognized by ODBC, as well as a complete explanation of precision and scale in ODBC, see the Microsoft ODBC 3.0 Software Development Kit Programmer’s Reference.