Good morning.
I am stumbling through trying to make a data import module work. After finally getting the data to import, I see duplicates. The text file I am importing has two unique factors ... transaction date and reference number. So I thought by making these the primary keys, I would only get what is the on the text file.
No can do. Now I get the 3022 'The changes you requested to the table ....'
So I have two questions:
1.) Why is my code (below) bringing in only some records more than once?
2.) Will/does setting a primary key on the table prevent duplicates from being imported by giving you an error message, or should it just keep the first record and ignore the duplicate?
Thanks for your help.
I am stumbling through trying to make a data import module work. After finally getting the data to import, I see duplicates. The text file I am importing has two unique factors ... transaction date and reference number. So I thought by making these the primary keys, I would only get what is the on the text file.
No can do. Now I get the 3022 'The changes you requested to the table ....'
So I have two questions:
1.) Why is my code (below) bringing in only some records more than once?
2.) Will/does setting a primary key on the table prevent duplicates from being imported by giving you an error message, or should it just keep the first record and ignore the duplicate?
Thanks for your help.
Code:
Option Compare Database
Option Explicit
Public Const cDailyActivityReport As String = "T:\Visa\Visa Month End\Daily Activity Data\CD021Data.txt"
Public Const cTITLE2 As String = "Daily Activity Report"
Public Function ImportEFile()
Dim myCheck
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strInFile As String, intInFile As Integer, strFileData As String
Dim datReport As Date
Dim strDate As Date
Dim strCardnumber As String
Dim strTranCode As String
Dim strAmount As String
Dim strReferenceNumber As String
Dim strTransactionDate As String
Dim strFT As String
On Error GoTo ImportFile_Err
strInFile = cDailyActivityReport
DoEvents
myCheck = MsgBox("Are you sure you want to import Visa Credit Card Daily Activity?", vbYesNo)
If myCheck = vbYes Then
'Initialize DAO objects
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CD 021 Data", dbOpenDynaset)
intInFile = FreeFile
Open strInFile For Input As intInFile
SysCmd acSysCmdInitMeter, "Importing Data File", LOF(intInFile)
Do Until EOF(intInFile)
SysCmd acSysCmdUpdateMeter, (Loc(intInFile) * 128)
Line Input #intInFile, strFileData
If StrComp(Mid(strFileData, 55, 8), "MONETARY", vbBinaryCompare) = 0 Then
datReport = Mid(strFileData, 111, 8)
strDate = datReport
End If
If StrComp(Mid(strFileData, 56, 8), "MONETARY", vbBinaryCompare) = 0 Then
datReport = Mid(strFileData, 112, 8)
strDate = datReport
End If
If StrComp(Mid(strFileData, 8, 4), "XXX") = 0 Then 'Or StrComp(Mid(strFileData, 16, 3), " B ") = 0 Then
strCardnumber = Mid(strFileData, 8, 16)
strTranCode = Mid(strFileData, 29, 3)
strAmount = Mid(strFileData, 35, 15)
strReferenceNumber = Mid(strFileData, 52, 17)
strTransactionDate = Mid(strFileData, 71, 6)
strFT = Mid(strFileData, 79, 2)
End If
With rst
.AddNew
!Date = strDate
!CardNumber = strCardnumber
!TranCode = strTranCode
!Amount = strAmount
!ReferenceNumber = strReferenceNumber
!TransactionDate = strTransactionDate
!FT = strFT
.Update
End With
' End If
Loop
Else: MsgBox "Visa Daily Data Import cancelled", vbOKOnly
End If
rst.Close
dbs.Close
MsgBox "Visa Daily Credit Card Activity Import Complete"
ImportFile_Exit:
SysCmd acSysCmdClearStatus
Close intInFile
Set dbs = Nothing
Set rst = Nothing
'Set cd = Nothing
Exit Function
ImportFile_Err:
'MsgBox Err & " " & Err.Description & vbLf & "Job Name: ImportFile" & vbLf & cSUPPORT, vbCritical
MsgBox Err & " " & Err.Description & vbLf & "Job Name: ImportFile" & vbLf & vbCritical
Resume ImportFile_Exit
End Function
Last edited by a moderator: