PDA

Click to See Complete Forum and Search --> : ADO Duplicate error in Access


Gary.Lowe
May 22nd, 2000, 06:39 PM
Dear Whoever

Can you help

I am taking two fields from a text file (a date 22/05/2000)
and a ID number(Will be the primary key in the table)

the problem is that the text file contains duplicate of these records sometimes not directly after each other.
this causes the -2147467259 error which i have trapped and tried to clear, rollback and goto the next line in the file.

the problem is once it has hit this error whether the next sGAR(ID) is duplicate or not it continually hits this error.

I have included the code I have written to import this file.

Public Function ImportFile()
On Error GoTo ImportErr

Dim dFDate As Date
Dim sGAR As String
Dim i As Integer
Dim sAll As String
Open "c:\text.txt" For Input As #1

For i = 1 To 1
Line Input #1, sAll
Next

Set rstImport = New ADODB.Recordset
rstImport.CursorType = adOpenStatic
rstImport.LockType = adLockOptimistic
rstImport.Open "tblImport", cnnMIS, , , adCmdTable


Do While Not EOF(1)
cnnMIS.BeginTrans
Line Input #1, sAll

dFDate = Mid(sAll, 8, 9)
sGAR = Trim(Mid(sAll, 87, 13))

If sGAROld <> sGAR Then
rstImport.AddNew Array("Date", "Account Ref"), _
Array(dFDate, sGAR)
rstImport.Update
cnnMIS.CommitTrans
Else
cnnMIS.RollbackTrans
End If
sGAROld = sGAR

NextItem:
Loop

Close #1

ImportErr:
Select Case Err
Case -2147467259
cnnMIS.Errors.Clear
cnnMIS.Errors.Refresh
cnnMIS.RollbackTrans
Resume NextItem
Case Err
msgbox Err.Number & " : " & Err.Description
exit function
End Select

End Function

mattbrown
May 24th, 2000, 10:36 AM
I'm not sure the exact specifications that you are running your database to but the RollbackTrans and CommitTrans are specifically for if you want to use transactions.

I had this problem once although I was not using transactions. When the DB threw up an error because of the duplicate record i used to following code to handle it


dupErrHndl:
rstImported.CancelUpdate 'Don't update the recordset
Err.Clear 'Clear the application level error
Resume Next


That code uses an ADODB.Recordset object for rstImported
The underlying db engine is MS.Jet 4.0

Not sure if this helps or not

Regards