When linking to a remote table, Microsoft Jet uses the ODBC SQLColumns() API call to gather information on the fields in the table. The information returned by SQLColumns() determines how the data type of each field will be mapped to a Microsoft Jet data type.
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 Jet Engine's mapping of the ODBC SQL_DECIMAL and SQL_NUMERIC types is detailed below. The mapping of these column types is problematic when the precision of a column is greater than 15. Under these circumstances, Jet maps the column to the Jet Text type. This is done to preserve precision, since the maximum precision of the Jet numeric types is 15. Unfortunately, once mapped to the Text type, the data is no longer recognized as being numeric.
To overcome this limitation, the
|ODBC Data Type||Jet Data Type†||Notes|
|Type||Precision p||Scale s|
|p <= 4||s = 0||Number (Integer)|
|p <= 9||s = 0||Number (Long Integer)|
|p <= 15||0 <= s <= 4||Number (Double)|
|p <= 15||s > 4||Number (Double)||"ODBC to Jet Data Type Mapping" document unclear here.|
|p = 19||s = 4||Currency||MS SQL Server only|
|p = 10||s = 4||Currency||MS SQL Server only|
|p > 15||irrelevant||Text||Not documented. Based on testing.|
†The Jet Data Type is a property setting available in the table Design view.
|Jet Data Type||Range|
|Integer||–32,768 to +32,767|
|Long Integer||–2,147,483,648 to +2,147,483,647|
|Double||–1.8E+308 –1.8E–308, +1.8E–308 +1.8E+308|
|Currency||–922,337,203,685,477.5808 to +922,337,203,685,477.5807|
- Microsoft Knowledge base article KB214854, "Improved ODBC
DataType Mappings with Jet 4.0".
- Chapter 9, "Developing Client/Server Application", of Microsoft Jet Database Engine Programmer's Guide, Second Edition.
Redmond, WA: Microsoft Press, 1997.