[AccessD] Can't append all the records in the append query - set 1 field(s) to null due to type conversion failure

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Thu Jun 30 01:37:24 CDT 2022


Thanks for your replies.

This has got me worried that I might have been creating problems (or
potential problems) for a considerable time - but I don't fully understand
it yet.

Firstly, OBBC Driver for SQL Server v17 was installed on all computers.  I
understand it to be the latest.  If it is the cause of type conversion
errors when the data includes fractional seconds then it is less 'graceful'
than Stuart suggests.

The Date/Time Extended data type (as mentioned by Stuart) was introduced in
Access 2021.  It is NOT compatible with older versions of Access.
My client still has a mixture of Access 365 and Access 2010 users so I can
not use this new Access data type (yet).
Stuart linked to a Microsoft page that says this new field can be used as a
primary key.  I'm not sure that would agree with my understanding of good
practice - but as noted above I need to review my understanding!

The ability to see decimal seconds does not directly exist in Access 2010.
Neither the Format statement nor the field format specification within
table design support it.  Stuart's suggested Format statement might work in
Access 2021+, but in Access 2010 the attempt to show decimal seconds is
interpreted as repeating the integer seconds after the decimal point, for
example:
? format$(now(),"d mmm yyyy hh:nn:ss.sssssss")
30 Jun 2022 09:40:02.0202022
30 Jun 2022 09:39:52.52525252
I have, however, verified that the Access 2010 Date/Time type and its
Date/Time field do indeed hold fractional seconds.  I did this by
converting it to a double (floating point), taking the fractional part
(representing the time since midnight) and scaling it to get seconds and
fractional seconds.  I could read, increment and write the table field with
a resolution of slightly less than 10 ms.  The VBA Date variable had a
resolution of slightly less than 1 ms.  In Access 2010 you can only set the
fractional seconds by converting from another data type, such as a VBA
double type.  Attempts to type fractional seconds through the user
interface are blocked.

In my particular case, the fractional seconds were created in SQL Server
with 1 ms precision (GETDATE()) and saved in a datetime table field by code
that is nearly 20 years old (and part of a system continues to evolve).
Microsoft's help on datetime has warned us for some time to "Use the time,
date, datetime2 and datetimeoffset data types for new work."
After one query successfully copied the value to a Date/Time field in the
user's front end database (because it is not shared by other users) another
(recently written) query copied it (sometimes without success) to a
datetime2(0) field in SQL Server.  My policy has been to use datetime2 on
all new fields with the lowest precision necessary - and I seldom need
fractional seconds.

This suggests I could potentially have a lot of places in my code with the
same problem.

I need to re-learn the official rules for type conversion between date +
time types.  I had assumed them to be 'inexact' types (like real. double,
floating point, ...) - and I had understood that conversion between groups
of 'inexact' types is always possible over a prescribed range with rounding
implicit.  Furthermore, 'inexact' types should never be tested for equality
or used as a primary key.  Clearly things are no longer so simple - not all
date + time types are meeting those expectations.

Microsoft are clearly heading in a direction I'm not understanding now that
they state the Access Date/Time Extended field type can used as a primary
key and claim it to be compatible with SQL Server datetime2 (except they
forgot to say datetime2(7) only)!

Can anyone please point me to good information on date + time type
conversion and recommended practice?  I note the internet is awash with
contrary opinions on preferences for datetime vs datetime2 that are not so
helpful.

Paul Wolstenholme


On Wed, 29 Jun 2022 at 19:05, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Ahem!  My turn :)
>
>
> https://support.microsoft.com/en-us/office/using-the-date-time-extended-data-type-708c32d
> a-a052-4cc2-9850-9851042e0024
> <https://support.microsoft.com/en-us/office/using-the-date-time-extended-data-type-708c32da-a052-4cc2-9850-9851042e0024>
>
> The Date/Time Extended data type stores date and time information and is
> similar
> to the Date/Time data type, but it provides a larger date range, a higher
> fractional
> precision, and compatibility with the SQL Server datetime2 date type. When
> you
> import or link Access data to SQL Server, you can consistently map an
> Access
> Date/Time Extended field to a SQL Server datetime2 column.
>
>
> --
> Stuart
>
> On 29 Jun 2022 at 6:53, Gustav Brock via AccessD wrote:
>
> > Hi Paul
> >
> > Access doesn't work very well with DateTime2, even though the newer
> > versions have an option under settings to be "compatible" - but that
> > does little, as DateTime2 in VBA is a no-no and probably ever will.
> >
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list