There are so many things that can go wrong with an import from Excel
(largely unstructured data) into Access (well typed data.)
If you want to know ahead of time about the errors you could try attaching
the Excel data, so you can run queries against it, and test for all the
possible problems.
A better idea might be to create a table in Access that has all fields of
type Text. Import the data, and then run your tests against this table. Test
for things like Nulls in a required column, zero-length strings (as distinct
from Null), wrong data types (e.g. someone type N/A into a Date or numeric
column), new values in a lookup table, duplicated values in a uniquely
indexed field, or even the possibity that the data in this spreadsheet has
already been imported. You may also want to test for data that is unlikely
to be right, such as a date in the year 200 or a future date.
Typically, you provide an interface that reports all the critical errors,
and allows the user to correct them in the temporary import table (without
altering the spreadsheet), and only when all critical errors are solved do
you allow the user to complete the process by importing into the real
tables. This final step consists of executing an Append query statement. The
query will co-erce the text-field data into the correct data types.
If you use the Execute method with the dbFailOnError switch, you can drop
out of the import when an error occurs--a quite different result than
producing the error table. For specifics, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
That will leave you with a partial result. To avoid that, you probably want
to execute the append query in a transaction. For an example of that, see:
http://allenbrowne.com/ser-37.html
It may seem that this involves more steps than you expected. Unfortunately,
that's what's involved in reliably importing data from a free-form structure
into a well-typed database.
