Strange date behaviour in Access 2002 adp

[Ian Crofts]

I have been using an Access 2000 adp connecting to a SQL 2000 backend. After
upgrading to Access 2002, any datetime fields with a time only component,
say 8:45 AM now display as 30/12/1899 8:45:00AM. Fair enough to a point, as
Access is supposed to use this as the default date for time values. However,
if I add a time value to one of these fields using the adp under Access
2002, the time displays a time only (ie. 8:45:00AM) in Access. But if I look
at the table in Enterprise Manager the value shows as 1/01/1900 8:45:00AM.
So it looks as though Access is now using 1/01/1900 as the default date. I
can't for the life of me find where this could be configured. The same thing
happens whether the adp is converted to 2002 format or not. Any ideas?

 

[Van T. Dinh]

The problem is that JET/VBA uses 30/12/1899 as the zero-reference point but
SQL Server uses 01/01/1900 as the zero-reference point. JET/VBA reference
has been 30/12/1899 as far as I remember.

Since you use ADP, SQL Server is the database engine and therefore you now
have 01/01/1900.

There are no configuration options available ...

 

[Ian Crofts]

Thanks for the reply. I'm still confused though, as the application has
always been using an adp, with a SQL database. If what you say is correct,
shouldn't the app have been using 1/1/900 from the start?

 

[Sylvain Lafontaine]

Hi,

I use the following procedure to correct the time field from the Access 2000
ADP version to the 2002 version. Replace the name "Heure" with whatever you
use as the name of your field. This is for the french version of SQL-Server
but I think it will work directly under the english version without any
modification.

CREATE Procedure dbo.UpdateHours_SQL
AS
UPDATE t SET t.Heure = Convert (DateTime, Convert (char (10), t.Heure, 108))
FROM dbo.CalendTemp AS t
WHERE t.Heure is not null and Convert (char (10), t.Heure, 102) <>
'1900.01.01'
GO

It may not be the most efficient way of dealing with this but it works well
for me.

S. L.

 

[Ian Crofts]

Thanks for that. I will end up doing something like that - but at the moment
we are looking at some people accessing the same database from Access 2000,
and some from Access 2002. Looks like that won't be a possibility without a
lot of mucking about. Thanks again for the response.

 

[Van T. Dinh]

Not sure what happened in your case.

I was concerned about datetimes between Access and MS-
Server for a while also (I mainly use MDB, NOT ADP).
However, when I use Upsize or Append Query (from an Access
Table to an SQL Table), the dates are translated
correctly. The datetime values are shown correctly in MS-
SQL EM. When I link the SQL Table into an Access MDB, the
datetime values are also shown correctly as well.

I think the ODBC diver must be doing all sorts of
conversions / translations that we don't know about.

HTH
Van T. Dinh
MVP (Access)

 

[Sylvain Lafontaine]

One possible solution will be to use a char field instead of a datetime
field.

You can also use an unbound field and fill it from the time field when
entering the Current event and do the inverse in the OnBeforeUpdate event.
This should work well the single forms. In the case of continuous form, you
will have to add a temporary char field in the table somewhere to compute
and display this intermediary value.

It is possible that you can also put this conversion on the SQL-Server side
with differents Select statements for the two versions of Access and some
kind of stored procedure or InsteadOf trigger to replace things after an
update but I'm less sure about that; particularly if you don't want
Read-Only form.

One final solution would be to use unbound form. With unbound form, you can
do anything you want but they have higher costs in term of coding work.

All these solutions involve some work and therefore some $$$, so maybe the
best thing to do is simply to drop support for Access 2000 now.

S. L.