ACC: Cannot Trap Import Errors in Visual Basic for Applications

Last reviewed: June 16, 1997
Article ID: Q169956
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

In Visual Basic for Applications, you cannot trap for import errors generated when you use the TransferText or TransferSpreadsheet methods to import or append data to a Microsoft Access table.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

STATUS

This behavior is by design.

MORE INFORMATION

The same rules that apply to manual data entry also apply to methods for importing data in 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 1.x and 2.0, a violation of these rules generates a trappable error, which makes it possible for developers to determine if the import completed successfully. In Microsoft Access 7.0 and 97, this behavior has changed so that no trappable error is generated. Furthermore, 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 folder C:\My Documents, select OrdDetails.txt, and 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 box, type "Order Details Specification" (without the quotation marks) 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 Debug 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.

    Click Yes. Note that the error handler never executes. This indicates that the procedure did not encounter a trappable error.


Keywords : IsmExl4 IsmExl5 IsmTxtd IsmTxtfx kbinterop kbprg PgmErr
Version : 7.00 97
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.