I have built this simple Access DB (2010) that imports 11 fields from Excel spreadsheet into a single table. What I have not done yet is to check if record in table exists and to not import it again creating duplicates.
Business Case: User receives hundreds of spreadsheets per day (a form is used by employees in a field). User saves all these spreadsheets in a designated folder from where, VBA code picks up only some data and inserts in a single table in Access DB. When import is complete I then ask user to move all those spreadsheets into archive folder manually. I understand this process is very prone to where same spreadsheet may be imported again. I would like add a check to my VBA code to check if record exists and ignore it. It would need to check 3 fields: employee name, date, and location as there should only be one report per employee per day per location. I am a novice and am just learning VBA so some solutions I found online are not sufficient enough for me at this point. Would like a specific example of code that I could reuse. My current code (which I also found online and modified to work for me) is something like this.
Function DoImport()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer
Dim strWorksheets(1 To 1) As String
Dim strTables(1 To 1) As String
strWorksheets(1) = "data"
strTables(1) = "my_table"
blnHasFieldNames = True
strPath = "folder path were user originally saves all reports"
For intWorksheets = 1 To 1
strFile = Dir(strPath & "*.xlsm")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
'MsgBox strPathFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTables(intWorksheets), strPathFile, blnHasFieldNames, strWorksheets(intWorksheets) & "$"
strFile = Dir()
Loop
Next intWorksheets
End Function
Thank you vary much.
