INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Error Messages

How do I get Access to display a message if Errors occur while Importing Data? by BillPower
Posted: 12 Dec 02

Access doesn't  give a message if errors occur while importing a file using the  TransferDatabase, TransferSpreadsheet or TransferText Methods.

This Function will instruct Access to give a message to the user if an error(s) occurs during the import and to display the name of the table where the import error(s) are documented.

Paste the following into a global module:

Global gPrevImportErrorsDate As Date

Public Function LookForImportErrors(strFileName As String, _
                                    booBeforeImport As Boolean)
Dim dbs As Database, tdf As TableDef
Dim dteDateCreated As Date, dteLatestImportErrorsDate As Date, dteLoopDate As Date
Dim strT_Name As String, strF_Name As String, BackSlashPos
On Error GoTo LookForImportErrors_Err
BackSlashPos = InStrRev(strFileName, "\")
strF_Name = Mid(strFileName, BackSlashPos + 1, Len(strFileName) - BackSlashPos - 4)
    Set dbs = CurrentDb
    dbs.TableDefs.Refresh
        With dbs
             For Each tdf In dbs.TableDefs
                 strT_Name = strF_Name & "_ImportErrors"
                 If Left(tdf.Name, Len(strT_Name)) = strT_Name Then
                     dteDateCreated = tdf.DateCreated
                     If dteLoopDate < dteDateCreated Then
                        dteLoopDate = dteDateCreated
                     End If
                 End If
             Next tdf
        .Close
        End With
        If booBeforeImport = True Then
           gPrevImportErrorsDate = dteLoopDate
        Else
           dteLatestImportErrorsDate = dteLoopDate
              If dteLatestImportErrorsDate > gPrevImportErrorsDate Then
                  Set dbs = CurrentDb
                  dbs.TableDefs.Refresh
                  With dbs
                       For Each tdf In dbs.TableDefs
                           If tdf.DateCreated = dteLatestImportErrorsDate Then
                              strT_Name = tdf.Name
                              MsgBox "Errors occurred during your import. " & _
                                     "A table called " & strT_Name & _
                                     " has been created describing these errors."
                           End If
                       Next tdf
                  .Close
                  End With
              End If
        End If
        Exit Function
LookForImportErrors_Err:
    MsgBox CStr(Err) & " " & Err.Description
End Function


Put the following behind a button to test it:

Dim strF_Name As String
On Error GoTo ImportFile_Err
strF_Name = "C:\My Documents\Import.txt"
    Call LookForImportErrors(strF_Name, True)
    DoCmd.TransferText acImportFixed, "RA Import", "tblWorkingRA", strF_Name, False
    Call LookForImportErrors(strF_Name, False)
    Exit Sub
ImportFile_Err:
    MsgBox CStr(Err) & " " & Err.Description


Once LookForImportErrors is placed in a module it can be used without any maintenance and can be used with TransferDatabase,   TransferSpreadsheet and TransferText Methods using any of the acImport Transfertypes. You might want  to edit the type of message box and message the user receives.

What LookForImportErrors() does:

1.Identifies the prefix name the table will be named if errors occur. If a file named ôC:\My Documents\Demo.txtö, by using a combination inStrRev, Mid and Len, identifies that if an error occurs that the table created will be called Demo_ImportErrorx, where x could be any number or no number, depending if errors have occurred on a previous import and whether the tables created were deleted or not.

2. To identify the table name, the function uses Table DefÆs  Name and DateCreated properties, firstly to see if there is an existing instance of  Demo_ImportErrorx and if there is gets the most recent date created and stores the date as a variable gPrevImportErrorsDate

3. Next, after the import has taken place, it checks the date created again of any tables called Demo_ImportError and stores the most recent date (if any) as a variable dteLatestImportErrorsDate .

4. It then compare dteLatestImportErrorsDate to gPrevImportErrorsDate and if  dteLatestImportErrorsDate   is more recent than gPrevImportErrorsDate , then an instance of Demo_ImportErrorx must have been created.

5. Finally, when itÆs been identified that a new table has been created, the Function gets the table name and informs the user.


To use the LookForImportErrors Function:

strF_Name = "C:\My Documents\Import.txt" Put the import file name and location here or you can reference it to a control on a form. strF_Name = Forms!MyFormName!MyControlName

Call LookForImportErrors(strF_Name, True)   Always put this before the Docmd.TransferXXXX, this tells the function the file name and location and True tells the function to perform pre import tasks.

DoCmd.TransferText acImportFixed, "RA Import", "tblWorkingRA", strF_Name, False   Alter this line to perform the import that suits you, do not change strF_Name"

Call LookForImportErrors(strF_Name, False)   Always put this after the Docmd.TransferXXXX, this tells the function the file name and location and False tells the function to perform post import tasks.

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close