Improved ODBC DataType Mappings with Jet 4.0

Provided by Microsoft® (Last Reviewed: December 5, 2003)

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: "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: 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). 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.


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.

* An unsigned SQL TINYINT Maps to a Jet Byte, a sign SQL_TINYINT maps to a JET Integer.

ODBC SQL Type Precision Scale Jet 3.5 Type Jet 4.0 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_CHARL > 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

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 4.0 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

The following sample code can be used to verify most of the mappings in the tables above. Run the code using DAO 3.5 and DAO 3.6 (obtained with Office 2000) to verify the Jet 3.5 and Jet 4.0 data type mappings, respectively.

Note: The code requires a Microsoft SQL Server 7.0 database server; you need to adjust the connection string if the SQL Server database is on a different machine (change the SERVER=(Local); token to SERVER=Server Name;).

Option Explicit

' Various constant strings.
Const strConnect = "ODBC;Driver=SQL Server;SERVER=(Local);" & _
   "DATABASE=Pubs;UID=sa;PWD=;"
Const strSelectSQL = "select * from tmpAllTypes"
Const strDropTableSQL = "drop table tmpAllTypes"

' Outputs a listing of ODBC -> Jet Data Type mappings using a
' SQL Server 7.0 table as the data source.
Sub ODBCJetMapTest()
   Dim eng As New DAO.DBEngine
   Dim qd As DAO.QueryDef
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim f As DAO.Field
   Dim strSQL As String
	
   ' Verify DAO version used.
   ' Reference "Microsoft DAO 3.51 Object Library" to see 
   ' Jet 3.5 mappings or "Microsoft DAO 3.6 Object Library"
   ' to view Jet 4.0 ODBC mappings.
   Debug.Print "ODBCJetMapTest is using DAO version " & _
      eng.Version & "."

   ' Open SQL Server database connection.
   Set db = eng.OpenDatabase("", False, False, strConnect)
	
   ' Verify SQL Server version.
   Set qd = db.CreateQueryDef("")
   qd.Connect = strConnect
   qd.sql = "exec sp_server_info 500"
   Set rs = qd.OpenRecordset()
   Debug.Print "SQL Server version is " & _
      rs.Fields("attribute_value") & _
      " (version 7.X or greater required)."
   rs.Close

   ' Drop and re-create test table.
   On Error Resume Next
   db.Execute strDropTableSQL, dbSQLPassThrough
   On Error GoTo 0

   ' Build our create table SQL.
   strSQL = "CREATE TABLE tmpAllTypes("

   ' Common numeric mappings.
   AddField strSQL, "SQL_BIT", "bit", Empty
   AddField strSQL, "SQL_TINYINT", "tinyint", Empty
   AddField strSQL, "SQL_SMALLINT", "smallint", Empty
   AddField strSQL, "SQL_INTEGER", "int", Empty
   AddField strSQL, "SQL_REAL", "real", Empty
   AddField strSQL, "SQL_FLOAT", "float", Empty

   ' Decimal mappings.
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(4, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(5, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(9, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(10, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(15, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(16, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(28, 0)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(4, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(5, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(9, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(10, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(15, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(16, 1)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(28, 1)

   ' SQL Server specific mappings -> Currency.
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(10, 4)
   AddField strSQL, "SQL_DECIMAL", "decimal", Array(19, 4)

   ' Numeric mappings.
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(4, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(5, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(9, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(10, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(15, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(16, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(28, 0)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(4, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(5, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(9, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(10, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(15, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(16, 1)
   AddField strSQL, "SQL_NUMERIC", "numeric", Array(28, 1)

   ' Character mappings.
   AddField strSQL, "SQL_CHAR", "char", Array(255)
   AddField strSQL, "SQL_CHAR", "char", Array(256)
   AddField strSQL, "SQL_VARCHAR", "varchar", Array(255)
   AddField strSQL, "SQL_VARCHAR", "varchar", Array(256)
   AddField strSQL, "SQL_WCHAR", "nchar", Array(255)
   AddField strSQL, "SQL_WCHAR", "nchar", Array(256)
   AddField strSQL, "SQL_WVARCHAR", "nvarchar", Array(255)
   AddField strSQL, "SQL_WVARCHAR", "nvarchar", Array(256)

   ' Binary mappings.
   AddField strSQL, "SQL_BINARY", "binary", Array(255)
   AddField strSQL, "SQL_BINARY", "binary", Array(256)
   AddField strSQL, "SQL_BINARY", "binary", Array(510)
   AddField strSQL, "SQL_BINARY", "binary", Array(511)
   AddField strSQL, "SQL_LONGVARBINARY", "image", Empty

   ' Date mappings.
   AddField strSQL, "SQL_TIMESTAMP", "datetime", Empty
     
   ' Specialized mappings.
   AddField strSQL, "SQL_GUID", "uniqueidentifier", Empty, ")"

   ' Create table.
   db.Execute strSQL, dbSQLPassThrough

   ' Open recordset on table and dump out ODBC -> Jet mappings.
   Set rs = db.OpenRecordset(strSelectSQL, dbOpenForwardOnly, _
      dbReadOnly, dbReadOnly)
   For Each f In rs.Fields
      Debug.Print f.Name & " maps to " & GetJetTypeString(f.Type) & "."
   Next f
   rs.Close
   On Error Resume Next
   db.Execute strDropTableSQL, dbSQLPassThrough
   On Error GoTo 0
   db.Close
End Sub	

' Function to return string constant for Jet Type.
Function GetJetTypeString(lngDataTypeEnum As Long) As String
   Dim strReturn As String
   strReturn = "UNKNOWN"
   Select Case lngDataTypeEnum
      Case dbBigInt: strReturn = "dbBigInt"
      Case dbBinary: strReturn = "dbBinary"
      Case dbBoolean: strReturn = "dbBoolean"
      Case dbByte: strReturn = "dbByte"
      Case dbChar: strReturn = "dbChar"
      Case dbCurrency: strReturn = "dbCurrency"
      Case dbDate: strReturn = "dbDate"
      Case dbDecimal: strReturn = "dbDecimal"
      Case dbDouble: strReturn = "dbDouble"
      Case dbFloat: strReturn = "dbFloat"
      Case dbGUID: strReturn = "dbGUID"
      Case dbInteger: strReturn = "dbInteger"
      Case dbLong: strReturn = "dbLong"
      Case dbLongBinary: strReturn = "dbLongBinary"
      Case dbMemo: strReturn = "dbMemo"
      Case dbNumeric: strReturn = "dbNumeric"
      Case dbSingle: strReturn = "dbSingle"
      Case dbText: strReturn = "dbText"
      Case dbTime: strReturn = "dbTime"
      Case dbTimeStamp: strReturn = "dbTimeStamp"
      Case dbVarBinary: strReturn = "dbVarBinary"
   End Select
   GetJetTypeString = strReturn
End Function

' Function to append a SQL token to a SQL string.
Sub AddField(sql As String, FieldName As String, SQLType As String, _
PS As Variant, Optional Terminator As String = ",")
   If IsEmpty(PS) Then
      sql = sql & FieldName & " " & SQLType
   Else
      sql = sql & FieldName & "_" & Format(PS(0), "00")
      If UBound(PS) = 0 Then
         sql = sql & " " & SQLType
         sql = sql & "(" & PS(0) & ")"
      Else
         sql = sql & "_" & Format(PS(1), "00") & " " & SQLType
         sql = sql & "(" & PS(0) & "," & PS(1) & ")"
      End If
   End If
   sql = sql & Terminator
End Sub

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

The Information in this Article Applies to:

  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft OLE DB Provider for Jet 4.0

Keywords: kbDatabase kbinfo kbJET kbProvider KB214854

Table Design

Query Design

Form Design

Form Tips and Mistakes

Copy Command Button and Keep Picture

Module VBA to Forms and Controls

Form Navigation Caption

Resync Record in a Subform

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top 6 Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppress Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Add Buttons to the Quick Access Toolbar

Collapse the Office Ribbon for more space

VBA Programming

Basics: Forms and Controls

Run VBA Code from a Macro

Use Nz() to Handle Nulls

Avoid Exits in the Body of a Procedure

Shortcut Debugging Keys

Set Module Options

Math Rounding Issues

Rename a File or Folder

Avoid DoEvents in Loops

Age Calculations

Weekday Math

Send Emails with DoCmd.SendObject

Source Code Library

Microsoft Access Modules Library

Microsoft Access Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual SourceSafe

Deployment

Runtime Downloads

Simulate Runtime

Prevent Close Box

Disable Design Changes

Broken References

Remote Desktop Connection Setup

Terminal Services and RemoteApp Deployment

Reboot Remote Desktop

Missing Package & Deployment Wizard

Avoid Program Files Folder

Unavailable Mapped Drives

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Remove 'Save to SharePoint Site' Prompt from an Access Database

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

SQL Server Upsizing

Microsoft Access to SQL Server Upsizing Center

Microsoft Access to SQL Server Upsizing Center

When and How to Upsize Access to SQL Server

SQL Server Express Versions and Downloads

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

SQL Server Azure Usage and DTU Limits

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Connect with Us

 

Free Product Catalog from FMS