Very Computer

I have been trying to migrate my company's Access 2000 database to SQL
server while maintaining the Access front-end.

When trying to import the current tables, I get an error.  The error
is due to the date fields.  They are date/time in Access 2000.  I have
tried importing them as either datetime or smalldatetime.  No luck.

They will import as text, but then I loose all sorting functionality
on the date fields.

I have scoured the Internet, all Microsoft Access public newsgroups,
and checked on IRC.  No one seems to have had this problem before.

What am I doing wrong?

Thanks,

Jonathan Roberts
Advantage Innovation

Jonathan,

The most likely reason for an Access date to fail in SQL Server is that the
date ranges supported by the two products differ. Access supports dates as
early as Jan 1, 100, while SQL Server only supports dates back to Jan 1,
1753 (and only Jan 1, 1900 if it is a smalldatetime data type).

Even if you don't think you have dates prior to Jan 1, 1753, remember that a
data entry error can easily cause a date that was supposed to be May 13,
1999 to be entered as May 13, 199.

Use Access to check for any dates prior to Jan 1, 1753.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> I have been trying to migrate my company's Access 2000 database to SQL
> server while maintaining the Access front-end.

> When trying to import the current tables, I get an error.  The error
> is due to the date fields.  They are date/time in Access 2000.  I have
> tried importing them as either datetime or smalldatetime.  No luck.

> They will import as text, but then I loose all sorting functionality
> on the date fields.

> I have scoured the Internet, all Microsoft Access public newsgroups,
> and checked on IRC.  No one seems to have had this problem before.

> What am I doing wrong?

> Thanks,

> Jonathan Roberts
> Advantage Innovation

Have you tried using the Upsizing Wizard? You may want to search the MSDN
and read some of the technical articles and white papers.

But then, this may do more than you want to. I did notice one table that
says the Date/Time data type in Access will upsize to a datetime data type
in SQL-Server.

Jim

Quote:> I have been trying to migrate my company's Access 2000 database to SQL
> server while maintaining the Access front-end.

> When trying to import the current tables, I get an error.  The error
> is due to the date fields.  They are date/time in Access 2000.  I have
> tried importing them as either datetime or smalldatetime.  No luck.

> They will import as text, but then I loose all sorting functionality
> on the date fields.

> I have scoured the Internet, all Microsoft Access public newsgroups,
> and checked on IRC.  No one seems to have had this problem before.

> What am I doing wrong?

> Thanks,

> Jonathan Roberts
> Advantage Innovation

Thanks for the tip.

I found the solution.

It turns out that some of the data in the Access date/time fields were
invalid.  ie. 1/1/199, etc.  I was able to locate the errors using SSW
Upsizing Pro.

Thanks again for your feedback.
JMR