Results 1 to 12 of 12
  1. #1
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15

    Quick Module Question: :)

    My module updates a few values in my tables. Everytime it is going to update it, it says "You are about to update # Row(s)" and asks me Yes/No. Eventually this database will have tons of records, and it'll take forever for me to hit YES all these times. How can I disable this warning and have it just go ahead and update all the records?



    Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to see your code. If you're using DoCmd.RunSQL, you can use DoCmd.SetWarnings before and after, though using CurrentDb.Execute is considered to be more efficient that RunSQL and it doesn't throw the warnings.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    It would help to see your code. If you're using DoCmd.RunSQL, you can use DoCmd.SetWarnings before and after, though using CurrentDb.Execute is considered to be more efficient that RunSQL and it doesn't throw the warnings.
    Here is the line of code I used to update my fields:

    DoCmd.RunSQL ("update [seasons by module] set [profit after breakpoint] = " & postbp & " where [Module] = '" & mod_val & "' ;")

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    So did either of the two methods I suggested help?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    So did either of the two methods I suggested help?
    Yes, I did DoCmd.SetWarnings False at the beginning of my sub and it all works perfectly

    Thank you!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I hope you paid attention when I said "before and after", else you might notice some unintended side effects:

    http://www.baldyweb.com/SQLWarnings.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    I hope you paid attention when I said "before and after", else you might notice some unintended side effects:

    http://www.baldyweb.com/SQLWarnings.htm

    Thank you, I added in DoCmd.SetWarnings True before my sub ends. I noticed you said CurrentDb.Execute is more efficient. I should only be updating about 100-300 values, I don't think I need to be worried about efficiency, but for future knowledge does it look like I could do what I need done with CurrentDb.Execute?

    DoCmd.RunSQL ("update [seasons by module] set [profit after breakpoint] = " & postbp & " where [Module] = '" & mod_val & "' ;")
    In this I am updating the profit after breakpoint field where the module in its row has a specific value based on the variable mod_val. This way it only updates rows with that module value.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, Execute should also work there. The only time I think the Execute method can't be used is if there's a form reference inside the SQL (like in a saved query).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15
    So would my code be:

    CurrentDB.Execute ("update [seasons by module] set [profit after breakpoint] = " & postbp & " where [Module] = '" & mod_val & "' ;")

    ?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it works, yes...if not, no.

    I wouldn't bother with the parentheses, but they probably don't hurt anything.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    oregoncrete is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    15
    Quote Originally Posted by pbaldy View Post
    If it works, yes...if not, no.

    I wouldn't bother with the parentheses, but they probably don't hurt anything.
    it seems to work, but why is this considered more efficient?

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    They operate at different levels; Execute works directly with JET. Here's an explanation from MVP Rick Brandt:

    There is the Jet database engine which can be interacted with from code using DAO or ADO object libraries and then there is the "Access" interface which can also be manipulated from code.

    The execute method is a DAO method of the database object and it interacts
    directly with the Jet engine. Anything that starts with "DoCmd" is part of the Access interface.

    Generally, the Execute method is better. No "are you sure" prompts and you
    can trap for errors. On the other hand RunSQL can invoke the expression service so it can resolve things like "Forms!NameOfForm!NameOfControl" where the Execute method cannot. That limitation of Execute is easily worked around though by taking the reference out of the SQL quoted string so VBA can evaluate it...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Quick SQL Query question.
    By Suoipoc in forum Access
    Replies: 4
    Last Post: 09-06-2011, 09:10 PM
  2. Quick question about deleting records
    By pinecrest515 in forum Queries
    Replies: 3
    Last Post: 12-14-2010, 10:58 AM
  3. tabbed forms .. QUICK question!
    By kroenc17 in forum Forms
    Replies: 2
    Last Post: 12-01-2010, 08:38 AM
  4. Quick Question
    By Imgsolutions in forum Access
    Replies: 3
    Last Post: 07-19-2010, 11:22 AM
  5. Replies: 2
    Last Post: 06-30-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums