Hi i have a recordset name rcdDNE. I read the rtn, accno, first name, Middle name, last name, amount, from text file and store it to the recordset. Now I want to store that values to database table. In my table accno is primary key. So before storing that into my table i want to find out if there is any duplicate accno in my recordset. If i have i want to write it to text file.
Can anyone help me.
' Set up rcdDNE structure
With rcdDNE.Fields
.Append "RTN", adVarChar, 9
.Append "AccountNbr", adVarChar, 17
.Append "IndividualName", adVarChar, 22
.Append "FirstName", adVarChar, 50
.Append "MiddleName", adVarChar, 1
.Append "LastName", adVarChar, 50
.Append "Amount", adCurrency
End With
rcdDNE.Open
intFileNbr = FreeFile(1)
Open strFileName For Input As #intFileNbr Len = 95 ' Open file for input.
Do While Not EOF(intFileNbr)
Line Input #intFileNbr, strCurrentLine
If Mid(strCurrentLine, 1, 1) = 6 Then
strRoutingNbr = Mid(strCurrentLine, 4, 8)
strAcct = Trim(Mid(strCurrentLine, 13, 17))
strIndividualName = Trim(Mid(strCurrentLine, 55, 22))
strAmount = Trim(Mid(strCurrentLine, 30, 10))
strAmount = Left(strAmount, Len(strAmount) - 1)
curAmount = CCur(strAmount)
' Add new record to temporary recordset
With rcdDNE
.AddNew
.Fields![RTN] = strRoutingNbr
.Fields![AccountNbr] = strAcct
.Fields![IndividualName] = strIndividualName
.Fields![Amount] = curAmount
.Update
End With
End If
Loop
' Write records to Database
frmDNELoad.lblStatus.Caption = "Loading data into database......"
Dim lngRecCount As Long
lngRecCount = 0
rcdDNE.MoveFirst
With cmdCommand
.ActiveConnection = objConn
.CommandText = "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNbr") & "', '" & rcdDNE("FirstName") & "', '" & rcdDNE("MiddleName") & "', '" & rcdDNE("LastName") & "', '" & rcdDNE("Amount") & "')"
.CommandType = adCmdText
End With
Set rcddnefrc = New ADODB.Recordset
With rcddnefrc
.ActiveConnection = objConn
.Source = "SELECT * FROM T_DATA_DNEFRC"
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
End With
Do Until rcdDNE.EOF
lngRecCount = lngRecCount + 1
frmDNELoad.lblStatus.Caption = "Adding record " & lngRecCount & " of " & rcdDNE.RecordCount & " to database."
frmDNELoad.Refresh
DoEvents
Call CommitNew
rcdDNE.MoveNext
Loop
Best Answer
In the loop, where reading the data from the text file, Build a list of accno.
Each time you read a line from the text, first check if the list contains the accno, if not, add the record, and add the accno to the list.
If it does contain the accno in the list already, dont add the line to the record set and move to the next line.