PRB: Jet/VBA May not Work Correctly w/ Datetime Data Containing Higher Precision for Fractional Seconds

• Microsoft Open Database Connectivity, versions 2.0, 3.0, 3.5

SYMPTOMS

In some cases, when running queries from Microsoft Access or any application that uses Jet/VBA, like Data Access Objects (DAO), that involve an ODBC timestamp or SQL datetime data with the fractional portion of the timestamp having a higher precision than milliseconds (3 digits), during updates you may get an error message titled Write Conflict as follows:

This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make the changes.

CAUSE

Jet/VBA retrieves the timestamp data and converts it to a native format for display. When the user tries to update such data, Jet/VBA has to convert the data stored in native format to ODBC timestamp. In some cases during this process the precision part of the fractional portion of the timestamp data may be lost, and the backend may not be able to update the data successfully. When this occurs you will receive the error.

STATUS

This behavior is by design.

MORE INFORMATION

This problem may occur with any applications using the Jet engine/VBA on their clients.

Microsoft SQL Server uses a maximum of three digits (milliseconds) for the fractional portion of the timestamp data, so the problem has not been observed with respect to Microsoft SQL Server.

Additional query words: timestamp odbc datetime

Keywords : kbDAO kbDatabase kbODBC KbVBA
Version : WINDOWS:2.0,3.0,3.5
Platform : WINDOWS
Issue type : kbprb