Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Detecting import error table (2003 sp3)

    I have a routine that imports a large amount of data from multiple spreadsheets. Occasionally an import error occurs so Access creates an import errors table for that particular spreadsheet. I need to identify the spreadsheets which cause the errors so I thought I could add a line that would check for the existence of the import errors table after each loop and if found I would use a message box to give the name of the spreadsheet. I tried to adapt the code Hans wrote in another post relating to this but I have not been successful.

    How can I detect the import errors table?
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Detecting import error table (2003 sp3)

    It would have been helpful if you had told us which other post you looked at.

    Here's a snippet of code you might use. It looks if there is an Import Errors table, and if so displays a message and deletes the Import Errors table (otherwise the message would keep on appearing).
    <code>
    Dim obj As AccessObject
    Dim strFile As String

    ...

    strFile = ...
    ' Import the spreadsheet
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", strFile, True
    ' Loop through the tables
    For Each obj In CurrentData.AllTables
    ' Inspect the name
    If obj.Name Like "*Import Errors" Then
    ' Display a message
    MsgBox "An error occurred while importing " & strFile, vbInformation
    ' Delete the error table
    DoCmd.DeleteObject acTable, obj.Name
    ' And exit the loop
    Exit For
    End If
    Next obj</code>

  3. #3
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detecting import error table (2003 sp3)

    Sorry Hans. The post is <post#=695155>post 695155</post#>. I'm still too much a novice at adapting code.
    Thanks
    chuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Detecting import error table (2003 sp3)

    The function in that post tests whether a table with a fixed name exists in an external database. You want to check whether a table with a variable name exists in the current database, so the code is quite different.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detecting import error table (2003 sp3)

    The snippet worked perfectly. With 301 spreedsheets involved, Hans, you are a life saver.
    Thanks
    chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •