troubleshooting Question

Prevent import from happening more than one time

Avatar of SteveL13
SteveL13Flag for United States of America asked on
Microsoft Access
18 Comments4 Solutions75 ViewsLast Modified:
I'm trying to prevent the user from importing an Excel file with the same name if the file has already been imported.  Here is my code for the import:

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

    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

    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

How can I prevent the import from happening again?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros