How do I get Access to display a message if Errors occur while Importing Data?

Access doesn't give a message if errors occur while importing a file using the [color blue]TransferDatabase, TransferSpreadsheet[/color] or [color blue]TransferText[/color] 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:

[color blue]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[/color]

Put the following behind a button to test it:

[color red]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[/color]

Once LookForImportErrors is placed in a module it can be used without any maintenance and can be used with [color blue]TransferDatabase, TransferSpreadsheet[/color] and [color blue]TransferText[/color] Methods using any of the [color blue]acImport[/color] 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 ô[color blue]C:\My Documents\Demo.txt[/color]ö, by using a combination [color blue]inStrRev, Mid and Len[/color], identifies that if an error occurs that the table created will be called [color blue]Demo_ImportError[/color][color red]x[/color], where [color red]x[/color] 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 [color blue]Table DefÆs Name[/color] and [color blue]DateCreated[/color] properties, firstly to see if there is an existing instance of [color blue]Demo_ImportError[/color][color red]x[/color] and if there is gets the most recent date created and stores the date as a variable [color blue]gPrevImportErrorsDate [/color]

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

4. It then compare [color blue]dteLatestImportErrorsDate [/color] to [color blue]gPrevImportErrorsDate [/color] and if [color blue]dteLatestImportErrorsDate [/color] is more recent than [color blue]gPrevImportErrorsDate [/color], then an instance of [color blue]Demo_ImportError[/color][color red]x[/color] 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:

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

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

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

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