Delete ADODB Recordset

I am trying to delete an ADODB recordset. Here is the code that I am using...

strSelect = "SELECT *"
strFrom = "FROM tblVehicleDetails"
strWhere = "WHERE ((tblVehicleDetails.[Unit Number])=" & lngUnitNumber &
")"
strOrderBy = ";"

Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True)

rst.Delete adAffectCurrent

rst.UpdateBatch
rst.Close
Set rst = Nothing

The runquery function works and returns a connected recordset. There is no
error when the code runs but the records are not deleted. I have no trouble
deleting the records manually in the databse. Any thoughts...

TIA

Jim Thomlinson

No worries. I figured it out... You need to delete each record in the
recordset individually, before you update the batch...

"Jim Thomlinson" wrote:

> I am trying to delete an ADODB recordset. Here is the code that I am using...
>
> strSelect = "SELECT *"
> strFrom = "FROM tblVehicleDetails"
> strWhere = "WHERE ((tblVehicleDetails.[Unit Number])=" & lngUnitNumber &
> ")"
> strOrderBy = ";"
>
> Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True)
>
> rst.Delete adAffectCurrent
>
> rst.UpdateBatch
> rst.Close
> Set rst = Nothing
>
> The runquery function works and returns a connected recordset. There is no
> error when the code runs but the records are not deleted. I have no trouble
> deleting the records manually in the databse. Any thoughts...
>
> TIA
>
> Jim Thomlinson

I don't completely follow, since I can't see some of the important details
(what is your data source/connection?) RunQuery is not a standard ADODB
method that I know of so there must be code there that could be important.
It is a function call (no?) that returns a recordset? But is that recordset
still connected to the source database? If you are getting a disconnected
recordset it would explain why the deleted records are not deleted in the
source data, since you would only be deleting them from the disconnected copy
of the recordset, no longer linked to the source.


"Jim Thomlinson" wrote:

> I am trying to delete an ADODB recordset. Here is the code that I am using...
>
> strSelect = "SELECT *"
> strFrom = "FROM tblVehicleDetails"
> strWhere = "WHERE ((tblVehicleDetails.[Unit Number])=" & lngUnitNumber &
> ")"
> strOrderBy = ";"
>
> Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True)
>
> rst.Delete adAffectCurrent
>
> rst.UpdateBatch
> rst.Close
> Set rst = Nothing
>
> The runquery function works and returns a connected recordset. There is no
> error when the code runs but the records are not deleted. I have no trouble
> deleting the records manually in the databse. Any thoughts...
>
> TIA
>
> Jim Thomlinson

Your best bet would be to skip the recordset altogether and just run a
delete SQL directly on the database. There's no need for a recordset
here.

You can see how many records were deleted using

Connection.Execute [SQLStatement], [RecordsAffected], [Options]

"RecordsAffected" will now contain the number of records affected
(deleted, in this case)

EG:

Dim strSQL As String
Dim lngRecs As Long
strSQL = "UPDATE Titles SET Price = Price * 1.10" & _
" WHERE Type = 'Business'"
conPubs.Execute strSQL, lngRecs, adCmdText
Response.Write lngRecs & " records were updated."



Tim.

"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in
message news:78826177-C1DD-4BD9-8A5C-FC1D49E4F520@microsoft.com...
> No worries. I figured it out... You need to delete each record in
> the
> recordset individually, before you update the batch...
>
> "Jim Thomlinson" wrote:
>
>> I am trying to delete an ADODB recordset. Here is the code that I
>> am using...
>>
>> strSelect = "SELECT *"
>> strFrom = "FROM tblVehicleDetails"
>> strWhere = "WHERE ((tblVehicleDetails.[Unit Number])=" &
>> lngUnitNumber &
>> ")"
>> strOrderBy = ";"
>>
>> Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy,
>> True)
>>
>> rst.Delete adAffectCurrent
>>
>> rst.UpdateBatch
>> rst.Close
>> Set rst = Nothing
>>
>> The runquery function works and returns a connected recordset.
>> There is no
>> error when the code runs but the records are not deleted. I have no
>> trouble
>> deleting the records manually in the databse. Any thoughts...
>>
>> TIA
>>
>> Jim Thomlinson