Delete Import Errors File (1 Viewer)

Amileaux · Aug 28, 2003

I did search for the answer under Import errors and wildcard - so let me know what I should have done differently. My question:

I am importing files that have different dates, so the import errors file is never the same name. I want to delete these files after I import. However, DoCmd.DeleteObject acTable, like "*_ImportErrors" doesn't work, nor did DoCmd.DeleteObject acTable, "*_ImportErrors". How do I refer to a table using a wildcard? Thank you. Marie

namliam · Aug 28, 2003

You cant use wildcards here...
you can loop thru all tables and find the full Table name and delete that....

dim tbldef as tabledef
for each tbldef in currentdb.tabledefs
if tbldef.name like "*_importerror" then
delete
end if
next tbldef

or something like that, i didnt test it so might need a little addapting here and there...

also, if you are importing different files. you know the filenames there right? So why not (try and) delete them right then and there using the full filnames known there....

Regards

Amileaux · Aug 28, 2003

Thanks! Good to know when I'm charging down the wrong path! I'm going to try your second option first. Thanks again. Marie

ghudson · Aug 28, 2003

This function will delete any *ImportErrors* tables if they exist.
The table will also be printed before it is deleted. A message box
will also alert the user that all import records were not sucessful.
You can easily modify it to only delete the error tables.

Code:

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

Amileaux · Aug 28, 2003

Wow! It does work - I had about 7 import error files stacked up and this took care of all of them. Thank you very much.

Marie

jmriddic · Aug 11, 2004

Ok

Where do I get table def from it doesn't recoginize the data type.

namliam · Aug 12, 2004

Amileaux said:
Wow! It does work - I had about 7 import error files stacked up and this took care of all of them. Thank you very much. Marie

Which is just a detailed example of what i allready gave you... anyway...

namliam · Aug 12, 2004

jmriddic said:
Where do I get table def from it doesn't recoginize the data type.

1) Disambiguate:
Dim tblDef As DAO.TableDef
2) make sure your DAO reference is set...
Search the forum, and find out the how or why.. I think in the faq...

Regardz

caution · May 2, 2013

ghudson said:

This function will delete any *ImportErrors* tables if they exist.
The table will also be printed before it is deleted. A message box
will also alert the user that all import records were not sucessful.
You can easily modify it to only delete the error tables.

Code:

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

It will be very silly to ask but where exactly we write this code in VBA?

boblarson · May 2, 2013

caution said:
It will be very silly to ask but where exactly we write this code in VBA?

There is no set place. How do you want to deal with them? If you want it to happen just after you do an import via code, just put it into a standard module and then call it from your code.

I would suggest the procedure get modified to this, because if you have more than one Import table, it could get missed with the current code because once you delete something from the collection it gets reordered. So you should step backwards through it like this:

Code:

[I][SIZE=3][FONT=Calibri]Public Function VerifyImportErrorTables()[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]On Error GoTo Err_VerifyImportErrorTables[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri] [/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    Dim tblDef As TableDef[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri] [B][COLOR=red]   Dim z As Long[/COLOR][/B][/FONT][/SIZE][/I]
[I][FONT=Calibri][SIZE=3] [/SIZE][/FONT][/I]
[I][SIZE=3][FONT=Calibri][B][COLOR=red]      For z = CurrentDb.TableDefs.Count-1 To 0 Step -1[/COLOR][/B][/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    If InStr(1, tblDef.NAME, "ImportError") > 0 Then[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]        DoCmd.SelectObject acTable, tblDef.NAME, True[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]        DoCmd.PrintOut[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]        DoCmd.DeleteObject acTable, tblDef.NAME[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]        Beep[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]        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"[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    End If[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    Next tblDef[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri] [/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]Exit_VerifyImportErrorTables:[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    Exit Function[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri] [/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]Err_VerifyImportErrorTables:[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    MsgBox Err.Number & " - " & Err.Description[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri]    Resume Exit_VerifyImportErrorTables[/FONT][/SIZE][/I]
[I][SIZE=3][FONT=Calibri] [/FONT][/SIZE][/I]
[I][FONT=Calibri][SIZE=3]End Function[/SIZE][/FONT][/I]

Delete Import Errors File (1 Viewer)

Amileaux

Registered User.

namliam

The Mailman - AWF VIP

Amileaux

Registered User.

ghudson

Registered User.

Amileaux

Registered User.

jmriddic

Registered User.

namliam

The Mailman - AWF VIP

namliam

The Mailman - AWF VIP

caution

New member

boblarson

Smeghead

Similar threads

Users who are viewing this thread