Additional Reading

ODBC to Jet Data Type Mapping

Overview

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 OpenLink Jetfix option changes the ODBC type returned by SQLColumns for native database column types which would normally be reported as SQL_DECIMAL or SQL_NUMERIC. These include MONEY, DECIMAL, and NUMERIC columns in the remote table. With Jetfix enabled, these columns are typically reported as SQL_DOUBLE, which Jet always maps to Number (Double). The result is that Jet recognizes the data as numeric, albeit with a possible loss of precision, depending on the actual data values.

ODBC to Jet Data Type Mapping performed by Jet Engine

ODBC Data Type			Jet Data Type†	Notes
Type	Precision `p`	Scale `s`	Jet Data Type†	Notes
`SQL_DECIMAL` or `SQL_NUMERIC`	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 Ranges

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

References

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.