ACC2000: Cannot Trap Import Errors in Visual Basic for Applications (197596)

MORE INFORMATION

The same rules that apply to manual data entry also apply to methods for importing data when using Visual Basic for Applications. Therefore, any violations of rules, such as referential integrity, validation rules, or the Required property, will result in an incomplete import.

In Microsoft Access 2000, no trappable error is generated and if the procedure disables system messages by using the SetWarnings statement, there is no indication that any problem occurred.

Steps to Reproduce Behavior

1. Open any text editor, such as Notepad, and create the following new text file:

      OrderID,ProductID,UnitPrice,Quantity,Discount
      99999,54,7.45,20,0.000
   					

2. Save the text file as C:\My Documents\OrdDetails.txt.
3. Start Microsoft Access and open the sample database Northwind.mdb.
4. On the File menu, point to Get External Data, and then click Import.
5. In the Import dialog box, select Text Files in the Files Of Type box.
6. Locate the C:\My Documents folder, select OrdDetails.txt, and then click Import.
7. On the first screen of the Import Text Wizard, select the Delimited option, and then click Next.
8. On the second screen of the Import Text Wizard, select the First Row Contains Field Names option.
9. Click the Advanced button.
10. In the OrdDetails Import Specification dialog box, click Save As.
11. In the Save Import/Export Specification dialog box, type Order Details Specification in the Specification Name box, and then click OK.
12. Click OK to close the Order Details Specification dialog box.
13. Click Cancel to exit the Text Import Wizard.
14. Create a module and type the following line in the Declarations section if it is not already there:

    Option Explicit
    					

15. Type the following procedure:

    Sub ImportOrderDetails()
       On Error GoTo ImportOrderDetails_Error
       DoCmd.TransferText acImportDelim, _
          "Order Details Specification", _
          "Order Details", "C:\My Documents\OrdDetails.txt"
    
    Exit_ImportOrderDetails:
    
    Exit Sub
    
    ImportOrderDetails_Error:
       MsgBox "Error Handler was invoked."
       MsgBox CStr(Err) & " " & Err.Description
       Resume Exit_ImportOrderDetails
    End Sub
    					

16. To test this procedure, type the following line in the Immediate window, and then press ENTER.
    

    ImportOrderDetails
    						

    
    Note that you receive the following message:
    Microsoft Access was unable to append all the data to the table.
    
    The contents of fields in 0 records(s) were deleted, and 1 record(s) were lost due to key violations.... Do You want to proceed anyway?
    Click Yes. Note that the error handler is never executed. This indicates that the procedure did not encounter a trappable error.