Retrieve record count

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • BSB
    New Member
    • Jul 2007
    • 19

    Retrieve record count

    Hi I use VB 6.o as my front end... execute a query in Access... by the following code

    sQueryName = "[Warranty Update 12 Month Rolling #2]"

    adocmd.CommandT ext = sQueryName
    adocmd.CommandT ype = adCmdStoredProc
    adocmd.ActiveCo nnection = conAdo
    adocmd.CommandT imeout = 1200
    adocmd.Execute

    I wanted to retrieve the record count that is affected ....

    can anyone suggest me the syntax
  • MikeTheBike
    Recognized Expert Contributor
    • Jun 2007
    • 640

    #2
    Originally posted by BSB
    Hi I use VB 6.o as my front end... execute a query in Access... by the following code

    sQueryName = "[Warranty Update 12 Month Rolling #2]"

    adocmd.CommandT ext = sQueryName
    adocmd.CommandT ype = adCmdStoredProc
    adocmd.ActiveCo nnection = conAdo
    adocmd.CommandT imeout = 1200
    adocmd.Execute

    I wanted to retrieve the record count that is affected ....

    can anyone suggest me the syntax
    Hi

    How about

    Dim RecordNum as Long
    .
    .
    .
    adocmd.Execute RecordNum

    msgbox RecordNum

    ??

    MTB

    Comment

    • BSB
      New Member
      • Jul 2007
      • 19

      #3
      Originally posted by MikeTheBike
      Hi

      How about

      Dim RecordNum as Long
      .
      .
      .
      adocmd.Execute RecordNum

      msgbox RecordNum

      ??

      MTB
      \]



      hey thank you..it works

      Comment

      • BSB
        New Member
        • Jul 2007
        • 19

        #4
        Originally posted by BSB
        \]



        hey thank you..it works

        One more doubt,

        the query that was executed is the find duplicate query
        say if the count retrieved is 10...
        I need to select all those 10 records
        How to do that????

        Comment

        • Killer42
          Recognized Expert Expert
          • Oct 2006
          • 8429

          #5
          Originally posted by BSB
          the query that was executed is the find duplicate query
          say if the count retrieved is 10...
          I need to select all those 10 records
          If you have a query which returned a bunch of records, and you want to delete them, I believe there are two basic approaches. You can use a modified copy of the query to delete them instead of selecting them (change the "select query" to a "delete query"), or your code can just loop through all the records and delete them, using the .Delete method.

          Comment

          • MikeTheBike
            Recognized Expert Contributor
            • Jun 2007
            • 640

            #6
            Originally posted by BSB
            One more doubt,

            the query that was executed is the find duplicate query
            say if the count retrieved is 10...
            I need to select all those 10 records
            How to do that????
            Hi

            I don't normally use the ADO Command objects but perhaps this will do it

            sQueryName = "[Warranty Update 12 Month Rolling #2]"

            adocmd.CommandT ext = sQueryName
            adocmd.CommandT ype = adCmdStoredProc
            adocmd.ActiveCo nnection = conAdo
            adocmd.CommandT imeout = 1200


            Dim rs as ADODB.Recordset
            Set rs = adocmd.Execute( )


            This of course asumes the "[Warranty Update 12 Month Rolling #2]"
            is a select query and conADO is an opened connection.

            My normal method would be (on the above basis).

            Set rs = New ADODB.Recordset
            rs.Open sQueryName, conAdo

            or similar

            Is that what you had in mind ?

            MTB

            Comment

            • Killer42
              Recognized Expert Expert
              • Oct 2006
              • 8429

              #7
              Oops!

              Sorry about that. I don't know where I got the idea that you wanted to delete them. :(

              Comment

              Working...