Deleting From a Join Table With an ADODB.Recordset

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
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

technology leadership

Why the World Needs More Technology Leadership

As a fact, technology has touched every single aspect of our lives. And there are some technology giants in today’s world which have been frequently opined to have a strong influence on recent overall technological influence. Moreover, those tech giants have popular technology leaders leading the companies toward achieving greatness.

iOS app development

The Future of iOS App Development: Trends to Watch

When it launched in 2008, the Apple App Store only had 500 apps available. By the first quarter of 2022, the store had about 2.18 million iOS-exclusive apps. Average monthly app releases for the platform reached 34,000 in the first half of 2022, indicating rapid growth in iOS app development.

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job