Error debugging/information

[seisbye]

I'm very new to VBA in access, i've only played with excel..

But now my precious excel is overloaded, and i've started an database in access. to keep all the informations. I have 30 sheets with 20.000 lines in each(and it will keep increasing).. Now it take my excel sheet 20 min to open..

I've then made the this little peace of code to run them all..

Sub IMPORT()
DoCmd.RunSavedImportExport "Import-Turnover_Report_Austria_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_England_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_England2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Finland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_France_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany3_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Greece_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Holland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Iceland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Ireland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Italy_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Italy2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Japan_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Latvia_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Lithuania_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Norway_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Northern_Ireland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Poland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Portugal_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Scotland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain3_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain4_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Sweden_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Sweden2_2013"
End Sub

My problem is now, all the sheets are returned from a lot of different people, who manually fill in new data each week. which of course gives errors of all kinds. Which then gives me import error tables.

And here comes my problem. I've tried googling several hours now, i and i give up All the error tables are named the same. And i can't find out from which import it accurs..

I can only find topics where people want to delete them. But then i can't correct the error. And will then get missing data. so that is NOT the solution. I've tried all kinds of log error functions. Like this

Sub IMPORT()
On Error GoTo Err_SomeName
DoCmd.RunSavedImportExport "Import-Turnover_Report_Austria_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_England_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_England2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Finland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_France_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Germany3_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Greece_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Holland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Iceland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Ireland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Italy_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Italy2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Japan_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Latvia_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Lithuania_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Norway_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Northern_Ireland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Poland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Portugal_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Scotland_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain2_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain3_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Spain4_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Sweden_2013"
DoCmd.RunSavedImportExport "Import-Turnover_Report_Sweden2_2013"
Exit_SomeName:                           ' Label to resume after error.
     Exit Sub                   ' Exit before error handler.
Err_SomeName:                           ' Label to jump to on error.
     MsgBox Err.Number & Err.Description ' Place error handling here.
     Resume Exit_SomeName                ' Pick up again and quit.
 End Sub
End Sub
Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
    strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
On Error GoTo Err_LogError
    ' Purpose: Generic error handler.
    ' Logs errors to table "tLogError".
    ' Arguments: lngErrNumber - value of Err.Number
    ' strErrDescription - value of Err.Description
    ' strCallingProc - name of sub|function that generated the error.
    ' vParameters - optional string: List of parameters to record.
    ' bShowUser - optional boolean: If False, suppresses display.
    ' Author: Allen Browne,
    Dim strMsg As String      ' String for display in MsgBox
    Dim rst As DAO.Recordset  ' The tLogError table
    Select Case lngErrNumber
    Case 0
        Debug.Print strCallingProc & " called error 0."
    Case 2501                ' Cancelled
        'Do nothing.
    Case 3314, 2101, 2115    ' Can't save.
        If bShowUser Then
            strMsg = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press <Esc> to undo."
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            strMsg = "Error " & lngErrNumber & ": " & strErrDescription
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
        Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
        rst.AddNew
            rst![ErrNumber] = lngErrNumber
            rst![ErrDescription] = Left$(strErrDescription, 255)
            rst![ErrDate] = Now()
            rst![CallingProc] = strCallingProc
            rst![UserName] = CurrentUser()
            rst![ShowUser] = bShowUser
            If Not IsMissing(vParameters) Then
                rst![Parameters] = Left(vParameters, 255)
            End If
        rst.Update
        rst.Close
        LogError = True
    End Select
Exit_LogError:
    Set rst = Nothing
    Exit Function
Err_LogError:
    strMsg = "An unexpected situation arose in your program." & vbCrLf & _
        "Please write down the following details:" & vbCrLf & vbCrLf & _
        "Calling Proc: " & strCallingProc & vbCrLf & _
        "Error Number " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Unable to record because Error " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "LogError()"
    Resume Exit_LogError
End Function

But even if i step through the macro. It dosn't error. So i don't think Access see's it as an real import error.?

So How do I get Access to tell me from what workbook the import error comes from.. ?

On advance thanks..

 

[speakers_86]

After each line, you could check for the existence of that error table. If it exists, then the previous line (the import) had an issue. You could then rename that table, and move on, repeating the process.

 

[gemma-the-husky]

i sent you a PM with some thoughts.