[AccessD] Access + SQL Server Date/Time Conclusions

[Paul Wolstenholme]

Hi,

I've had a hard look at using Access with an ODBC link to SQL Server back
end tables to understand the issues that cause Date/Time problems.

My short answer is that I will now use the Access Date/Time data type and
the SQL Server datetime2(3) data type only for date and time handling to
avoid problems.

If you want the long answer...

The Access Date/Time extended data type was introduced in Access 2021.  It
is NOT compatible with older versions of Access.  My customer is not ready
for it - I can not use it.

Tests were done using Access 2010, OBBC Driver for SQL Server v17 (Release
number: 17.6.1.1, Released: July 31, 2020) and SQL Server Express 2014.
I did not try newer releases of the driver (v18 was released earlier this
year but is not listed on connectionstrings.com) because the release notes
don't refer to related issues.

*** In all tests performed, Access sent values to SQL Server as multiples
of 1 millisecond (0.001 s). ***

Values entered into a table (or presumably a form) and programmed date
constants are only accepted as multiples of 1 second.
In Access 2010 a variable of type double can be converted to a Date type (
CDate() or CV Date() ) yielding a resolution of under 1 microsecond (<
0.000001 s).  Access 2010 only shows these values rounded to a multiple of
1 second.
Access 2010 can retrieve values from Date/Time fields of Access tables with
a resolution of under 10 microseconds (< 0.000010 s).  Access 2010 only
shows these values rounded to a multiple of 1 second.
Values retrieved from elsewhere, such as linked SQL Server tables can also
contain small time increments.  It would appear these are held in Access
2010 as Date types.

Problem 1:
When editing a SQL Server table in Access, Access will always check whether
any value has changed before saving a record and warn that another user has
made a change during editing.
A false warning can be produced as a result of data rounding in which case
the user receives the warning and can not save the change.  It seems this
only occurs if the following 2 conditions occur:
1) The SQL Server table has no column of type 'timestamp' (meaning row
version).  This means Access can only check for changes by comparing data
values in all columns with values retrieved earlier.
2) SQL Server has provided a non-zero value in a column after the 1
millisecond column of any field.
Solution 1:
Either avoid datetime2(n) where n>3 or always remember to add a timestamp
column (or better still do both).

Problem 2:
Whenever Access tries to save a value to a SQL Server table that includes
non-zero values in a column not supported by the SQL Server field, a data
error is raised preventing the record from being stored.
This includes trying to save a Date that rounds to 2022-07-04 12:34:56.001
in a datetime2(2) field.  2022-07-04 12:34:56.010 can be saved successfully
in the same field with no data error.
Solution 2:
Because Access 2010 is rounding values to 1 ms precision before sending it
to SQL Server, avoiding the use of datetime2(n) where n<3 solves the
problem in all data cases.

Microsoft has advised that datetime should no longer be used for new work
in SQL Server.  This leaves datetime2(3) as the suitable replacement to
minimise problems.

Note that Access 2021 does support the new Date/Time Extended data type and
states it is compatible with SQL Server datetime2 (presumably
datetime2(7).  In that case Access 2021 will be sending data to SQL Server
data as multiples of 100 ns (0.0000001 s).
I am hopeful that no further issues have been introduced in that
environment provided I avoid using Date/Time Extended and avoid linking
Access to tables with datetime2(7).

My plan to avoid the repetition of the data error that bugged me is to
replace all datetime2(n) fields, where n <> 3 with datetime2(3).
Circumstances might mean they don't all need changing immediately, but if I
don't change them then future updates will make the system vulnerable to
further data errors cropping up.

Paul Wolstenholme