[AccessD] SQL Server - Time only in date field

Hi John

OK, the year 931 is clearly out of the range for SQL Server.
The "missing" date from the time-only records must represent a bug in the upsizer tool.

/gustav

>>> jwcolby at colbyconsulting.com 01-04-2007 19:17 >>>
LOL, the upsizing wizard does not ETLL you what the problem is anymore,
although I think it used to.  Now it simply says "tblXXX failed to upsize".
>From past experience I knew that dates were a problem.  The "other date
problems" were dates before the valid date range of SQL Server.  Data entry
errors had left dates of 1/23/0931 for example where the intent was
1/23/1931.  Anything in 0931 is an invalid date to SQL Server but quite
valid to Access.  Likewise I had fields that only contained a date, using
the Time() function of VBA.  These are date fields that now contain
something like #13:53:02# with NO DATE.  These also failed to upsize until I
placed a bogus date in front of the time.  

The upsizing wizard used to IIRC update the table but generate an error
report with a list of records that failed to upsize.  Now (Access 2003) it
just silently fails and simply reports "tblXXX failed to upsize".  No clue
at all what went wrong.  I happen to know from hard experience that dates
caused me problems last time, and were in fact the ONLY thing that caused me
problems in the upsizing wizard, so I went looking for bad dates in the
specific tables that refused to upsize.  Sure enough, that is what I found.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Sunday, April 01, 2007 1:03 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] SQL Server - Time only in date field

Hi John

But isn't the upsize thingy an Access tool?
This SQL should cause zero problems:

  insert into dbo_timetable (timefield) values (#12:00:00 AM#)

Which "other date problems" did you encounter? The datetime data type of
Access/JET is very strict on holding valid dates/times only.
This sounds very weird.

/gustav