Results 1 to 11 of 11
  1. #1
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9

    Question How do I run a Macro saved in DB #1 from a different external DB (#2)?

    Hi,
    I have a macro in an Access 2013 DB #1, that opens an external Access DB #2.
    In DB #2 I have a macro (called "Test Run Function") that runs a function module, which calls a sub code module to run.



    1. I want to be able to create a macro (or just the macro action if possible) in DB#1, that will run the "Test Run Function" macro in DB #2. I already have a macro in DB #1 that opens DB #2. I do not want to use an Autoexec in DB #2 because I do not want the macro to run every time I open the DB.

    2. I need to know how to have a macro in DB #1, close DB #2.

    I am trying to automate as many steps as possible for a big report I will be creating.
    I have DB #1 as my workhorse & DB #2 as my final summarized data tables to be used to create the report in Excel with PowerPivot.

    Please be kind as I am a newbie to VBA, so I don't know many commands or the lingo. I'm just learning some of the basics as I need them.

    ANY ideas or help would be GREATLY appreciated!! TY TY TY!!!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There have been several similar questions in recent weeks
    The obvious question is why can't you do everything you need from DB1?

    In DB1, link any DB2 tables involved with this macro
    Also Import any queries needed from DB2 to DB1 and the DB2 Test Run Function macro

    Now run the macro from DB1 - no need to open (or later close) DB2
    You can also then "create the report in Excel with PowerPivot." in DB1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9
    Quote Originally Posted by ridders52 View Post
    There have been several similar questions in recent weeks
    The obvious question is why can't you do everything you need from DB1?

    In DB1, link any DB2 tables involved with this macro
    Also Import any queries needed from DB2 to DB1 and the DB2 Test Run Function macro

    Now run the macro from DB1 - no need to open (or later close) DB2
    You can also then "create the report in Excel with PowerPivot." in DB1
    ---------------------------------------

    Thank you Ridders52- I searched the forums for about an hour yesterday trying to find an answer, & came up with nothing that would solve my issue. I apologize if it has already been answered.

    I should give a little more detail about my current project.
    I am working with up to 15 1-2GB DB's that currently create 6 large reports. I am working with huge amounts of data from almost 100K employees, so running everything out of 1 DB in this case isn't possible. Trust me, I wish it was. I am trying to combine & remove some of the report data to slim down the amount of DB's we currently use. (We are working on converting to a better storage program, but until then, I am stuck using the 2013 Access DB's.)

    So being able to automate running a macro in one DB from a different DB, and then close it would help a lot.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Emkretsch View Post
    ---------------------------------------

    Thank you Ridders52- I searched the forums for about an hour yesterday trying to find an answer, & came up with nothing that would solve my issue. I apologize if it has already been answered.

    I should give a little more detail about my current project.
    I am working with up to 15 1-2GB DB's that currently create 6 large reports. I am working with huge amounts of data from almost 100K employees, so running everything out of 1 DB in this case isn't possible. Trust me, I wish it was. I am trying to combine & remove some of the report data to slim down the amount of DB's we currently use. (We are working on converting to a better storage program, but until then, I am stuck using the 2013 Access DB's.)

    So being able to automate running a macro in one DB from a different DB, and then close it would help a lot.
    Sorry to disagree but nothing you wrote prevents the solution I suggested.

    A reminder that I wasn't talking about putting all your tables into one database
    That clearly isn't possible in any case due to the large size of your databases as Access has a 2GB limit
    As an aside, I strongly recommend you move the BE databases to SQL Server for extra stability, greater capacity (10GB) and quite possibly faster performance

    However, you can LINK to any required tables in one or more external databases
    Once you have done so, you can run queries, forms, reports, VBA code and (if you must) macros based on those linked tables from within the 'host' database
    As a further aside, I also recommend you convert your macros to VBA code for the significant additional capability that this will provide

    So once again, my point is that there is no need to open DB2 to run your macro & therefore no need to close it again
    It can be done but its totally unnecessary here
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9
    Quote Originally Posted by ridders52 View Post
    Sorry to disagree but nothing you wrote prevents the solution I suggested.

    A reminder that I wasn't talking about putting all your tables into one database
    That clearly isn't possible in any case due to the large size of your databases as Access has a 2GB limit
    As an aside, I strongly recommend you move the BE databases to SQL Server for extra stability, greater capacity (10GB) and quite possibly faster performance

    However, you can LINK to any required tables in one or more external databases
    Once you have done so, you can run queries, forms, reports, VBA code and (if you must) macros based on those linked tables from within the 'host' database
    As a further aside, I also recommend you convert your macros to VBA code for the significant additional capability that this will provide

    So once again, my point is that there is no need to open DB2 to run your macro & therefore no need to close it again
    It can be done but its totally unnecessary here
    ------------------------------
    Thank you for your efforts to help. I will try to get an answer to my question somewhere else I guess...?

    I understood what you said the 1st time, but you did not answer my question. You are making assumptions about my DB content (that it has tables to link to & queries to run), & replying based on those assumptions. I feel like you are creating a cobweb of scenarios & solutions that do everything but answer my 1 question.

    I just want to find out if there is a way (if so, how) to run a macro in one DB, & close it from a different DB. (If I knew how to do everything you mentioned, and had the time, authority, and ability to do it, I wouldn't need to ask my question, right?). There is a reason I am doing and have done everything in this way. I have a large amount of limitations with what I can do.

    #1- I am not running any queries in DB2.
    #2- There are no tables to link to in DB2 because..
    #3- The commands/Macro I run in DB2 is not tied to any tables, and in fact deletes all of the tables that exist in DB #2.

    #4 I am in a HUGE company on a network system. I have zero control over/nor knowledge of using SQL Server, or any other storage systems my company uses. All I have been told is that the company is converting to Teradata and until it is all moved, I will have to carry on as I have been with what I have. As a whole, we (my company) JUST updated to Windows 2010, with office 2013. Some of our computers are still on 2007...I am not in IT/IS. Welcome to my struggle.

    #5 As I wrote in my original post, I am a beginner with VBA. I have no clue how to write VBA. I stumbled my way through the 2 small codes I managed to create using google. I have no clue how to convert my macros to VBA, and am not given the time at work to figure it out. I already KNOW there are much faster, more efficient ways of doing what I need to do, but I do not have the time to teach myself VBA or re-do everything I have already created. (Again, I REALLY wish I did)

    Again, Thank you for your suggestions & time spent trying to help. It is appreciated!

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is this what you are looking for? It's vba, and I doubt could be recreated as a macro, but you are welcome to try.

    https://www.devhut.net/2014/09/16/ms...rnal-database/

    The other option, if your macro is converted to vba is to add the other db as a library. No idea if it will work for access macros, you would have to give it a try. I don't use macros due to their limitations.

    To add your file as a library in vba, in the vba window click on tools>references . Then click on browse and navigate to your other db and select it. Make sure you change the file type to .accdb before searching
    I have no clue how to convert my macros to VBA
    there is a button on the ribbon. For standalone macros, you will find it if you go into the macro design, for form/report macros, you will find it in form/report design

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hopefully you will appreciate that I wasn't being awkward but trying to give you good advice
    All the previous comments are still valid but as requested, here's an answer to your question (hopefully!)....

    The code below opens the external database, runs a macro then closes the external database
    Optionally it closes the current database as well

    Code:
    Public Function RunExternalDatabase() As Boolean
        
        Dim app As Access.Application, strPath As String
        
        'Start a new Access application
        Set app = New Access.Application
        
        'Open the remote database and run a macro, then close the remote database
        With app
            'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
            strPath = "C:\Programs\MendipDataSystems\JATFA\JATFA.accdb" 'replace with your file path
            .OpenCurrentDatabase strPath, True, "password"
        
        '    .DoCmd.RunMacro "YourMacroNameHere" 'run your macro
            .CloseCurrentDatabase 'closes external database as that is current
        End With
        
        'Quit the spawned app
        app.Quit acQuitSaveNone
        Set app = Nothing
        
        'Quit the current app - optional
        Application.Quit acQuitSaveNone
        
    End Function
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9
    RIDDERS52
    THANK YOU, THANK YOU, THANK YOU!! This is exactly what I was looking for! I apologize for getting frustrated. I'm still VERY new to VBA and I know there are WAAAAAY easier & better ways of setting up my DB's, but I have not been given the time to learn, research, & carry out the changes.
    Trust me, I'd TOTALLY LOVE to learn & be able to write VBA, but My boss is "give me results ASAP" driven, and not so good with my "But if I take the time now to learn this, going forward everything will be faster & much easier" opinion. So I'm stuck looking for answers by asking..."how can I accomplish this now, with what I can grasp, so I have something to show for my time". Having to be this way is BEYOND frustrating for me, but I'm stuck with it for now. I really do appreciate the help, & I think I took some of my own frustration out on you. My apologies sir! Thank you!

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Three weeks on! I'd completely forgotten about this thread.
    Obviously you didn't manage to find the answer elsewhere.

    There are two approaches to answering questions.
    I prefer to give good advice rather than exactly what the OP wants if I believe there is a better way
    Anyway, glad you're happy now.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9

    Circled around

    Actually I was working on something else and circled back around to the site because I had a different question. saw you had replied to this and took a peek just for grins. Thank you for giving me exactly what I wanted in the end(vs. just the advice) on this one.

    Quote Originally Posted by ridders52 View Post
    Three weeks on! I'd completely forgotten about this thread.
    Obviously you didn't manage to find the answer elsewhere.

    There are two approaches to answering questions.
    I prefer to give good advice rather than exactly what the OP wants if I believe there is a better way
    Anyway, glad you're happy now.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Emkretsch View Post
    Actually I was working on something else and circled back around to the site because I had a different question. saw you had replied to this and took a peek just for grins. Thank you for giving me exactly what I wanted in the end(vs. just the advice) on this one.
    LOL - remind me to keep well away next time!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 1
    Last Post: 10-29-2015, 07:03 AM
  2. Replies: 6
    Last Post: 09-22-2014, 02:45 AM
  3. Macro to mimic Import & Link Excel button on external data
    By arnstrb in forum Import/Export Data
    Replies: 1
    Last Post: 03-16-2014, 07:52 PM
  4. Replies: 1
    Last Post: 05-29-2013, 03:08 PM
  5. Get External Data Macro
    By Harley Guy in forum Access
    Replies: 2
    Last Post: 11-16-2010, 12:20 AM

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
  •  
Other Forums: Microsoft Office Forums