Undo an append?

[thebign]

If I import a text file and append it to an existing table (using the TransferText method), is there a way to undo the append if there are errors? For example, if a user tries to import a text file with an incorrect structure, sometimes data will still be appended to the existing access table but this data will be completely erroneous. Any help with this would be great. I'm using Access 2000. Thanks.

 

[Mile-O]

Add a new field that records the date and time a record was created. When you append this will enter the date and time for all your new records.

 

[thebign]

Add a new field that records the date and time a record was created. When you append this will enter the date and time for all your new records.

Thanks for replying. I created a date/time field in the access table. So should I run an update query and update the date/time (using the date and time functions) every time data is appended? If so, won't this simply update every record in the table to the same date and time? Am I understanding you correctly?

 

[ghudson]

You should bring your imported data into a temp table first. The temp table should be formatted exactly like the destination table. If there are any importing errors you will get a runtime error that can be trapped.

I like to use this function to check if there were any import error tables created. If any are found, then I will know there is a problem. The code will print the first 2 pages of the error table to give the user an idea of which fields have a problem. Then the code will delete the import error table.

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
        Beep
        MsgBox "There was an error importing all of your records." & vbCrLf & vbLf & "An error report will be sent to your default printer.  Only the first ten pages of the report will be printed." & vbCrLf & vbLf & "The error report will detail the error reason for each field and row number for each record [up to the first ten pages] that was not successfully imported from your file." & vbCrLf & vbLf & "Please correct all errors and import your data again." & vbCrLf & vbLf & "Do not cancel the print job or else the error table will not be deleted.", vbInformation, "Import Errors"
        DoCmd.SelectObject acTable, tblDef.Name, True
        DoCmd.PrintOut acPages, 1, 2 'only print the first two pages
        DoCmd.DeleteObject acTable, tblDef.Name
    End If
    Next tblDef
    
Exit_VerifyImportErrorTables:
    Exit Function
    
Err_VerifyImportErrorTables:
    If Err = 2501 Then 'The PrintOut action was canceled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.
        Beep
        MsgBox "You cancelled printing the error table report." & vbCrLf & vbLf & "The program was not allowed to delete the error table and will be printed the next time you import a data file.", vbCritical, "Print Error Table Aborted"
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_VerifyImportErrorTables
    End If
    
End Function