Auto Delete import errors

[Treason]

OK this has came up before, but never recieved a straight responce. Sometimes a user would want to auto delete import errors. I am not a great VBA scripter so I am hoping some of you pro's can help me. This is what i got so far.... don't laught to hard. But this should give u a basic idea of what I am looking for.

Function DelErrors()

Dim Variname As String

Variname = "'*' & 'error' & '*'"
DoCmd.DeleteObject acTable, Variname

End Function

apparently wildcards are not useable here. So I also thought Right([Table_Reference?],12) like "ImportErrors"

I know I am off... but I am just throwing ideas out, Any help is appreciated

 

[ghudson]

The below function is how I test for an error table after my importing has finished.
If an error table is found, the user will get a message box stating the error, the error
table is printed and then deleted. Copy this function to a public module and call the
VerifyImportErrorTables() function at the end of your import process.

Public Function VerifyImportErrorTables()
On Error GoTo Err_VerifyImportErrorTables
    
    Dim tblDef As TableDef
    
    For Each tblDef In CurrentDb.TableDefs
    If InStr(1, tblDef.NAME, "ImportError") > 0 Then
        DoCmd.SelectObject acTable, tblDef.NAME, True
        DoCmd.PrintOut
        DoCmd.DeleteObject acTable, tblDef.NAME
        Beep
        MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report was sent to your default printer." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again.", vbInformation, "Import Errors"
    End If
    Next tblDef
    
Exit_VerifyImportErrorTables:
    Exit Function
    
Err_VerifyImportErrorTables:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_VerifyImportErrorTables
    
End Function

HTH

 

[Treason]

ghudson, that code works great! Thnx SOooOooO much =]

you rock

 

[djones]

Thanks Hudson, I've used a slightly modified version of your code to automate the dropping of all the ImportError tables. Many thanks. I tried using DROP TABLE but Access didn't seem to be playing ball. Your code has saved me a lot of time. Thanks again.

 

[ghudson]

You are welcome!