Prevent import from happening more than one time

You'd need to have a table to track what files have been imported and after a successful import append the current files name (and date/time, username, ...) into the table.  Then you would, at the beginning of the procedure check it before running (using DLookup on the filename).

I have such a table setup.  But I can't figure out how to capture the file name that has been imported;

Is it not

Me.FileList

Select allOpen in new window

Off subject, but here a slightly modified version of your code

Private Sub cmdImportAndClean_Click()
    Dim db                    As DAO.Database
    Dim tblDef                As DAO.TableDef

    On Error GoTo Err_cmdImportAndClean_Click

    If IsNull(Me.FileList) Or Len(Me.FileList & "") = 0 Then
        MsgBox "Please select an Excel file."
        Me.cmdCancel.SetFocus
        Exit Sub
    End If

    If MsgBox("This function will import the Excel file you have selected into " & _
              "the Participants table. Do you want to continue?", _
              vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
        Set db = CurrentDb
        DoCmd.Hourglass True

        db.Execute "delqryDeleteRecordsFromtblTempParticipantImport", dbFailOnError

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempParticipantImport", Me.FileList, True

        For Each tblDef In CurrentDb.TableDefs
            If InStr(1, tblDef.Name, "ImportError") > 0 Then
                DoCmd.SelectObject acTable, tblDef.Name, True
                DoCmd.DeleteObject acTable, tblDef.Name
                Beep
            End If
        Next tblDef

        db.Execute "apndtblqryImportParticipantRecords", dbFailOnError

        MsgBox "The import file was successfully imported."
    Else
        MsgBox "Import Function Cancelled"
    End If

    DoCmd.Close acForm, "frmBrowse", acSaveNo

Exit_cmdImportAndClean_Click:
    On Error Resume Next
    DoCmd.Hourglass False
    Set tblDef = Nothing
    Set db = Nothing
    Exit Sub

Err_cmdImportAndClean_Click:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmdImportAndClean_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Exit_cmdImportAndClean_Click
End Sub

Select allOpen in new window

I don't see where that is writing the name of the file to a table.

You might need to think about restriction based on the file path\name alone or with other characteristics, such as modified date and size.  The most restrictive might be to compare hashes of the import file.

If you create a unique index on the table, you can prevent duplicates, which might be the easiest.

You might want to give some thought to what you want to consider as "the same file".
What properties of the file do you want include as "the same file"?
Same file name in the same folder with the same size and same file created date and same last-modified date?
Once you have this worked out, then you'll know what to store in your import log table.

I know I want ImportID (pk), importfilename, and importdate in the table. I just can’t figure out how to get importfilename and importdate into the table.

Well the solution is what other experts has pointed out...
A table which holds the filename and that's about all....well not exactly because in many cases we have identical filenames (the export functionality has the filename default)..so in that case is better to hash the file.
For this kind of functionality you can take a look here : Code for Calculating File Hash in a Standard module

Mark,  I really like where you are going with this.  I ended up with...

C:\Users\Steve\Desktop\SampleImportFile.xlsx

In the FilePath field.  Is there any way to capture just...

SampleImportFile.xlsx

In the event other users try to import the same file?

Also, I noticed that the table received the new file name when I tried again after renaming the file SampleImportFile2.xlsx.  I would have thought it would add the new record to the table, not replace it.  Can the code be changed to allow for the addition of the new file name?

To all:

I just about have this done.  But I'm ending up with C:\Users\Steve\Desktop\SampleImportFile.xlsx in the FilePath field.   Is there any way to capture just...

SampleImportFile.xlsx

(In other words, everything following the last "\" mark)

You can use the filesystemobject to parse the components of a file path.

or

You can use InstrRev() to get the position of the last path delimiter and then the Mid() function to parse the file name, starting after the path.

To each of you that contributed...  thank you.  I ended up with this although I'm sure there was a better way:

Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Click

    If IsNull(Me.FileList) Or Len(Me.FileList & "") = 0 Then
        MsgBox "Please select an Excel file."
        Me.cmdCancel.SetFocus
        Exit Sub
    End If
   
    'Check for duplicate file names...
    Dim importfilename As String
    importfilename = Dir(Me.txtFileName)
    If DCount("[importfilename]", "tblFileImportRecords", "[importfilename] = '" & Dir(Forms!frmBrowse!txtFileName) & "'") > 0 Then
        MsgBox "You have already imported a file with this same name, therefore you cannot import it again."
        Me.txtFileName = ""
        Me.FileList.RowSource = ""
        Exit Sub
    End If
    'End of check for duplicate file names

    If MsgBox("This function will import the Excel file you have selected into the Participants table. Do you want to continue?", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then

    Dim db As Database
    Set db = CurrentDb
    db.Execute "delqryDeleteRecordsFromtblTempParticipantImport", dbFailOnError

    DoCmd.Hourglass True

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempParticipantImport", Me.FileList, True

    db.Execute "INSERT INTO tblFileImportRecords (importfilename, importfiledate) VALUES ('" & Dir(Me.FileList) & "', Now())", dbFailOnError

    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.DeleteObject acTable, tblDef.Name
        Beep
    End If
    Next tblDef

    db.Execute "apndtblqryImportParticipantRecords", dbFailOnError

    Else

    MsgBox "Import Function Cancelled"

    DoCmd.Close , "frmBrowse", acSaveNo

    End If

    MsgBox "The import file was successfully imported."

    DoCmd.Close acForm, "frmBrowse", acSaveNo

    DoCmd.Hourglass False
 
Exit_cmdImportAndClean_Click:
    Exit Sub
   
Err_cmdImportAndClean_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportAndClean_Click
 
End Sub