Results 1 to 9 of 9
  1. #1
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51

    Open-Run a Macro-Close a database from a separate database

    Hello friends,

    I want to open a Access database (aka Db1), run a macro, then close the database from a separate database (aka Db2). The code below works great to open Db1 and run the macro from Db2. But Db1 stays open. I attempted to add QuitAccess to the end of the macro. When I ran the code from Db2, I got a Microsoft Visual Basic error "Run-time error 2501. The RunMacro action was cancelled" and the objAccess.DoCmd.RunMacro "MACRO NAME" part of the code was highlighted. How can I get Db1 to close after the macro completes? Thanks in advance for your suggestions.

    Cheers, Kevin

    Public Function RunSeparateDbMacro()


    Dim objAccess As Access.Application


    Set objAccess = CreateObject("Access.Application")
    objAccess.Visible = True
    'Open Test.mdb exclusively
    objAccess.OpenCurrentDatabase "C:\DIFFERENT DATABASE PATH\DIFFERENT DATABASE NAME.accdb", True
    objAccess.DoCmd.RunMacro "MACRO NAME"
    objAccess.DoCmd.Maximize
    Set objAccess = Nothing


    End Function

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why bother with making db Visible and Maximized?

    Before setting to nothing, try:

    objAccess.Quit
    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.

  3. #3
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Hi June7,

    Thanks for your suggestion. Sorry. I copied that code from my library and forgot the Visible and Maximized was there. I placed objAccess.Quit above Set objAccess = Nothing and got the same "Run-time error 2501".

    Cheers, Kevin

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Works for me. No error.

    Is your macro actually named "MACRO NAME"?

    I had assumed you have actual database path/file as well as actual macro name in your actual code. If what you posted is literally your code, no doubt it will fail.
    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.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would think it should be
    Dim appAccess As Object
    Set appAccess = CreateObject("Access.Application")

    or

    Dim appAccess As New Access.Application

    but not both?

    Isn't your application object being destroyed before the macro even has a chance to launch?

    EDIT - guess I should have tested first. Works for me too although I did also test as Dim objAccess As Object as well.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    Thank you both for your replies.
    June7 - My macro name is macro566. I still get the error. Could you please post your successful code?
    Micron - Would you please post your code as well?

    Thank you both for your help.
    Cheers, Kevin

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I copy/pasted your code into a Sub instead of Function, changed file path and macro name, deleted the Visible and Maximize lines, added the Quit line.

    Only thing my macro does is open a MsgBox.
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by June7 View Post
    I copy/pasted your code into a Sub instead of Function, changed file path and macro name, deleted the Visible and Maximize lines, added the Quit line.

    Only thing my macro does is open a MsgBox.
    I did exactly the same thing except for the Quit line - just a message box which is raised by Macro1 in the remote db. db name and folder in below code changed for privacy reasons.

    Code:
    Dim objAccess As Object
    Set objAccess = CreateObject("Access.Application")
    objAccess.Visible = True
    'Open Test.mdb exclusively
    objAccess.OpenCurrentDatabase "C:\Users\Micron\Access\AccessForums\FolderNameHere\DataBaseNameHere.accdb", True
    objAccess.DoCmd.RunMacro "Macro1"
    Set objAccess = Nothing
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Macallan60 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2010
    Posts
    51
    I had a PICNIC error (Problem In Chair Not In Computer). The macro in Db1 still had the QuitAccess command. Once I removed it, the code worked fine.

    Thank you both for your kind help and suggestions.
    Cheers, Kevin

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

Similar Threads

  1. Hide Min / Max and Close Title Bar on Database Open
    By billgyrotech1 in forum Access
    Replies: 9
    Last Post: 06-08-2019, 06:57 AM
  2. Replies: 7
    Last Post: 02-19-2016, 11:14 PM
  3. Replies: 3
    Last Post: 08-20-2015, 10:31 AM
  4. Replies: 1
    Last Post: 07-02-2014, 11:22 AM
  5. Replies: 3
    Last Post: 06-20-2013, 08:57 AM

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