Newbie question: deleting records from a recordset

[Kooda]

This should be a simple task, however I am getting this error everytime I attempt to delete the records chosen by the recordset:
Runtime error: "Object or provider is not capable of performing requested operation"

We are running a Sybase11 SQL server.

Here is the majority of my code:
---------------------------------
Dim conn As Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties=DSN=BHSI;SRVR=BHSI;DB=test;UID=zz;PWD=zztop;"
conn.Open
---------------------------------
SQLdelete.Open "(sql statement), , conn, , , adCmdText"

If Not SQLdelete.EOF Then
SQLdelete.Delete
End If
SQLdelete.Close
----------------------------------

THANKS for any help..

 

[shavon]

Do you need to use a recordset?

I would use this:

Dim sqlDelete as string
Dim conn As Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Extended Properties=DSN=BHSI;SRVR=BHSI;DB=test;UID=zz;PWD=zztop;"

if conn.state=adstateclosed then conn.Open

sqlDelete="Delete * From tblTable Where iD=123"

conn.Execute sqlDelete

msgbox "Records deleted!",vbinformation

if conn.state=adstateopen then conn.Close

 

[stoolpigeon]

It would appear that either the default recordset cursor type is not supporting deletes or it is on the server side.

If you print out the cursor type you can verify what it is. Or instead of going w/the default you could open it as a cursor type that you are positive is read/write- such as adOpenKeyset or adOpenDynamic maybe.

Frankly if all you want to do is the delete- then you could do it w/a DELETE query and save yourself some overhead. Read/Write recordsets are expensive.

And maybe having all your options for your recordset in quotes like this is o.k. but I've never done it that way. But I guess if it is opening it must work. Usually I just open a recordset w/the method followed by the parameters.

rs.Open cn,adOpenKeyset,adLockOptimistic,adCmtText

Like that- you get the idea I am sure.

Just a couple thoughts.

 

[Kooda]

Theologian~

Are you suggesting I simply assign a delete query to a variable, and then open the variable like so:
--------------------------------
SQLVar = Select * from table
--------------------------------
Recset.Open SQLVar,conn,adOpenKeyset,adLockOptimistic,adCmtText
----------------------------------------------------
thanks for the help everyone