ADODB,  get the result of a 'Delete'

Posted on 2012-09-10
Last Modified: 2012-09-10
I have the following commad that works, but I would like to retrieve the results.

Can't I change the last line to:

result = oCmd.Execute

    Dim oConn As ADODB.Connection
    Dim oSqlCmd As ADODB.Command
    Dim oRec As New ADODB.Recordset

    Set oConn = New ADODB.Connection
    sConn = "Driver={SQL Server};" & _
           "Server=" & SrvIP & ";" & _
           "Database=" & AppNm & ";" & _
           "Uid=" & Uid & ";" & _
           "Pwd=" & Pwd & ""

    oConn.Open (sConn)
    Dim oCmd As New ADODB.Command  ' Create a new SqlCommand
    oCmd.ActiveConnection = oConn
    Dim sTxtCmd As String
    sTxtCmd = "Delete from myTbl Where Date != '2012/07/18'"
    oCmd.CommandTimeout = 0
    oCmd.CommandType = adCmdText
    oCmd.CommandText = sTxtCmd

Open in new window

Question by:sidwelle
    LVL 33

    Expert Comment

    Try this.
        oCmd.Execute result
        MsgBox result & " records deleted"

    Open in new window

    LVL 59

    Assisted Solution

    by:Kevin Cross
    Hi. What are you expecting as the result? With a DELETE, there is no recordset to return. You have to use the parameter RecordsAffected (IIRC). Depending on the version of SQL Server you are using, you can look into use OUTPUT (ref: to return the deleted rows.

    EDIT: I just saw imnorie's response, which should be a good example of the RecordsAffected parameter syntax and usage.

    Author Comment

    Imnorie, Thanks for the help.  That was too easy.

    Question: will the variable returned ever be anything than an integer ?
    Should 'records affected' be dimensioned as a Long or Variant ?

    LVL 33

    Accepted Solution

    As far as I know it always returns a number.

    It should be dimensioned as Long.

    Author Closing Comment

    Thanks for the help.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now