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
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