Force TransferSpreadsheet to fail on key violation

Posted on 2009-12-30
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
    LVL 84

    Accepted Solution

    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.
    LVL 1

    Author Comment

    Damn, I was afraid that was the answer.
    LVL 58

    Expert Comment

    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.

    LVL 1

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Suggested Solutions

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now