Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS 
Author Message
 Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS

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



Fri, 16 Apr 2004 04:58:29 GMT
 Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS

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



Fri, 16 Apr 2004 07:40:36 GMT
 Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS
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



Fri, 16 Apr 2004 09:10:32 GMT
 Access 2000 to SQL Server 2000 using Import/Export Wizard or DTS
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

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



Fri, 16 Apr 2004 10:08:09 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Importing text files into SQL Server 2000 Database using DTS Import/Export Wizard

2. Access 2000 and Import/Export Wizard

3. using DTS to import Excel data into SQL Server 2000

4. How to import an access 2000 query into sql server 2000

5. Problems importing from Access 2000 to Sql Server 2000

6. Import / Export Question from MS SQL Server 2000 to MySQL Server

7. Importing Access 2000 data using DTS

8. SQL Server 2000: Potential Import Data Wizard BUG

9. Import wizard in Sql server 2000 break the referial integrity

10. Linking to an SQL Server 2000 table that uses bigint with Access 2000

11. problem in exporting sql table into csv file thro DTS in SQL Server 2000

12. Accessing Public Folder (Calendar exchange svr 2000) from SQL Server 2000 Using CDO


 
Powered by phpBB® Forum Software