Is there a way to indicate a source file name on Access's automatic import error table?

[keith_shoaf]

Hello everyone! I am hoping someone can help with a little problem I am having with my Access micro-application. I have a procedure that, among other things, loops through a folder that contains around 50 files and imports each excel file into an import table. The program runs fine, however, when finished, I have a new table in Access that has logged an import error. The table name is M_ImportErrors, and when I open it, it shows that there was a type conversion failure in the Amount field, row 50. I know this means that in one of the source excel files, there is a record on row 50 that has a format issue in the amount column. However, the error table does not indicate which of the 50 source files contains the issue. Is there a way to make Access capture the file name of the problem file? Thanks!

 

[ranman256]

after every import,youd need to check for the table 'M_ImportErrors'
then rename it before moving to the next file:

Public SUB getFilesInDir(ByVal pvDir) 
Dim FSO, oFolder, oFile, oRX
Dim colFiles As New Collection
Dim sTxt As String, sFile As String
const kERRTBL = "M_ImportErrors"

on error goto errGetFiles

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)

For Each oFile In oFolder.Files
 If InStr(oFile.Name, ".xls") > 0 Then 
          'import file here
      'docmd.Transferspreadsheet....or other method
      

               'IMPORT ERROR TABLE CREATED, RENAME IT
      if currentdb.tabledefs(kERRTBL).name = kERRTBL  then
            currentdb.tabledefs(kERRTBL).name = kERRTBL & "_" & oFile.Name
      ENDIF     
 End If
nextFile:
Next


endit:
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
exit sub


errGetFiles:
If Err = 3265 Then     'catch error if NO Import table errors
  ' "no errors found"
   resume nextFile
else
  MsgBox Err.Description, , Err
endif
End sub