Import into ODBC linked-table error

[kaledev]

I am attempting to import an Excel file into SQL Server 08, by using an Access front-end. When I attempt to import the file, I recieve the following error:

ODBC--insert on a linked table 'dbLDDATA' failed.
[Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (#0)

I am not sure if it is a problem with SQL Server or Access. The error message makes it sound like a datatype in access isn't compatible with the one in SQL Server. The only thing is that I have no clue which of the 18 or so fields it has a problem with.

I have also tried importing into an access table, and then appending, with the exact same results. SQL Server formats dates as yyyy-mm-dd, so I reformatted fields to match, and made them text so it doesn't output incorrectly. (even if you format as yyyy-mm-dd, if you leave it as date/time it outputs as the original) Otherwise I am at a loss at to what may be causing this.

 

[kaledev]

I think I ended up solving this one...I changed all of my Date datatypes to Datetime in SQL Server, and Time datatypes to Varchar. After that all of my datetime fields importing fine from Access...

 

[dipak1022]

Thank you so much Kaledev. It solved my problem too!!