Prevent import from happening more than one time

SteveL13
SteveL13 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
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).

Author

Commented:
I have such a table setup.  But I can't figure out how to capture the file name that has been imported;
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Is it not
Me.FileList

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
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

Open in new window

Author

Commented:
I don't see where that is writing the name of the file to a table.
Top Expert 2014

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Steve,

I generally have an ImportHistory table, which keeps track of this.  This table generally contains an ImportID (autonumber), filename, and # of records.

I use the ImportID as a FK in whatever table it is I'm importing into, so that I can uninstall that import if I need to.

When I do the import, I check that table after the user selects the file.  If the filename exists, I tell the user and ask them if they want to cancel the import, or delete existing records and import again.
Mark EdwardsChief Technology Officer

Commented:
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.

Author

Commented:
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.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
You'd do something along the lines of

db.Execute "INSERT INTO YourTableName (ImportID, importfilename, importdate) VALUES (YourImportID, 'YourImportFileName', Now())", dbFailOnError

Open in new window

db.Execute "INSERT INTO YourTableName (ImportID, importfilename, importdate) VALUES (YourImportID, '" & Me.FileList & "', Now())", dbFailOnError

Open in new window

Mark EdwardsChief Technology Officer
Commented:
Here's a sample app that imports Excel and logs the import so that you don't import the same file again.
ImportFileOnce.accdb
SampleImportFile.xlsx
John TsioumprisSoftware & Systems Engineer

Commented:
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

Author

Commented:
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?

Author

Commented:
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?

Author

Commented:
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)
Software & Systems Engineer
Commented:
DIr("C:\Users\Steve\Desktop\SampleImportFile.xlsx")
will return SampleImportFile.xlsx
Top Expert 2014

Commented:
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.

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial