Error dbFailOnError SQL Server datetime for time values

Question

I have a linked table in SQL Server with a datetime column where I only save the time value *. If I execute an UPDATE query without dbFailOnError, it translates this command to SELECT, followed by separate UPDATE statements that execute one line 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 same UPDATE query, but with dbFailOnError, I get this translation:

UPDATE "dbo"."Appeals" SET HearingTime={t '09:00:00'} 

Interestingly, dbFailOnError forces a more efficient UPDATE at the back end, but my real problem is with the time value itself.

In the first example, Access sets the date and time correctly on 12/30/1899 (max. In the second case, this does not happen. The end result is that the first example "works" and the second does not.

, , HearingTime , Access :

9:00:00 AM

, ( , 9/3/16 - ):

9/3/2016 9:00:00 AM

, Microsoft. - ? , , , Microsoft - ?

---

* , , SQL Server time. MS Access, MS Access .

Answer 1

Access/Date SQL.

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#10:00:00#", dbFailOnError

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#1899-12-30 11:00:00#", dbFailOnError

[HearingTime] (2016-09-03), "" .

, / , ,

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime='1899-12-30 12:00:00'", dbFailOnError

( [HearingTime] SQL Server DATETIME "" 1900-01-01.)

, DAO.QueryDef

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

Answer 2

, .

, ODBC, , , - SQL Server, ODBC, , , Access.

, , , TimeValue . , , .

SQL Server , ODBC .