Unable to delete all records from DB with ADO

Hello,

I'm trying to delete records from the database with ADO without any success. The deletion process starts fine but then I get a error
message "key column information is insufficient or in correct. To manyrows were affect by update." The are approx 34000 records and when I use DAO, all works fine. Any help would be much appreciated.

Thanks

Karl

Private Sub sbCleanup()

Dim strTemp As String
Dim strTaskOper As String
Dim strMsg As String
Dim strCurrentRcrd As String
Dim nRecCount As Long
Dim nCounter As Long
Dim nIndex As Long

On Error GoTo CleanupError

strMsg = "This routine is used to delete any " _
& "duplicated data for the " _
& "OPER and TASK number fields. " & vbCr _
& "Click OK to continue or " _
& "Cancel to Quit."

If MsgBox(strMsg, vbOKCancel, "CleanUp") = vbCancel Then
Exit Sub
End If

Adodc1.RecordSource = "SELECT * FROM " & strTableName & _
" ORDER BY OPER_NBR, TASK_NBR ASC"
Adodc1.Refresh


DataGrid1.Visible = False
ProgressBar1.Visible = True

Call navigateButtons("00000000")

Screen.MousePointer = vbHourglass

With Adodc1.Recordset
.MoveLast
nRecCount = .RecordCount
.MoveFirst
strTemp = !OPER_NBR & !TASK_NBR
.MoveNext
ProgressBar1.Min = 0
ProgressBar1.Max = .RecordCount
For nCounter = 1 To nRecCount - 1
strCurrentRcrd = (!OPER_NBR & !TASK_NBR)
If (strTemp = strCurrentRcrd) Then
.Delete
Else
strTemp = strCurrentRcrd
End If
.MoveNext
nIndex = nIndex + 1
ProgressBar1.Value = nIndex
Next nCounter
lblRecordCount = .RecordCount
.MoveFirst
End With
ProgressBar1.Visible = False
ProgressBar1.Value = 0
DataGrid1.Visible = True
Screen.MousePointer = vbDefault
DoEvents
Call sbRecords
Call ShowPosition

Exit Sub
CleanupError:
Call sbErrorDesc
DataGrid1.Visible = True
Exit Sub
End Sub


Comments

  • Hi

    I am not sure if this is the answer you want.
    Rather than looping thru all the records, use a delete query instead.
    It is very quick. You won't need a progress bar. It is also much
    easier. e.g. "delete from Table1" will delete all the records in table1.
    It is clean and very effcient. Just display an hourglass while it's
    deleting the records. It shouldn't take more than 2 seconds or so to delete the records.
    I'm not sure, but try the following:


    Adodc1.RecordSource = "DELETE FROM " & strTableName
    Adodc1.Refresh

    Heine





    : Hello,
    :
    : I'm trying to delete records from the database with ADO without any success. The deletion process starts fine but then I get a error
    : message "key column information is insufficient or in correct. To manyrows were affect by update." The are approx 34000 records and when I use DAO, all works fine. Any help would be much appreciated.
    :
    : Thanks
    :
    : Karl
    :
    : Private Sub sbCleanup()
    :
    : Dim strTemp As String
    : Dim strTaskOper As String
    : Dim strMsg As String
    : Dim strCurrentRcrd As String
    : Dim nRecCount As Long
    : Dim nCounter As Long
    : Dim nIndex As Long
    :
    : On Error GoTo CleanupError
    :
    : strMsg = "This routine is used to delete any " _
    : & "duplicated data for the " _
    : & "OPER and TASK number fields. " & vbCr _
    : & "Click OK to continue or " _
    : & "Cancel to Quit."
    :
    : If MsgBox(strMsg, vbOKCancel, "CleanUp") = vbCancel Then
    : Exit Sub
    : End If
    :
    : Adodc1.RecordSource = "SELECT * FROM " & strTableName & _
    : " ORDER BY OPER_NBR, TASK_NBR ASC"
    : Adodc1.Refresh
    :
    :
    : DataGrid1.Visible = False
    : ProgressBar1.Visible = True
    :
    : Call navigateButtons("00000000")
    :
    : Screen.MousePointer = vbHourglass
    :
    : With Adodc1.Recordset
    : .MoveLast
    : nRecCount = .RecordCount
    : .MoveFirst
    : strTemp = !OPER_NBR & !TASK_NBR
    : .MoveNext
    : ProgressBar1.Min = 0
    : ProgressBar1.Max = .RecordCount
    : For nCounter = 1 To nRecCount - 1
    : strCurrentRcrd = (!OPER_NBR & !TASK_NBR)
    : If (strTemp = strCurrentRcrd) Then
    : .Delete
    : Else
    : strTemp = strCurrentRcrd
    : End If
    : .MoveNext
    : nIndex = nIndex + 1
    : ProgressBar1.Value = nIndex
    : Next nCounter
    : lblRecordCount = .RecordCount
    : .MoveFirst
    : End With
    : ProgressBar1.Visible = False
    : ProgressBar1.Value = 0
    : DataGrid1.Visible = True
    : Screen.MousePointer = vbDefault
    : DoEvents
    : Call sbRecords
    : Call ShowPosition
    :
    : Exit Sub
    : CleanupError:
    : Call sbErrorDesc
    : DataGrid1.Visible = True
    : Exit Sub
    : End Sub
    :
    :
    :

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion