When using the SQL Server ODBC driver and SQL Server, and If ODBC preparedexecution is used, certain floating-point values may be incorrectly converted.Microsoft Access and Microsoft Visual Basic commonly use the ODBC preparedexecution. For example:
Dim db As DatabaseDim ds As DynasetSet ds = db.CreateDynaset("SELECT * FROM test")ds.AddNewds.Fields("col1") = 3.9ds.Update
A query that checks for equality of the float column to the value inserteddoes not show the record inserted, whereas a nonqualified query shows therecord. For example, the record set for the ds1 dynaset does not show therecord inserted, but ds2 dynaset will:
Set ds1 = db.CreateDynaset("SELECT * FROM test _ WHERE col1=3.9")Set ds2 = db.CreateDynaset("SELECT * FROM test")
The difference in behavior is because, in the case of prepared execution,the ODBC driver is doing the conversion to float. In the case of nonpreparedexecution and DB-Lib client tools, SQL Server is doing the conversion.To work around this problem, do an explicit convert on the SQL Serverusing a statement similar to this:
UPDATE test SET foo= (CONVERT(FLOAT, _ CONVERT(VARCHAR, col1)))
You can do the same thing within a trigger to automatically update thevalue for all new records inserted. Please note that this problem doesnot occur using the pass-through mechanism because in that case, the conversionis done by SQL Server.