Question:
How do I delete from an ADODB.Recordset that is created with a join expression and a where statement to minimize the result from the SQL query? I am using Visual Basic 6 and SQL Server 6.5 and I want to use the recordset delete function.

Answer:
You have to know a bit about ADO cursors and ADO locks and about what each type supports. The default combination of lock and cursor does not allow the action you described. It is easy to change the lock and cursor type from the default types to ones that better suit your needs. For example, you can change the cursor to dynamic cursor to allow most any action. Be careful with the dynamic cursor, it is the most resource intensive. This ADO code will accomplish what you want to do. Notice that the SQL statement uses both JOIN and WHERE clauses.

Private Sub DeleteRecord()    Dim RS     Set RS = server.CreateObject("ADODB.Recordset")    Dim strSQL    strSQL = "SELECT authors.au_lname, authors.au_fname,titleauthor.royaltyper FROM titleauthor INNER JOIN authors ONtitleauthor.au_id = authors.au_id WHERE (titleauthor.royaltyper> 50)"        Dim objConnectionPubs    Set objConnectionPubs = Server.CreateObject ("ADODB.Connection")        ' Establish a connection    With objConnectionPubs        .Provider = "SQLOLEDB"        .ConnectionString = "User ID=sa;" & _                   "Data Source=(local);" & _                   "Initial Catalog=Pubs"        .Open    End With'	THE FOLLOWING LINE OF CODE CORRESPONDS TO:'    RS.Open strSQL, objConnectionPubs, adOpenDynamic,adLockOptimistic    RS.Open strSQL, objConnectionPubs, 2, 3        RS.Delete    RS.MoveNext    End Sub