[AccessD] Access + SQL Server Date/Time Conclusions

[Gustav Brock]

Hi Paul

Thank you this info.

I guess we will have to live with this until the day when DateTime2 is fully implemented in VBA – which is not likely to happen.

/gustav

Fra: Paul Wolstenholme <Paul.W at industrialcontrol.co.nz<mailto:Paul.W at industrialcontrol.co.nz>>
Sendt: 11. august 2022 00:02
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>>
Cc: Gustav Brock <gustav at cactus.dk<mailto:gustav at cactus.dk>>
Emne: Re: [AccessD] Access + SQL Server Date/Time Conclusions

I have found unexpected behaviour when using datetime2(3) with Access365.
I now agree that datetime is the best SQL Server date-time data type to use for compatibility with Access.

The issue I found results in records that Access365 refuses to edit in ODBC forms when particular data values exist in the datetime2(3) field.  Any attempt to save changes to the record will result in an error stating that the record contents have been changed and that the edits may either be dropped or copied to the clipboard.

The issue was observed on Access 365 64 bit with SQL Server Express 2014 and ODBC Driver 17 for SQL Server.  A SQL Server table was created with an integer identity column (autonumber) and various forms of date-time columns including datetime2(3).  There was no 'timestamp' (row version) column (including this column is a known technique to avoid exactly this issue if the developer remembers to include it).  The table was linked to a database created by Access 2010 but later opened and edited under Access365.  A form was created to edit the linked table records.  The datetime2(3) field of a record was set to 2022-08-10 17:14:58.692.  Note that the fractional seconds can not be entered directly in the form, but it is perfectly acceptable to create fractional seconds in Access queries or VBA code (notably through the use of CDbl() and CDate() ) or with various SQL Server operations.

I have detailed shortcomings with all the other datetime2(n) field types in a previous post (below).

If this unexpected behaviour was not expected by the developer, that might be the definition of a bug.

Paul Wolstenholme

<snip>