ADO Duplicate error in Access

[Gary.Lowe]

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]

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