-
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
-
hmmmm
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
Code:
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
