Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Question Run Access 2013 union query in background

    Hi Everyone!

    I'm having issues getting a union query to just run in the background. I've used the below script in vba and no luck. any help is extremely appreciated.

    Private Sub DataEndDate_AfterUpdate()

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDataAudit"

    DoCmd.SetWarnings True


    End Sub

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Does the query run if you disable the SetWarning statements? And what is that query expected to do besides display any results of the query?
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    The union query runs whether is within DoCmd.SetWarnings False and True or without it. It just keeps opening up and displaying the results. The query collects the data that is then appended to a temp table.
    Last edited by Lastcall; 2015-11-06 at 10:34. Reason: missed the query purpose

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm having trouble understanding what isn't working. Since Union queries are not action queries, you don't get any warnings when you run them, so the SetWarnings property doesn't have any effect on the operation of the query, as you have discovered. If you want to use the results of that query to append data to a temp table, then you need an append query that uses the Union query as it's data source, and that query will show warnings since append queries are action queries. Will that accomplish what you are trying to do?
    Wendell

  5. #5
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by Lastcall View Post
    The union query runs whether is within DoCmd.SetWarnings False and True or without it. It just keeps opening up and displaying the results. The query collects the data that is then appended to a temp table.
    Your code is working correctly as written. You are telling the query to Open (OpenQuery) not Execute or Run(RunSQL). Which is what you described.

    If you want an append query to actual Execute/Run then you must use a different command.

    Try:

    Code:
    Private Sub DataEndDate_AfterUpdate()
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL "qryDataAudit"
    
    DoCmd.SetWarnings True
    
    
    End Sub
    or

    Code:
    Private Sub DataEndDate_AfterUpdate()
    
    
    CurrentDB.Execute "qryDataAudit"
    
    End Sub

    or if you do not want any record to append is there is any error then add dbFailOnError like this

    Code:
    Private Sub DataEndDate_AfterUpdate()
    
    
    CurrentDB.Execute "qryDataAudit", dbFailOnError
    
    
    End Sub
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #6
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    HiTechCoach, you are correct I want the union query just to execute so then I can run the append query after the union query has collected the data. I tried your suggested codes; I received the attached image, error 3129, when I ran DoCmd.RunSQL "qryDataAudit"db-Union-Error.png

    Also I received, error 3065, when I ran CurrentDB.Execute "qryDataAudit".db-Union-Error2.png

  7. #7
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I assume that the query qryDataAudit was an append query that was based on the Union Query.

    Is that is not true you need to make an append query that use the saved union query as the data source. When you create the append qquery select the query table to find the saved union query in the Show Table dialog.

    When yu rin the append query that is based on the union query it will do what you want. Access runs all the underlying queries first. In your case the union query will run and the results are automatically passed back up to the append query.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •