We help IT Professionals succeed at work.
Get Started

Prevent import from happening more than one time

SteveL13
SteveL13 asked
on
72 Views
Last Modified: 2020-01-14
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

Open in new window


How can I prevent the import from happening again?
Comment
Watch Question
IT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 4 Answers and 18 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE