Access VBA: Discard "can't append" message (Primary Key Violation)

Question

I'm trying to create a macro in Access 2010 that opens an excel file, runs the macro in excel and then imports the given results. I have 2 problems with this process.

Application.DisplayAlerts = False in Excel Nevertheless DisplayAlerts keep popping up. Do I need to do something special in the macro Access?
Alert "Can't append due to primary key violations" keeps popping up. I know what the problem is, but I want to ignore it. I can use On Error Resume? But I want a at the end a messagebox with the the table it hasn't append to. Is this possible and can you point me in the right direction. I already tried some errorhandeling but i don't know how to make the message popup at the end without interrupting the process.

code:

Private Sub Main_btn_Click()

        Dim fileImport(0 To 3, 0 To 2) As String

        fileImport(0, 0) = "Stock_CC"
        fileImport(0, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\Stock_getdata.xlsm"
        fileImport(0, 2) = "GetStock"

        fileImport(1, 0) = "Wips_CC"
        fileImport(1, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\Wips_getdata.xlsm"
        fileImport(1, 2) = "Update"

        fileImport(2, 0) = "CCA_cc"
        fileImport(2, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\SLAcc.xls"
        fileImport(2, 2) = "Read_CCA"

        fileImport(3, 0) = "Eps_cc"
        fileImport(3, 1) = "F:\370\Hyperviseur\SITUATIE\Macro\eps.xlsm"
        fileImport(3, 2) = "Update"


        Dim i As Integer
        For i = 0 To UBound(fileImport, 1)
            RunMacroInxcel fileImport(i, 1), fileImport(i, 2)
            transferSpreadsheetFunction fileImport(i, 0), fileImport(i, 1)
        Next i
    End Sub

    Private Sub RunMacroInExcel(fName As String, macroName As String)
      Dim Xl As Object

    'Step 1:  Start Excel, then open the target workbook.
       Set Xl = CreateObject("Excel.Application")
        Xl.Workbooks.Open (fName)
        Xl.Visible = True
          Xl.Run (macroName)
           Xl.ActiveWorkbook.Close (True)
            Xl.Quit
          Set Xl = Nothing


    End Sub

    Private Sub transferSpreadsheetFunction(ByVal tableName As String, ByVal fileName As String)
        If FileExist(fileName) Then
            DoCmd.TransferSpreadsheet acImport, , tableName, fileName, True
        Else
        Dim Msg As String
            Msg = "Bestand niet gevonden" & Str(Err.Number) & Err.Source & Err.Description
            MsgBox (Msg)
          End If
    End Sub


    Function FileExist(sTestFile As String) As Boolean
       Dim lSize As Long
       On Error Resume Next
          lSize = -1
          lSize = FileLen(sTestFile)
       If lSize > -1 Then
          FileExist = True
       Else
          FileExist = False
       End If
    End Function

Is this the code that's giving you errors, or are the errors coming from the macros that are being executed within the 4 workbooks? You call RunMacroInxcel, but have Private Sub RunMacroInExcel. I presume that's just a copy/paste error. For your error #1 - Where are you setting Application.DisplayAlerts = False? I don't see it in this code. — FreeMan

Parfait Parfait · Accepted Answer · 2015-05-29T19:58:10

Add error handling within the For Loop, concatenate to a string variable, then message box the string:

Dim i As integer, failedFiles as string

failedFiles = "List of failed tables: " & vbNewLine & vbNewLine

For i = 0 To UBound(fileImport, 1) 
   On Error Goto NextFile
      Call RunMacroInxcel(fileImport(i, 1), fileImport(i, 2))
      Call transferSpreadsheetFunction(fileImport(i, 0), fileImport(i, 1)) 

NextFile:
      failedFiles = failedFiles & " " & fileImport(i,0) & vbNewLine 
      Resume NextFile2
NextFile2:
Next i

MsgBox failedFiles, vbInformation, "Failed Tables List"

Access VBA: Discard "can't append" message (Primary Key Violation)

1 Answers