There's a bit more than just adding an error handler to get this to work
well.
There are 2 ways to attack the problem:
a) Handle the problems before appending the data to the real table.
b) Try to sort it out afterwards.
IMHO (a) is better, but TransferText uses (b).
It creates a table named "Import Errors" or similar so you can sort it out
afterwards. Looking in that table gives you some idea of what went wrong. If
you don't know why Access is rejecting those records, here's a list of some
of the reasons why the import might fail:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html
The better solution is to sort out the problems before the data appended to
the real table. This takes a bit more work, but is much more reliable IMHO.
You create a table with all Text fields (so you get no data mismatch
errors), no validation rules, no relationships to other tables, and an
AutoNumber primary key field last in the table (so the data gets appended
into the preceding fields.)
Next you build a from to perform the import. The first button deletes any
data in the temp table (from previous imports), and performs the
TransferText into the temp table. Your code then runs a series of tests for
everything that could go wrong: zero-length text fields, wrong data type,
bad dates, values that don't match anything in foreign key fields, and so
on. You flag these records and load them into a list box or the form itself
(typically in Continuous view), so the user can fix up these situations.
Once the user has handled all the problems, you enable the final command
button at the bottom of the form, which executes an append query to add the
data to the real table(s). If any error occurs during this step (typically
something you forgot to check for), you can trap this error by running using
the Execute method with dbFailOnError. If that's new, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
A failed Execute can still leave you with partial records in the final
table, so you probably want to wrap that operation in a transaction so you
can roll the whole thing back. For an example of using a transaction, see:
Archive: Move records to another table - copy + delete in a transaction
at:
http://allenbrowne.com/ser-37.html