Primary Key & Duplicates

[Moxioron]

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.

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

 

[xenou]

No can do. Now I get the 3022 'The changes you requested to the table ....'

This message is saying "I can't do this because of duplicates". Isn't that what you want? Are you getting this message when you run the import or when you create the composite primary key? You can't actually even create that composite key if the table already has duplicates in it - you would have to remove them first.

 

[xenou]

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?

Note that the answer to this question varies depending on how you go about your import. In my experience you generally get an error when you try to insert records with recordset object methods, but I'm not 100% because of habit I always use the DoCmd.RunSQL method when I want to insert records that I know might be rejected because of unique keys.

This might work for your current code (assuming we don't change it drastically - honestly it feels a little kludgy to me but I guess it will work):

With rst
            
    .AddNew
    !Date = strDate
    !CardNumber = strCardnumber
    !TranCode = strTranCode
    !Amount = strAmount
    !ReferenceNumber = strReferenceNumber
    !TransactionDate = strTransactionDate
    !FT = strFT
		
    '//Set Error Handling To "Resume Next" For Insert Operation
    On Error Resume Next
    .Update
    If Err Then
        If Err.Number = 3022 Then
            '//Ignore the dupe and move on to the next record
            .CancelUpdate 
        Else
            '//Some other problem occurred - abort
            Msgbox "Error Inserting Record: " & Err.Number & " " & Err.Description
	    .CancelUpdate
	    .Close
	    GoTo ImportFile_Exit
        End If
    End If
    '//Restore Error Handling
    On Error Goto ImportFile_Err

End With

For what it's worth I usually go a more roundabout way by first import all records into a staging table (that doesn't have any indexes and therefore accepts all the records). Then I push them to my final table with a DoCmd object method to run SQL or an append query, which will silently discard the dupes.