Handling data errors from DoCmd.TransferSpreadsheet & odd behaviour

  • Thread starter Thread starter robbinma
  • Start date Start date
R

robbinma

Hi,

I am using DoCmd.TransferSpreadsheet to transfer some data from Excel
spreadsheets into a database using MSAccess.

Does anyone know how to control what MSAccess does when it finds data
errors when it is using TransferSpreadsheet?

I am using it to load the data from about 20 spreadsheets in an Excel
workbook. If I load the data into an MSAccess table then it puts
details of any errors into sequentially named error tables based on the
range being loaded.
e.g. Z135_ImportErrors1
Z135_ImportErrors2
The other data gets loaded any the row/field combination gets put into
this table

I then have to go back and read each table in turn and process the
results.
It would be nice if I could specify the name of the error table before
the operation so I could control it e.g. worksheet_ABC_ImportErrors1

Anyone know how to do this?

If I load the data into a linked Oracle table then I just get a data
conversion error and the whole load fails with no indication of where
the problem is in the data. :mad:

Any ideas about how I can control this without having to write VBA that
reads the worksheet column by column and then populates the database.
Something that I would like to avoid.

Regards,

Mark
 
Back
Top