Delete all records in ADO recordset

[msc0tt]

I have an ADO recordset:
rs.source = "select * from myTable where myField = 'X'"
rs.open

I wish to delete ALL the records in this record set. I know I could loop through all them, but I'd rather do it in one fell swoop. I have an ADO book that mentions using the adAffectAll parameter to the .Delete method, but VB6 doesn't appear to support this value (only adAffectCurrent, adAffectGroup, and adAffectAllChapters). Ideas?

 

[hmckillop]

WHy dont you create a command object and just execute a delete for the same where clause
e.g.

DIM cmd1 as NEW ADODB.COmmand
set cmd1.ActiveCOnnection = myConnection 'open a connection object called myconnection to your db
cmd1.commandtext = "Delete myTable where myField = 'X'"
cmd1.execute

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[msc0tt]

Thanks, yes. This is 'Plan B'. My code already has the recordset open for additional purposes. What it needs to do at a certain point is purge the entire table, and repopulate it.
Here's a thought: What if I delete all the records in a table with a command object, and the table is already being referred to by an open recordset? Will the recordset instantly read empty? or would I need to close/reopen it?
Note: the record set is opened with
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic

-thanks

 

[hmckillop]

It would depend on the database type and how it manages locks, along with what operations you are doing on the recordset.
I would guess it would lock any data that is being changed by the recordset - keyset implies it only cares about the initial set of keys captured at opening of the recordset i.e. ignores new values etc., and with locktype optimistic you would still be permitted access to the data. I would think if you had changed any of the data using the recordset you wouldnt be able to delete.
I will test this with ADO and SQL Server and repost tonight.

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[ESquared]

For what it's worth, this seems more like an ADO/VB question than an SQL server one... ???

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[msc0tt]

ESquared,
Many of the forums here overlap -it is unavoidable. I write programs to manage Microsoft SQL databases (as in this case). As you are no doubt aware, this forum addresses "Microsoft SQL: Programming". I don't think I'm out of line here.
I do post in the general VB forum, but if my post is specific to ADO and SQL, I stand a much greater chance of a helpful reply here.
-Mike

 

[NoCoolHandle]

I think that the affects all requires you place a filter on the recordset..

i.e. rs.filter = "myField = 'X'"

then do the rs.delete (with all the parameters)

 

[ESquared]

It wasn't so much whether you were out of line, just how to get the best help.

-------------------------------------
A sacrifice is harder when no one knows you've made it.