INFO: Improved ODBC DataType Mappings with Jet 4.0

ID: Q214854

The information in this article applies to:

Microsoft Data Access Components version 2.1
Microsoft OLE DB Provider for Jet, version 4.0

SUMMARY

The Microsoft Data Access Components (MDAC) version 2.1 installs the Microsoft Jet 4.0 database engine. The Microsoft Jet database engine is used by the Microsoft Access ODBC Driver, the Microsoft Jet OLE DB Provider, and the Microsoft Data Access Objects (DAO) to provide access to Microsoft Access-format databases as well as various ODBC and Jet ISAM data sources.

For example, you can use ActiveX Data Objects (ADO), Remote Data Objects (RDO), or Data Access Objects (DAO) to gain access to Microsoft Jet by using any of the following methods:


   ADO -> OLE DB -> Jet OLE DB Provider -> Jet 
   ADO -> OLE DB -> MSDASQL -> ODBC -> Access ODBC Driver -> Jet
   DAO -> Jet
   RDO -> ODBC -> Access ODBC Driver -> Jet

Note that "MSDASQL" above is the Microsoft ODBC Provider for OLE DB, which is an OLE DB Provider that can talk to any ODBC driver (and thus allow ADO to talk to any ODBC Driver).

Microsoft Jet itself allows access to the following data sources:


   Jet -> ODBC -> Non-ODBC Desktop Driver -> Any non-Jet data source
   Jet -> Jet ISAM Driver -> Jet ISAM data source
   Jet -> Microsoft Access database

Note that an "ODBC Desktop Driver" is any one of the Microsoft ODBC drivers that uses Microsoft Jet internally to access a data source. Using any of the ODBC Desktop Drivers from Jet is not supported. For example, using the Microsoft Excel ODBC Driver is not supported from Jet. Jet does support using the Microsoft Excel ISAM driver, so the Excel ISAM driver can be used instead in this particular case. You can verify whether an ODBC driver is an ODBC Desktop Driver by examining the driver file name in the ODBC Administrator control panel under the Drivers section. If the driver file name is Odbcjt32.dll, then the driver is an ODBC Desktop Driver and is not supported for use with Jet via ODBC.

Microsoft Jet 4.0 provides an improved set of ODBC data type to Jet data type mappings versus the Microsoft Jet 3.5 database engine. For example, Jet 4.0 maps SQL_DECIMAL and SQL_NUMERIC type fields to a new Jet 4.0 data type called Decimal, providing a closer mapping to the actual ODBC data type. The Jet 4.0 Decimal data type is an exact numeric data type (called a scaled integer) that holds values from (10^28)-1 through -(10^28)+1. With the Decimal data type, you can define fields with precision and scale from (1,0) up to (28,28). Jet 3.5 maps SQL_DECIMAL and SQL_NUMERIC fields to the closest Jet numeric data type depending upon the precision and scale of the ODBC field, which in certain cases results in mapping to a non-exact (floating point) numeric Jet data type, such as Double.

MORE INFORMATION

Before the Microsoft Jet database engine maps its data types to an ODBC table, it calls the ODBC API function SQLColumns to gather information about the ODBC data type, precision, and scale for each column in the table. Using this ODBC type information, Jet matches each ODBC data type with the appropriate Jet data type. This article summarizes the ODBC data type to Jet data type mappings used by Jet 3.5 and Jet 4.0.

Note: The Jet data type LongBinary is listed as "Ole Object" in the Microsoft Access table designer user interface.

ODBC to Jet Data Type Mappings


ODBC SQL Type     Precision  Scale Jet 3.5 Type Jet 40 Type
-----------------------------------------------------------
SQL_BIT           N/A        N/A   Boolean      Boolean
SQL_TINYINT       N/A        N/A   Byte*        Byte*
SQL_TINYINT       N/A        N/A   Integer*     Integer*
SQL_SMALLINT      N/A        N/A   Integer      Integer
SQL_INTEGER       N/A        N/A   Long         Long
SQL_REAL          N/A        N/A   Single       Single
SQL_FLOAT         N/A        N/A   Double       Double
SQL_DOUBLE        N/A        N/A   Double       Double
SQL_DECIMAL       0 To 4     0     Integer      Decimal
SQL_DECIMAL       5 To 9     0     Long         Decimal
SQL_DECIMAL       10 to 15   0     Double       Decimal
SQL_DECIMAL       <=15       >0    Double       Decimal
SQL_DECIMAL       16 To 28   N/A   Text         Decimal
SQL_DECIMAL       > 28       N/A   Text         Text
SQL_NUMERIC       0 To 4     0     Integer      Decimal
SQL_NUMERIC       5 To 9     0     Long         Decimal
SQL_NUMERIC       10 to 15   0     Double       Decimal
SQL_NUMERIC       <=15       >0    Double       Decimal
SQL_NUMERIC       16 To 28   N/A   Text         Decimal
SQL_NUMERIC       > 28       N/A   Text         Text
SQL_CHAR          <= 255     N/A   Text         Text
SQL_CHAR          > 255      N/A   Memo         Memo
SQL_VARCHAR       <= 255     N/A   Text         Text
SQL_VARCHAR       > 255      N/A   Memo         Memo
SQL_LONGVARCHAR   N/A        N/A   Memo         Memo
SQL_WCHAR         <= 255     N/A   Unsupported  Text
SQL_WCHAR         > 255      N/A   Unsupported  Memo
SQL_WVARCHAR      <= 255     N/A   Unsupported  Text
SQL_WVARCHAR      > 255      N/A   Unsupported  Memo
SQL_WLONGVARCHAR  N/A        N/A   Unsupported  Memo
SQL_DATE          N/A        N/A   DateTime     DateTime
SQL_TIME          N/A        N/A   DateTime     DateTime
SQL_TIMESTAMP     N/A        N/A   DateTime     DateTime
SQL_BINARY        <=255      N/A   Binary       Binary
SQL_BINARY        256 To 510 N/A   LongBinary   Binary
SQL_BINARY        > 510      N/A   LongBinary   LongBinary
SQL_VARBINARY     <=255      N/A   Binary       Binary
SQL_VARBINARY     256 To 510 N/A   LongBinary   Binary
SQL_VARBINARY     > 510      N/A   LongBinary   LongBinary
SQL_LONGVARBINARY N/A        N/A   LongBinary   LongBinary
SQL_GUID          N/A        N/A   Text         Guid

* An unsigned SQL_TINYINT maps to a Jet Byte, a signed SQL_TINYINT
  maps to an Jet Integer.

Special ODBC-to-Jet Data Type Mappings For SQL Server

If Microsoft Jet is talking to the Microsoft SQL Server ODBC driver, then the following additional data type mappings occur:


ODBC SQL Type     Precision  Scale Jet 3.5 Type Jet 40 Type
-----------------------------------------------------------
SQL_DECIMAL       10         4     Currency     Currency
SQL_DECIMAL       19         4     Currency     Currency
SQL_NUMERIC       10         4     Currency     Currency
SQL_NUMERIC       19         4     Currency     Currency

REFERENCES

For more information on ODBC and Jet data type mappings, see Chapter 9 "Developing Client/Server Application" of the Microsoft Jet Database Programmer's Guide, Second Edition.

Additional query words:

Keywords : kbDatabase kbJET kbMDAC kbOLEDB kbProvider
Version : WINDOWS:2.1,4.0
Platform : WINDOWS
Issue type : kbinfo