Results 1 to 11 of 11

How to simplify a macro which updates my data

  1. #1
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49

    How to simplify a macro which updates my data

    I use Access to view a database which is placed in a SQL server "somewhere". I got the link and can access the data. No problem.
    In order to access the data (way) faster, I have created a series of queries that copy the data from the SQL server onto local tables in Access.
    To do THAT faster, I have made a macro that runs these queries and thereby updates all the local tables with a fresh view of data on-demand.

    Now, I'd like to simplify my macro:
    Presently, I use commands that does OpenQuery, then a command that does ActiveObject (said query), then a command that does CloseWindow (same query).
    This works, but it is unelegant because I need three lines of script for each query. Since I've a lot of queries I quickly lose track when editing the macro for trying new stuff out.
    I would like to know if there is the possibility to make the Access macro run the query (thereby updating the corresponding local table) using only one line of script in the macro.
    Something that does "Opens THIS query, run THE SAME query, then shut it".

    Is this possible?



    Thank you in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,537
    Running a query is 1 line, docmd.openquery.

    close window is 1 line...a form? You don't need to close a query.
    Not sure what the active object is...on a form?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,514
    If you had some way of easily identifying which queries you'd want to run, code would be a more elegant solution. F'rinstance, if every query name for this began with "_" then you could loop through the stored queries and only run those that begin with that character using one line of code. Or you could enter those names in a table but that is extra steps and adding a record each time there's a new query. Many ways.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  4. #4
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49
    Quote Originally Posted by ranman256 View Post
    Running a query is 1 line, docmd.openquery.

    close window is 1 line...a form? You don't need to close a query.
    Not sure what the active object is...on a form?
    Thank you for putting things straight here.
    In my experience, I've had to Open the query, then Activate it, and Close it.
    Trying again now, I see that it works even though I only Open the queries.
    Strange. Anyway, case solved.

  5. #5
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49
    Quote Originally Posted by Micron View Post
    If you had some way of easily identifying which queries you'd want to run, code would be a more elegant solution. F'rinstance, if every query name for this began with "_" then you could loop through the stored queries and only run those that begin with that character using one line of code. Or you could enter those names in a table but that is extra steps and adding a record each time there's a new query. Many ways.
    I haven't any experience with code, only in Excel, because the macros are so tied-in to the VBA code in Excel. I hanve't really found out how VBA functions in Access and if it is connected to the macros in the same way. Thanks all the same though.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Macros in Excel are essentially VBA code. Macros in Access are very different animals.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49
    Quote Originally Posted by June7 View Post
    Macros in Excel are essentially VBA code. Macros in Access are very different animals.
    If I were to try and do the code you mentioned earlier, how would I go about that?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    I didn't mention any code earlier. Don't know what you are asking for.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,514
    Quote Originally Posted by June7 View Post
    I didn't mention any code earlier. Don't know what you are asking for.
    I thought that the question might be for me, but didn't want to jump in because it was directed to you. The following should iterate over the stored queries and report the names and types that begin with _
    NOTE: any character might be useful - except ~

    LIST OF QUERY TYPE CONSTANTS HERE

    This first code example is so you can identify the types you need to run and edit the Select Case block accordingly, using the type name constants.

    Code:
    Sub LoopQueries()
    Dim qdf As DAO.QueryDef
    
    For Each qdf In CurrentDb.QueryDefs
      If Left(qdf.Name, 1) = "_" Then
        Debug.Print qdf.Name & "  " & qdf.Type
      End If
    Next
    End Sub
    While the following code will run Select queries, I have no idea if that's useful. I doubt it, but it's there anyway as part of the example. I'm also assuming that if there's a failure for any one query, code should report that failure and then stop. Any query that has parameters (such as a form reference) will fail if that form isn't open. Likely also will fail if a stacked query (one that calls other queries) has such parameters.
    Code:
    Sub RunQueries()
    Dim qdf As DAO.QueryDef
    
    On Error GoTo errHandler
    
    For Each qdf In CurrentDb.QueryDefs
     If Left(qdf.Name, 1) = "_" Then
       Select Case qdf.Type
         Case dbQDelete, dbQAppend, dbQUpdate
           CurrentDb.Execute qdf.Name, dbFailOnError
         Case dbQSelect, dbQSetOperation
           DoCmd.OpenQuery qdf.Name
       End Select
     End If
    
    Next
    exitHere:
    Exit Sub
    
    errHandler:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR IN " & qdf.Name
    Resume exitHere
    
    End Sub
    Then there is the method I mentioned of calling queries listed in a table, but this isn't it.
    Last edited by Micron; 04-20-2019 at 01:54 PM. Reason: Clarification
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  10. #10
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49
    Quote Originally Posted by June7 View Post
    I didn't mention any code earlier. Don't know what you are asking for.
    Sorry, that was a mix-up.

  11. #11
    Nanuaraq is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    49
    Quote Originally Posted by Micron View Post
    I thought that the question might be for me, but didn't want to jump in because it was directed to you. The following should iterate over the stored queries and report the names and types that begin with _
    NOTE: any character might be useful - except ~

    LIST OF QUERY TYPE CONSTANTS HERE

    This first code example is so you can identify the types you need to run and edit the Select Case block accordingly, using the type name constants.

    (...)

    Then there is the method I mentioned of calling queries listed in a table, but this isn't it.
    Thank you, will look into it.

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

Similar Threads

  1. how to simplify this query..............plz help
    By lalprasad in forum Queries
    Replies: 2
    Last Post: 10-25-2017, 12:41 AM
  2. Replies: 5
    Last Post: 05-23-2016, 08:15 AM
  3. Simplify VBA Code
    By bytreeide in forum Programming
    Replies: 3
    Last Post: 05-27-2015, 01:36 PM
  4. Help To Simplify My Code
    By graviz in forum Programming
    Replies: 2
    Last Post: 09-11-2013, 06:58 PM
  5. Need help to simplify this process
    By shanea.kr in forum Access
    Replies: 1
    Last Post: 07-10-2012, 12:40 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
  •  
Tech Forums: Microsoft Office Forums