I have a linked table in SQL Server with a datetime column where I store a time-only value*. If I execute an UPDATE query without dbFailOnError it translates that command into a SELECT followed by individual UPDATE statements that execute one row at a time:
exec sp_executesql N'UPDATE "dbo"."Appeals" SET "HearingTime"=@P1
WHERE "AppealID" = @P2'
,N'@P1 datetime,@P2 int','1899-12-30 09:00:00',1
...
exec sp_executesql N'UPDATE "dbo"."Appeals" SET "HearingTime"=@P1
WHERE "AppealID" = @P2'
,N'@P1 datetime,@P2 int','1899-12-30 09:00:00',4
If I execute the exact same UPDATE query, but with dbFailOnError, I get this translation:
UPDATE "dbo"."Appeals" SET HearingTime={t '09:00:00'}
It is interesting that the dbFailOnError forces a more efficient UPDATE on the back end, but my real concern is with the time value itself.
In the first example, Access correctly sets the datetime to 12/30/1899 (MS Access's magic "zero" day). In the second case, that does not happen. The end result is that the first example "works" and the second one does not.
What I mean by that, is that if I view the HearingTime field in datasheet view, Access shows the first one as:
9:00:00 AM
And the second one shows as (as of this writing, 9/3/16 is today's date):
9/3/2016 9:00:00 AM
I have to assume this is a bug on Microsoft's part. Or am I missing something here? Do I have a better option than reporting the bug and just hoping that Microsoft fixes it someday?
*Yes I am aware there is a time datatype in SQL Server. It is not compatible with the MS Access datetime type, so it's of little use to me in an MS Access linked table.
I was able to reproduce your issue using queries with Access SQL Date/Time literals. Both
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#10:00:00#", dbFailOnError
and
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#1899-12-30 11:00:00#", dbFailOnError
resulted in [HearingTime] values with the current date (2016-09-03), not the "zero" date.
However, passing the full date/time as a string seems to work
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime='1899-12-30 12:00:00'", dbFailOnError
(Omitting the date part in the string value results in [HearingTime] values with the SQL Server DATETIME "zero" date of 1900-01-01.)
And, even better, a parameterized query with a DAO.QueryDef object also appears to work correctly
Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("", _
"PARAMETERS prmHearingTime DateTime;" & _
"UPDATE dbo_Appeals SET HearingTime=[prmHearingTime]")
qdf!prmHearingTime = #10:00:00 AM#
qdf.Execute dbFailOnError