Import from Excel with some bad dates causes no records to import

[Trillium97]

Access 2003
We have a financial program from which we can export to an Excel file.
I've set up a routine in Access to import from the excel file to tables in
SQL (linked to Access). I am using the VB line:

DoCmd.TransferSpreadsheet acImport, , strTableName, strFullPath, True

This has been running fine for the last few months however yesterday it
failed because there were three records in the Excel file with bad dates.
One date is something like "1/1/0860" and the other two dates are blank, and
the financial program output " / / ". Granted these are data entry errors
but it stopped the whole routine and none of the other records were imported,
and it took me quite a while to find the problem. The error returned is
"numeric overflow".

I tried changing the field type for this field in the receiving table to
nvarchar in SQL (which then is Text in Access). This did not fix the
problem. Why?

If I import to a local table, the good records come in and the bad ones go
to Import$Errors.

Do I need to have an intermediate step of importing to a local table, then
moving those with good dates to SQL? I can do that but that seems like an
extraneous step, and then I'll have to delete the contents of the local table
and compact and repair the Access database. There are lots of records each
month, this month about 55K records.

I don't want the users to have to examine the contents of the Excel file to
prep it for importing. I'm trying to simplify their workflow.

Thanks in advance for any thoughts.

 

[Ken Snell MVP]

SQL Server is much less forgiving of errors when importing data. The
behavior you see is not unusual.

If I were you, I would import to a local table in ACCESS, run queries to
clean up the data, then append to the table in SQL Server.