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.
LVL 1
bejhanAsked:
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.
0
 
bejhanAuthor Commented:
Damn, I was afraid that was the answer.
0
 
harfangCommented:
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.

(°v°)
0
 
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.
0
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.