Deleting From a Join Table With an ADODB.Recordset

Deleting From a Join Table With an ADODB.Recordset

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.

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
Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes