Delete ADODB Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
 
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.
 
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 said:
No worries. I figured it out... You need to delete each record in
the
recordset individually, before you update the batch...

Jim Thomlinson said:
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
Click to expand...
Click to expand...
 
Back
Top