none
Trap Informational Messages RRS feed

  • Question

  • Experts -

    I have a little database here that will be passed off to a user, who will be importing data from Excel about once a month.  The table to which data will be appended has a primary key on claim #, and thus will not allow duplicate values to be appended (as planned).  I will instruct the user not to import the same file more than once, but in the event he does - I need a way to trap the "Microsoft Access was unable to append all data to the table...the contents of 0 fields were deleted, and x records were lost due to key violations..." message and repackage it as something more intelligible - like "You are reimporting data. Please don't."

    The problem is this is not an "error" message (an "informational" message maybe?), so I can't trap it via standard error handling. 

    Any ideas how to work around this?

    Thank you!


    Bonediggler

    Friday, May 20, 2016 8:24 PM

Answers

  • The message was generated during DoCmd.TransferSpreadsheet.  Anyway to work that in to your suggested approach?

    I would import the data in two stages.  Firstly import it into an empty 'holding' table by calling the TransferSpreadsheet method.  The execute an 'append' query to insert rows from the holding table into the operational table.  You can then trap the error by means of the dbFailOnError option of the Execute method of the database object.  This can easily be done as a single user operation by putting the code, which firstly would empty the holding table by executing a delete query, and then undertake the two import steps, into the Click event procedure of a command button.

    An alternative would be to create a link to the Excel file and execute an 'append' query from the linked table to the operational table.


    Ken Sheridan, Stafford, England

    • Marked as answer by Bonediggler Monday, May 23, 2016 3:00 PM
    Monday, May 23, 2016 2:14 PM
  • Hi Bone. I did mention earlier about importing the original data into a temporary table and depending on what you want to do with the duplicate records, I think you could simply use an OUTER JOIN to APPEND only those records which don't already exist in the permanent table, so there's no error to trap. If you want, you can also create a query to join the temporary and permanent tables to see if there are any duplicates and then show the result to the user or just let them know about them. Just my 2 cents...
    • Marked as answer by Bonediggler Monday, May 23, 2016 3:38 PM
    Monday, May 23, 2016 3:33 PM

All replies

  • I assume you mean you tried using an Error Handler and it wasn't triggered, correct? I haven't tried trapping this particular error but one approach I used in the past to avoid it is to add a file name field to the table to indicate which file was imported. So, if the user selects the same file during the import process, I can tell the user to select a different one. Just a thought...
    Friday, May 20, 2016 8:27 PM
  • Good idea DB guy - however, after thinking about this more deeply that won't always work as the same claim may appear on different reports.

    And yes, I do have error handling around the import functionality - and this message wasn't caught.  Presumably because Access does not classify it as an "error".


    Bonediggler

    Friday, May 20, 2016 8:31 PM
  • Well then, the next idea I might suggest is to import the data into a temporary table, so you can check it against the actual table for any duplicates. Hope it helps...
    Friday, May 20, 2016 8:32 PM
  • The message is classed as a 'warning'.  To suppress it and return your own message, instead of simply running the query call the Execute method of the database object with the dbFailOnError option.  You can then trap the error in the usual way.


    Ken Sheridan, Stafford, England

    Saturday, May 21, 2016 10:54 AM
  • Hi Ken -

    The message was generated during DoCmd.TransferSpreadsheet.  Anyway to work that in to your suggested approach?


    Bonediggler

    Monday, May 23, 2016 1:37 PM
  • The message was generated during DoCmd.TransferSpreadsheet.  Anyway to work that in to your suggested approach?

    I would import the data in two stages.  Firstly import it into an empty 'holding' table by calling the TransferSpreadsheet method.  The execute an 'append' query to insert rows from the holding table into the operational table.  You can then trap the error by means of the dbFailOnError option of the Execute method of the database object.  This can easily be done as a single user operation by putting the code, which firstly would empty the holding table by executing a delete query, and then undertake the two import steps, into the Click event procedure of a command button.

    An alternative would be to create a link to the Excel file and execute an 'append' query from the linked table to the operational table.


    Ken Sheridan, Stafford, England

    • Marked as answer by Bonediggler Monday, May 23, 2016 3:00 PM
    Monday, May 23, 2016 2:14 PM
  • Hi Bone. I did mention earlier about importing the original data into a temporary table and depending on what you want to do with the duplicate records, I think you could simply use an OUTER JOIN to APPEND only those records which don't already exist in the permanent table, so there's no error to trap. If you want, you can also create a query to join the temporary and permanent tables to see if there are any duplicates and then show the result to the user or just let them know about them. Just my 2 cents...
    • Marked as answer by Bonediggler Monday, May 23, 2016 3:38 PM
    Monday, May 23, 2016 3:33 PM
  • Hello DB guy -

    You are right - I actually ended up with this solution as a file can contain new as well as recycled claims.

    Thanks for the tip!


    Bonediggler

    Monday, May 23, 2016 3:38 PM
  • Hi Bone. You're welcome. Ken and I were happy to assist. Good luck with your project.
    Monday, May 23, 2016 3:50 PM