Force TransferSpreadsheet to fail on key violation

Posted on 2009-12-30
Medium Priority
Last Modified: 2012-05-08
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.
Question by:bejhan
  • 2
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 26149268
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.

Author Comment

ID: 26149277
Damn, I was afraid that was the answer.
LVL 58

Expert Comment

ID: 26151229
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.


Author Comment

ID: 26154576
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.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question