Supress "type conversion failure" message and continue with Import (1 Viewer)

mrkipling

Registered User.
Local time
Today, 07:50
Joined
Mar 19, 2003
Messages
22
I have some vba code running on a timer event in a form, that imports text files into a table. Some of these text files have misformed records, and will not import correctly in the table using my specified import spec.

Is there a way I can supress the error message informing of the type conversion failure, and continue with the import.

Thanks

Mark
 

ghudson

Registered User.
Local time
Today, 02:50
Joined
Jun 8, 2002
Messages
6,195
How does that help the user if they are not aware of any import errors? You need to alert the user that they had a problem importing a file. The below function will check if there is an import errors table and alert the user if true and also print the first two pages of the errors table so that the user can determine which fields and records had a problem that prevented the importing of the file.

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
            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 two 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 two 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
            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.
        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
 

ghudson

Registered User.
Local time
Today, 02:50
Joined
Jun 8, 2002
Messages
6,195
This is the code to turn the warnings off. Ensure that you turn the warnings back on at the end of your code and also in your error handler!

Code:
DoCmd.SetWarnings False
'your code here
DoCmd.SetWarnings True
 

mrkipling

Registered User.
Local time
Today, 07:50
Joined
Mar 19, 2003
Messages
22
Thanks for the quick reply ghudson, The database form is not seen by users. The only interaction by users is an asp front page on our intranet.

It is importing a text file generated by a .vbs script on our network.

I need it to ignore the incorrect records in the text file and only import the correctly formatted records.

Will the vba code you've posted for suppressing all error messages, work with the error that is generated by the import ?

thanks for your help
 

ghudson

Registered User.
Local time
Today, 02:50
Joined
Jun 8, 2002
Messages
6,195
Yes. Try it and see for your self.
 

Users who are viewing this thread

Top Bottom