Results 1 to 13 of 13
  1. #1
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68

    Update & Append queries - Macro question

    Hi,



    I have a couple of Append Queries and some Update queries that I have put into a Macro to run one after the other.

    Is there a way to make these queries run without the pop up boxes that advise how many records are being appended/updated etc? Is there a way to run this silently without someone having to keep pushing 'yes'??

    TIA

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes. In your Macro, before those steps, add the "SetWarnings" Action and set the Argument to "No".
    Then, at the very end of your Macro, add another "SetWarnings" Action and set the Argument to "Yes".

  3. #3
    euanl is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Posts
    25
    You can do it programatically (from the 'on click' event of button on a form for instance) if you want also:

    Dim stdocname As String

    'disable the warnings
    DoCmd.SetWarnings False

    'run the macro
    stdocname = "your_macro_name"
    DoCmd.RunMacro stdocname

    'Always reset whatever you unset, so re-enable the warnings
    DoCmd.SetWarnings true

  4. #4
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Thanks heaps for your help guys!

    I used the code for the On Click event, because it doesn't seem to give me the Action 'SetWarnings' in the Macro design in 2010. (unless I am completely blind and missed it all together)
    But the code is working great!

    Thanks again

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Some Actions may be hidden by default (another one of Microsoft's security measures).
    If you click on the "Show All Actions" button on the Show/Hide menu, you will see it.

  6. #6
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Ahhh yep, that would be what it is! It came up when I selected that. Thanks!

    With the code, I used from above, how do I make the query read only when it brings back the results?
    Even though the Queries that are in my Macro are set to 'Read Only' with the On Click event, when the query results open it still lets me go into Design View and save changes that I make?

  7. #7
    euanl is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Posts
    25
    You pretty much can't lock down the design view of queries without a lot of tinkering and messing about with the programme. Just tell your users not to change stuff, and if they do they get electric shock treatment or something.

    You can however make the queries themselves read only through code by just opening each query in turn instead of using the macro:


    Dim stdocname As String

    'disable the warnings
    DoCmd.SetWarnings False

    'run the queries
    stdocname = "your_firstquery_name"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly

    stdocname = "your_secondquery_name"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly 'etc - you get the picture

    'Re-enable the warnings
    DoCmd.SetWarnings true

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Also note a great little tool that Access has. There is a button on one of the ribbons to "Convert Macros to Visual Basic". This is a great tool if you want to do the same thing in VBA that you did in a Macro, but don't know the exact VBA code.
    It is also a good starter for VBA if have something that you want to do, but Macros are quite dynamic enough. It gives you a good start on the VBA code you will need.

  9. #9
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Quote Originally Posted by euanl View Post
    You pretty much can't lock down the design view of queries without a lot of tinkering and messing about with the programme. Just tell your users not to change stuff, and if they do they get electric shock treatment or something.

    You can however make the queries themselves read only through code by just opening each query in turn instead of using the macro:


    Dim stdocname As String

    'disable the warnings
    DoCmd.SetWarnings False

    'run the queries
    stdocname = "your_firstquery_name"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly

    stdocname = "your_secondquery_name"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly 'etc - you get the picture

    'Re-enable the warnings
    DoCmd.SetWarnings true
    haha, I might try the electric shock approach thanks

    But I will maybe try you code you put so that I can make the queries read only.....

    But I have a few append & update queries, which update/append to a table, then I have a query which opens the results once the table has been updated by the append & update queries...

    So I just want the append & update queries to run in the background and close, dont want them to open, I just want the last query to open? can I do this?

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you "Open" action queries like Update and Append queries in VBA code or Macros, they do not actually open, they just run (try it, you'll see!).
    Only Select queries will actually open when you use the OpenQuery command.

    So you should be able to do what you want without any issues.

  11. #11
    noaccessguru is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    68
    Quote Originally Posted by JoeM View Post
    If you "Open" action queries like Update and Append queries in VBA code or Macros, they do not actually open, they just run (try it, you'll see!).
    Only Select queries will actually open when you use the OpenQuery command.

    So you should be able to do what you want without any issues.
    Thank you!

    I did do this and it works fine, but the final query that opens (which is what I want to open), still allows you to make a change to the query if you go into design view, even though I have it set to Read Only?

    Am I doing something wrong? This is my code below:


    Dim stdocname As String

    DoCmd.SetWarnings False

    stdocname = "Qry_AppendABP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendALL"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendBUH"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendCAP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendCTP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendMBG"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendWV"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_AppendYAP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateABP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateALL"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateBUH"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateCAP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateCTP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateMBG"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateWV"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_UpdateYAP"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly
    stdocname = "Qry_Expired_COMPLETE"
    DoCmd.OpenQuery stdocname, acViewNormal, acReadOnly

    DoCmd.SetWarnings True

    End Sub
    Last edited by noaccessguru; 08-29-2013 at 01:18 AM. Reason: correction to code

  12. #12
    euanl is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Posts
    25
    No, as I said, as far as I am aware, and I am happy to be corrected on this, you can't lock down the design view of queries without substantial changes to your database.

    Also there is no need to make the append and update queries read only as they just run (without the user seeing them running). Just DoCmd.OpenQuery stdocname, acViewNormal for those, (in case the read only bit stops them working right for example).

  13. #13
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I did do this and it works fine, but the final query that opens (which is what I want to open), still allows you to make a change to the query if you go into design view, even though I have it set to Read Only?
    That only sets how the Query initially opens. If they start changing around with the views and such, they can easily get around that.

    If you want to display the data to them that is not editable, put the data in a Report instead, and open the Report instead of the Query.

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

Similar Threads

  1. Run Append and Update Queries
    By Govinda in forum Programming
    Replies: 1
    Last Post: 04-04-2013, 01:14 AM
  2. Replies: 4
    Last Post: 12-14-2012, 06:33 PM
  3. Replies: 5
    Last Post: 09-12-2012, 09:53 AM
  4. Replies: 1
    Last Post: 12-29-2011, 05:51 PM
  5. macro: rapid firing of update queries
    By stevepcne in forum Access
    Replies: 1
    Last Post: 11-17-2011, 05:55 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