ODBCToJetDataTypeMappingAdditional 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 <tgroup><thead /><tbody> <row> </row> <para> </para><row> </row><para> </para><row><para> </para><entry> <para><computeroutput>SQL_DECIMAL</computeroutput> <emphasis>or</emphasis> <computeroutput>SQL_NUMERIC</computeroutput></para> </entry> <entry>p <= 4</entry> <entry>s = 0</entry><para> </para><entry> <computeroutput>Number (Integer)</computeroutput> </entry> <entry> </entry> </row> <row> <entry>p <= 9</entry> <entry>s = 0</entry> <entry> <computeroutput>Number (Long Integer)</computeroutput> </entry><para> </para><entry> </entry> </row> <row> <entry>p <= 15</entry> <entry>0 <= s <= 4</entry> <entry> <computeroutput>Number (Double)</computeroutput> </entry><para> </para><entry> </entry> </row> <row> <entry>p <= 15</entry> <entry>s > 4</entry> <entry> <computeroutput>Number (Double)</computeroutput> </entry><para> </para><entry>"ODBC to Jet Data Type Mapping" document unclear here.</entry> </row> <row> <entry>p = 19</entry> <entry>s = 4</entry> <entry> <computeroutput>Currency</computeroutput> </entry> <entry>MS SQL Server only</entry><para> </para></row> <row> <entry>p = 10</entry> <entry>s = 4</entry> <entry> <computeroutput>Currency</computeroutput> </entry> <entry>MS SQL Server only</entry> </row><para> </para><row> <entry>p > 15</entry> <entry> irrelevant </entry> <entry> <computeroutput>Text</computeroutput> </entry> <entry>Not documented. Based on testing.</entry> </row><para> </para></tbody></tgroup></table><para> †The Jet Data Type is a property setting available in the table Design view.</para><bridgehead class="http://www.w3.org/1999/xhtml:h4">Jet Data Type Ranges</bridgehead> <para> </para><table><title /><tgroup><thead /><tbody> <row><entry> <emphasis>Jet Data Type</emphasis> </entry><entry> <emphasis>Range</emphasis> </entry></row> <row><entry><computeroutput>Integer</computeroutput> </entry><entry> –32,768 …to +32,767 </entry></row> <row><entry><computeroutput>Long Integer</computeroutput> </entry><entry> –2,147,483,648 …to +2,147,483,647 </entry></row> <row><entry><computeroutput>Double</computeroutput> </entry><entry> –1.8E+308 … –1.8E–308, +1.8E–308 … +1.8E+308 </entry></row> <row><entry><computeroutput>Currency</computeroutput> </entry><entry> –922,337,203,685,477.5808 …to +922,337,203,685,477.5807 </entry></row> </tbody></tgroup></table> <para> </para><bridgehead class="http://www.w3.org/1999/xhtml:h4">References</bridgehead> <para> </para><itemizedlist mark="bullet" spacing="compact"><listitem> Microsoft Knowledge base article <ulink url="support.microsoft.com/kb/214854">KB214854, "Improved ODBC DataType Mappings with Jet 4.0"</ulink>. </listitem> <listitem> Chapter 9, "Developing Client/Server Application", of <ulink url="http://openlibrary.org/b/OL802690M/Microsoft_Jet_database_engine_programmer%27s_guide">Microsoft Jet Database Engine Programmer's Guide, Second Edition</ulink>. Redmond, WA: Microsoft Press, 1997. </listitem> </itemizedlist></section></docbook>