Type Conversion Failure using TransferSpreadsheet during Excel to Access Import
Posted on 2005-02-24
I am importing an Excel spreadsheet into a table in Access called [Import]. Each field on my Access Table has been declared as either a Text or Memo field.
When I run the TransferSpreadsheet command I am getting an Sheet1$_ImportErrors Table created. This tells me that in the Field "Date Added" on lines 41-48 have a Type Conversion Failure. I dont understand why this is as I'm importing the data to a Text field and I thought that Date Formats can get Parsed to Text Format no problemo.
The data on the Excel sheet in this field in those rows is as follows,
Row Date Added
46 June 2004
47 Dec 2004 ORC
48 Dec 2004 ORC
in some other rows the date format is "Sep-04".
My problem is that I dont want this error_table created. I want to load the excel sheet into an Import Table and then do the Type Checking myself using VBA code and then copy the data either onto my Project Tables or onto an Errors Table.
The weird thing is when I delete all the surrounding rows in the sheet, leaving just rows 41-48, then it loads fine and no error_table is created !
Can anyone advise me if there is a way of forcing the TransferSpreadsheet to do a type conversion to Text ignoring the Excel formats ?