Avatar of SteveL13
SteveL13Flag for United States of America

asked on 

Prevent import from happening more than one time

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?
Microsoft Access

Avatar of undefined
Last Comment
SteveL13
Avatar of Daniel Pineault
Daniel Pineault

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).
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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

Open 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

Open in new window

Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

I don't see where that is writing the name of the file to a table.
Avatar of aikimark
aikimark
Flag of United States of America image

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.
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

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.
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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?
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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?
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of aikimark
aikimark
Flag of United States of America image

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.
Avatar of SteveL13
SteveL13
Flag of United States of America image

ASKER

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
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo