Error Importing from Access to SQL Server

[miranda]

We are attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few questions.

1) Some of the Access tables have a single field for combined date time with the Access data type set to Date/Time and no formatting. The field is filled via an ASP script using the Now() function. When the conversion happens these become text data type in the SQL Server. Is there a way to have these become either the smalldatetime SQL Server data type or datetime SQL Server data type?

2) On some other Access Tables with the data type set again to Date/Time again no formatting is set but some columns are dates with the date in the Short Date format, and some columns are times in the Short Time format. Some of these columns may contain null values. This is causing the following error.

Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.

We thought maybe it was due to the naming of the one field as a Time so we changed the name of that field but again the same error occurs but with the new field name.

When we click on the transform button to view the data types none of them are listed as timestamp.

[ghell]

ive never used the DTS Import/Export Wizard but since all of these are dates is it possible that you are using smalldatetime instead of datetime so its losin accuracy.. or its just in the wrong format (when inserting/updating mssql i have to use 'MM-dd-yy' although i get an overflow if i dont use '' so that could be antoher reason).. tbh im grabbin at straws :o

[miranda]

Our Admin is letting the wizard determine the datatypes... I think that is the problem. It looks like the wizard is trying to make them a timestamp data type instead of a datetime data type. That is the only thing i can think of. I copied one of the Access Databases to my jump drive and brought it home and was able to upsize using the Access upsizing wizard. I will discuss this with him tomorrow.