How do you use .RecordsAffected

Posted on 2004-11-13
Last Modified: 2006-11-17
 Dim db As Database
  Set db = CurrentDb
   Dim queryDef As DAO.queryDef
   Set queryDef = db.CreateQueryDef("", SqlStr)
   debug.print queryDef.RecordsAffected -->gives zero  every time
  db.Execute (SqlStr), dbFailOnError --> the dbFailOnError seems to make the execute statement more picky about what it will let execute
  DaoExecuteSql = db.RecordsAffected --> gives back the number of records affected correctly

WTF is going on here?

Question by:tricks801
    1 Comment
    LVL 58

    Accepted Solution

    Hmm. RecordsAffected work with action queries. For example:

        With CurrentDb
            .Execute "DELETE * FROM tblClients"
            MsgBox "You deleted " & .RecordsAffected & " Clients!"
        End With

    In Conjuction with .BeginTrans and .CommitTrans / .RollBack, this allows messages in the form:

        12 records will be updated.
        Press [OK] to confirm or [Esc] to cancel

    It also allow to decide wheter a refresh is needed:

        If .RecordsAffected Then Me.subForm.Requery

    Finally, if you use dbFailOnError, it will not even attempt to run the query if there is any error. In the Delete example above, only clients who never placed an order would be deleted because of relational integrity. This is basically what you get when you run an action query from the database window...

    Hope this sheds some light :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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