avoid creating an error table when using DoCmd.TransferText

[spnz]

Good afternoon,

I am hopying someone can help out with a question that I have.

I am trying to import a .csv file into a table in my database.I am using

DoCmd.TransferText acImportDelim, "", "TICK", strLocation, True, ""

It works great but always creates an a table that contains the errors from the import. I already know that there is going to be the errors before the import so I don't want access creating a table each time I do an import.


Is there anyway around this?

Thanks for your help

 

[ghudson]

Do you not want the user to know that there were errors [problems]
with the imported data?

You can not stop Access from creating the import errors table
but you can do something with it.

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.

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.PrintOut acPages, 1, 10 'only print the first ten pages
        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