sql RS.delete event help (error runtime 214746259)(RESOLVED)

[ksuwanto8ksd]

I use ADO to access SQL table as database and I have problem when objRS.delete is access. This command is to remove all record in the table.

"Error run time 214746259(80004005)
Key column information is insufficent or incorrect.Too many row were infected by update"

when use in Access table is run smootly.I dont know what's up here. If any ever experiences and know exactly what's up here can give comment is greatly appreciated.thanks in advance

Dim objConnection As ADODB.Connection
Dim objRS As ADODB.Recordset
 
Private Sub Command3_Click()
 
If objRS.RecordCount = 0 Then
    MsgBox "OUW. NO RECORD!"
    Exit Sub
Else
objRS.MoveFirst
 
Do While Not objRS.EOF
    objRS.MoveFirst
    [B]objRS.Delete[/B]
    objRS.Update
    
    objRS.MoveNext
Loop
    MsgBox "Finish,Hm"
End If
End Sub

Best Regards
Kamus

[dee-u]

The code below should do it....

objConnection.Execute "DELETE * FROM TableName"

[ksuwanto8ksd]

hi dee-u

run time error 2147217900
incorect syntax near '*'

[dee-u]

TableName is the name of your table.... Are you using SQL Server?

objConnection.Execute "DELETE FROM TableName"

[ksuwanto8ksd]

"run time error 2147217864980040e38)
Row cannot be located for updating.Some value may have been changed since it was last read"

error line "objRS.delete"

[Pradeep1210]

"run time error 2147217864980040e38)
Row cannot be located for updating.Some value may have been changed since it was last read"

error line "objRS.delete"

Replace your Command3 event procedure with this two lines of code.

Private Sub Command3_Click()
    ''Assuming Table1 is the name of table to which objRS is connected
    objConnection.Execute "DELETE FROM Table1"
    objRS.Requery
End Sub

Pradeep

[ksuwanto8ksd]

ouw , nice .run smootly ,thanks

[ksuwanto8ksd]

hi Pradeep

What objRS.Requery is doing here? extra regards

best regards

[dee-u]

I think this should suffice even without the requery...

Private Sub Command3_Click()
    ''Assuming Table1 is the name of table to which objRS is connected
    objConnection.Execute "DELETE FROM Table1"
End Sub

[Pradeep1210]

I think this should suffice even without the requery...

Private Sub Command3_Click()
    ''Assuming Table1 is the name of table to which objRS is connected
    objConnection.Execute "DELETE FROM Table1"
End Sub

Sorry, but It won't,
The requery statement will clear the recordset.
Otherwise,
The records will still be in the recordset, though deleted from database. So whenever he tries to access them again, he will get an error.
The error which he is getting is just due to the reason that some records have already been deleted/modified from some other part of the app and so it can't be deleted here again. They are already not in the database.

Pradeep

[dee-u]

Hmmmmnnnnnn......

Will this cause an error?

Private Sub Command3_Click()
Dim objRS As ADODB.Recordset
''Assuming Table1 is the name of table to which objRS is connected
objConnection.Execute "DELETE FROM Table1"
set objRS = New ADODB.Recordset
objRS.Open "SELECT * FROM Table1",objConnection
End Sub

I dont have vb in this computer right now to check it but I have not used .Requery when I delete from a database.... And isnt it that objConnection is a Connection object and not a Recordset object, therefore whats the use of requery in it?

[ksuwanto8ksd]

Yes it clear the reordset. and when I use if ...else event it realy help

Thanks for all response

[ksuwanto8ksd]

dee-u your code do delete recordset from table and database too