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:
How can I prevent the import from happening again?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
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
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.
If you create a unique index on the table, you can prevent duplicates, which might be the easiest.
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.
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.
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.
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.
You'd do something along the lines of
db.Execute "INSERT INTO YourTableName (ImportID, importfilename, importdate) VALUES (YourImportID, 'YourImportFileName', Now())", dbFailOnError
db.Execute "INSERT INTO YourTableName (ImportID, importfilename, importdate) VALUES (YourImportID, '" & Me.FileList & "', Now())", dbFailOnError
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
ImportFileOnce.accdb
SampleImportFile.xlsx
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
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\Sam
In the FilePath field. Is there any way to capture just...
SampleImportFile.xlsx
In the event other users try to import the same file?
C:\Users\Steve\Desktop\Sam
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\Sam
SampleImportFile.xlsx
(In other words, everything following the last "\" mark)
I just about have this done. But I'm ending up with C:\Users\Steve\Desktop\Sam
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.
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_Clic
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]",
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 "delqryDeleteRecordsFromtb
DoCmd.Hourglass True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempParticipantImport"
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 "apndtblqryImportParticipa
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_Cli
Exit Sub
Err_cmdImportAndClean_Clic
MsgBox Err.Description
Resume Exit_cmdImportAndClean_Cli
End Sub
Private Sub cmdImportAndClean_Click()
On Error GoTo Err_cmdImportAndClean_Clic
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]",
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 "delqryDeleteRecordsFromtb
DoCmd.Hourglass True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTempParticipantImport"
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 "apndtblqryImportParticipa
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_Cli
Exit Sub
Err_cmdImportAndClean_Clic
MsgBox Err.Description
Resume Exit_cmdImportAndClean_Cli
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial