JWColby
jwcolby at colbyconsulting.com
Sun Apr 1 12:57:43 CDT 2007
I used the Access upsizing tool. 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 Dan Waters Sent: Sunday, April 01, 2007 1:47 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] SQL Server - Time only in date field Hi John, This is a side question - are you using the Access upsizing tool or the SSMA upsizing tool? Also, I know of a tool (I haven't used it) you can purchase from a company which provides a list of issues to look at prior to upsizing. It's at http://www.ssw.com.au/ssw/UpsizingPRO/. They also have a free trial version. I just looked at their site - their take on SSMA is that it's 'Not Ready'! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Sunday, April 01, 2007 12:17 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] SQL Server - Time only in date field 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com