Re: Delete a single record instead of entire recordset

jans__@xxxxxxxxxxx wrote:
I would like to delete a single record, however the result of my
code
is that the complete recordset is deleted

Can anyone help me out here????

Here is my code


Sub DeleteAdvisor()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim OfferDB as String
dim advisorid as string

Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open OfferDB
End With

Set rst = New ADODB.Recordset

With rst
' Open the Recordset object.
advisorid = "12"

strSQL = "SELECT * FROM Qry_Invul_Adviseur WHERE Adviseur_id =
"
& advisorid
.Open Source:=strSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
End With

rst.MoveFirst
rst.Delete
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

I don't see anything here that would cause the entire recordset to be
deleted. How are you determining that this is what is happening?

However ...
You're retrieving all the fields for all the records where Adviseur_id
contains 12 just to delete a single record??? Not the most efficient
technique to be using ...

If Adviseur_id is the primary key of the source table, then there can only
be a single record in the table where Adviseur_id contains 12. So simply do
this:

strSQL = "DELETE * FROM Qry_Invul_Adviseur WHERE Adviseur_id =?"
set cmd=new adodb.command
set cmd.activeconnection=cnn
cmd.commandtype=adCmdText
cmd.Execute ,advisorid

If there could be multiple records where Adviseur_id contains 12, then post
back and let us know.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • VB IDE Collating Seq error
    ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ... Dim strSQL As String ...
    (microsoft.public.vb.database.ado)
  • Re: Find code errors
    ... Dim rst As ADODB.Recordset ... Dim strCriteria As String ... 'opens recordset to support all cursor and record movements ...
    (microsoft.public.access.macros)
  • Re: ADODB.Connection run-time error -2147467259 (80004005)
    ... recordsets but the connection error still occurred. ... recordset in the procedure below. ... Dim cnn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADODB.Connection run-time error -2147467259 (80004005)
    ... VBA was connected to one of these tables then the connection failed. ... one name for all of the recordset objects without any problem. ... Dim cnn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: using LIKE operator in SQL
    ... Dim rst As Recordset ... Dim rst As DAO.Recordset ... it's lower in precedence than ADO. ... There's a Recordset object in both the ADO and DAO models. ...
    (microsoft.public.access.queries)