Force TransferSpreadsheet to fail on key violation

Right now when I import a spreadsheet using the TransferSpreadsheet function a pop up will appear asking me if I wish to continue in the case of key violations. I do not want to give the user the option of continuing. How can I just show an error message instead of having the prompt appear?

I have tried trapping the error, but this doesn't seem to throw an error unless you choose no on the prompt.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
AFAIK you can't ... if you need finer control, you should automate the import (i.e. automate Excel) and examine the data before you import.
bejhanAuthor Commented:
Damn, I was afraid that was the answer.
In fact, this is the reason most applications first import into a temporary table (without key validation or the like), and then manage the synchronisation process from the temporary table. This might be easier than to examine the data in Excel.

bejhanAuthor Commented:
I usually use temp tables when I need to better sanitize the data, but here I just wanted to avoid key violations, thought there might be an easier way.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.