[AccessD] Access + SQL Server Date/Time Conclusions

[Paul Wolstenholme]

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


On Thu, 7 Jul 2022 at 09:10, Paul Wolstenholme <
Paul.W at industrialcontrol.co.nz> wrote:

> Gustav,
>
> I had not considered compatibility with older drivers.  Thanks for
> pointing that out.
>
> The concept of non-uniform resolution and the measurements that best
> describe it was missing from my education.  My attempts to resolve this
> matter by looking for precise definitions of resolution in this context
> were totally thwarted by resolution being such an overused word.  About
> 3.333 ms and up to 4 ms and 3 or 4 ms are probably all correct.  I lack the
> resolve to look further.
>
> Paul Wolstenholme
>
>
> On Wed, 6 Jul 2022 at 19:57, Gustav Brock via AccessD <
> accessd at databaseadvisors.com> wrote:
>
>> Hi Paul
>>
>> I didn’t write 3.333 ms, I wrote about 3.333 ms as the exact value varies
>> and isn’t that important; actually, it is the average.
>>
>> DateTime is preferred among most Access developers, because it will cause
>> zero troubles and requires no concerns. It even works with the native ”SQL
>> Server” ODBC driver of Windows, should you be forced to work with that in
>> some locked down corporate environment.
>> Also, if you don’t operate with early dates and millisecond precision,
>> DateTime2 offers nothing extra.
>>
>> /gustav
>>
>> Fra: Paul Wolstenholme<mailto:Paul.W at industrialcontrol.co.nz>
>> Sendt: 5. juli 2022 23:09
>> Til: Access Developers discussion and problem solving<mailto:
>> accessd at databaseadvisors.com>
>> Emne: Re: [AccessD] Access + SQL Server Date/Time Conclusions
>>
>> Gustav,
>>
>> As you state, datetime has drawbacks over datetime2(3).
>>
>> I agree that all but one version of datetime2 are not preferred in
>> conjunction with Access - according to my proof.
>> What is the basis for your implicit inclusion of datetime2(3) in your
>> assertion that "In relation to Access, DateTime2 is in no way preferred"?
>> Not preferred by whom and why?
>>
>>
>> As a side note, I would assert that the resolution of datetime is up to 4
>> ms, as per this example SQL Server code:
>> DECLARE @RawMin datetime2(7) = '2022-07-04 12:34:56.0045000'
>> DECLARE @RawMax datetime2(7) = '2022-07-04 12:34:56.0084999'
>>
>> SELECT
>>     @RawMin AS RawMin
>>     , CAST(@RawMin AS datetime) AS RoundedFromMin
>>     , @RawMax AS RawMax
>>     , CAST(@RawMax AS datetime) AS RoundedFromMax
>> which yields:
>> RawMin                      RoundedFromMin          RawMax
>>      RoundedFromMax
>> --------------------------- -----------------------
>> --------------------------- -----------------------
>> 2022-07-04 12:34:56.0045000 2022-07-04 12:34:56.007 2022-07-04
>> 12:34:56.0084999 2022-07-04 12:34:56.007
>>
>> The millisecond digit of datetime values is always resolved to a member of
>> {0, 3, 7}.  The example values above (differing by almost 4 ms) are
>> resolved to the same datetime value.
>>
>> Paul Wolstenholme
>>
>> On Tue, 5 Jul 2022 at 18:52, Gustav Brock via AccessD <
>> accessd at databaseadvisors.com> wrote:
>>
>> > Hi Paul
>> >
>> > You could skip all these worries and just use DateTime in SQL Server.
>> > Compared to DateTime of Access, it has only two drawbacks which probably
>> > will be of no importance for most:
>> >
>> > 1. Earliest date of its range is 1753-01-01
>> > 2. Resolution is only about 3.333 ms
>> >
>> > In relation to Access, DateTime2 is in no way preferred, on the
>> contrary,
>> > neither are Date and Time which are subsets of DateTime2.
>> > Also, the old date/time data type of SQL Server - SmallDateTime - should
>> > never be used. It is very limited and can be regarded as obsolete.
>> >
>> > /gustav
>> >
>> > -----Oprindelig meddelelse-----
>> > Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com> På
>> > vegne af Paul Wolstenholme
>> > Sendt: 5. juli 2022 07:55
>> > Til: Access Developers discussion and problem solving <
>> > accessd at databaseadvisors.com>
>> > Emne: [AccessD] Access + SQL Server Date/Time Conclusions
>> >
>> > 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
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> https://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>