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

Delete ADODB Recordset
 

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

Delete ADODB Recordset
 

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" wrote in
message ...
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