Prevent import of duplicate txt file

[Nick X]

Hello all,
I have a button on a form that imports a comma delimited text file. It then
runs an update statement which concatenates data into a memo field. Is there
a way to prevent the same text file from being imported more than once? I
was thinking if I wrote the name of the file to a table, that would do the
trick or maybe some sort of like statement that looks at the memo field for a
certain number of matching characters.


Dim strSQLdel As String
Dim strSQLupd As String
Dim strFilter As String
Dim strInputFileName As String

strSQLdel = "DELETE tblImport_temp.*" & "FROM tblImport_temp;"

CurrentDb.Execute strSQLdel, dbFailOnError

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
Dim strFile As String

DoCmd.TransferText acImportDelim, _
"BM_ASCIIimport", "tblImport_temp", strInputFileName

strSQLupd = "UPDATE tblImport_temp, PtblBenchmark SET PtblBenchmark.BMNum =
[tblImport_temp].[BMNum], PtblBenchmark.Northing =
[tblImport_temp].[Northing], PtblBenchmark.Easting =
[tblImport_temp].[Easting], PtblBenchmark.ElevGPS =
[tblImport_temp].[ElevGPS], PtblBenchmark.Remarks = [PtblBenchmark].[Remarks]
& "" "" & [tblImport_temp].[Remarks], PtblBenchmark.GPSDate = Now()" _
& "WHERE (((PtblBenchmark.BMNum)=[tblImport_temp].[BMNum]));"

CurrentDb.Execute strSQLupd, dbFailOnError
Me.Dirty = False
Me.Refresh

MsgBox "Data file has been imported.", , "Import Complete"

Thanks in advance,
Nick X

 

[Nick X]

OK,
I added tblImport_Log with an autonumber and a text field(255). I also
added a Dlookup and an If statement to my code:
strTemp = DLookup("FileName", "tblImport_Log", "FileName =" &
strInputFileName)

'The criteria in the dlookup returns the whole file path though.

If strTemp <> strInputFileName Then
DoCmd.TransferText acImportDelim, _
"BM_ASCIIimport", "tblImport_temp", strInputFileName
strSQLupd = "UPDATE tblImport_temp, PtblBenchmark SET
PtblBenchmark.BMNum = [tblImport_temp].[BMNum], PtblBenchmark.Northing =
[tblImport_temp].[Northing], PtblBenchmark.Easting =
[tblImport_temp].[Easting], PtblBenchmark.ElevGPS =
[tblImport_temp].[ElevGPS], PtblBenchmark.Remarks = [PtblBenchmark].[Remarks]
& "" "" & [tblImport_temp].[Remarks], PtblBenchmark.GPSDate = Now()" _
& "WHERE (((PtblBenchmark.BMNum)=[tblImport_temp].[BMNum]));"

CurrentDb.Execute strSQLupd, dbFailOnError
Me.Dirty = False
Me.Refresh

MsgBox "Data file has been imported.", , "Import Complete"
Else
MsgBox "File has already been imported!" & vbCrLf & "If this file
has been updated, " _
& vbCrLf & "please change the name of the file and try again.", ,
"Duplicate File Name"
End If

Hello all,
I have a button on a form that imports a comma delimited text file. It then
runs an update statement which concatenates data into a memo field. Is there
a way to prevent the same text file from being imported more than once? I
was thinking if I wrote the name of the file to a table, that would do the
trick or maybe some sort of like statement that looks at the memo field for a
certain number of matching characters.


Dim strSQLdel As String
Dim strSQLupd As String
Dim strFilter As String
Dim strInputFileName As String

strSQLdel = "DELETE tblImport_temp.*" & "FROM tblImport_temp;"

CurrentDb.Execute strSQLdel, dbFailOnError

strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
Dim strFile As String

DoCmd.TransferText acImportDelim, _
"BM_ASCIIimport", "tblImport_temp", strInputFileName

strSQLupd = "UPDATE tblImport_temp, PtblBenchmark SET PtblBenchmark.BMNum =
[tblImport_temp].[BMNum], PtblBenchmark.Northing =
[tblImport_temp].[Northing], PtblBenchmark.Easting =
[tblImport_temp].[Easting], PtblBenchmark.ElevGPS =
[tblImport_temp].[ElevGPS], PtblBenchmark.Remarks = [PtblBenchmark].[Remarks]
& "" "" & [tblImport_temp].[Remarks], PtblBenchmark.GPSDate = Now()" _
& "WHERE (((PtblBenchmark.BMNum)=[tblImport_temp].[BMNum]));"

CurrentDb.Execute strSQLupd, dbFailOnError
Me.Dirty = False
Me.Refresh

MsgBox "Data file has been imported.", , "Import Complete"

Thanks in advance,
Nick X