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

    Question Is there a way to open multiple DB's & run the macros within each DB, w/ one "master" DB or macro??

    I am trying to find out if there is a way to create a "Master Macro or DB(?)" that can Open multiple DB's & run the macro's within each DB, in succession?


    i.e.
    1-Open a DB
    2-Run the macro's in the DB
    3-Then close the DB
    4-Open the next DB, run the macros in that DB, close it, etc...

    I have multiple Access DB's, each with a lot of queries within each DB, to create multiple tables that I link to Excel to create reports. I currently open each DB individually and run 1-4 macros in each DB to create all of the tables needed for 1 report in Excel. Due to size of the DB's and similar data, I cannot combine all of the queries into 1 DB.

    Is it possible to create a Master DB (or something) that does all of the Opening, Running macros, & Closing of each DB, so I don't have to do each DB individually?

    Does this question make sense? I'm not very good with VBA or SQL's other than to make small adjustments such as the table, DB, Macro names involved. Help?
    TIA- Emily

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes,
    create an object , then vb can control the instance, then run macros, or queries...

    Code:
    Public Sub RunAccessInstance()
    Dim acc As Access
    
    Set acc = CreateObject("Access.Application")
    
    With acc
        .Visible = True
        .UserControl = True
        .User = ""
        .Password = ""
        .OpenCurrentDatabase ("\\server\folder\myDb.mdb")
        .Run "mTest"     'run macro
        
        .Quit
    End With
    
    Set acc = Nothing
    End Sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Really not sure what you are doing, but I will try and suggest something to the best of my understanding.

    First: create a new forler and name the folder "TestdB"
    Copy all of the involved dB's to the folder you just created. This is so you are working on a backup/copy of the dBs - if something goes really wrong, you can delete the folder/dBs and not lose anything.

    Second: when you state "run 1-4 macros in each DB to create all of the tables needed for 1 report in Excel." this concerns me. It is really not a good idea to constantly be creating new tables. It is better to delete the records. Constantly be creating new tables is a good way to corrupt your dB...

    Third: there needs to be a way to know which tables/macros are from which dB.
    In each of the dBs, give each of the tables a prefix. So lets say you have 4 dBs.
    In the 1st dB, each table will have a prefix of d1. Each of the macros will also have the same prefix.
    In the 2nd dB, each table will have a prefix of d2. Macros also.
    In the 3rddB, each table will have a prefix of d2. Macros also.
    In the 4th dB, each table will have a prefix of d2. Macros also.

    Fourth: Create a new Access dB, ie the "Master".

    Fifth: LINK the tables in each of the data dBs (the BEs) to the FE (the Master).
    Import the macros from the BE dBs into the FE (the Master)

    Now all of the tables/macros are in one "Master dB". Or, at least, it appears that way.......


    I do not use macros. They are too limiting. So you are on your own from here on......

    Now that all of the macros are in the "Master" FE, you will have to create a master macro to run each of the macros in the correct order.
    You will also have to modify the macros, changing the table names to the new renamed table names.



    Good luck with your project......

  4. #4
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by Emkretsch View Post
    Is it possible to create a Master DB (or something) that does all of the Opening, Running macros, & Closing of each DB, so I don't have to do each DB individually?
    Emily,

    Yes, you can make a master DB to do what you want.

    The way I recommend doing this is:

    1) create a new front end
    2) Links to all the required tables in the different back ends (databases)
    3) import all the existing macros and VBA code that needs run.


    Now you have a single front end (database) that can do all the work.

    I would think it would be possible to create a form with a command button that runs everything.

    I do similar things where I make the frontend autorun on open and close. All without any human interaction. That way I can set it up as a scheduled task.

  5. #5
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9
    Hi, Thank you for your help!! I know I'm not the best when clarifying what I am trying to do.

    In response to your "Second" statement about always creating new tables being a good way to corrupt my DB...My current queries delete the tables created from the last time I ran the query before the query runs. Then they create a new table with the new records (basically). Is that what you mean by deleting records first?

    Correct me if I am not understanding please... following your procedure, wouldn't all of the final tables created from the macros, end up in the FE (1 big DB)? If so, that is what I am trying to avoid. I have millions of records in each DB that I am querying & pulling into the tables. The tables end up very large. So because my final tables pretty much max out a DB (they get cranky when I make them too big 2+Gb), I want all of the final tables/data to end up in the original DB's.

    Example:
    DB1 -I run 4 macros that include 15-20 queries that search hundreds of thousands of records, from about 20 tables. I end up with about 10 final tables of data used for an excel report that includes multiple tabs & pivot tables, charts, etc..

    DB2 -I run 3 macros that include 10 queries that search hundreds of thousands of records, from about 10 different tables. I end up with about 10 final tables of data used for a completely different excel report (that includes multiple tabs & pivot tables, charts, etc..)

    DB3 - similar situation as DB 1 (& 2)

    So I'd like something (? don't know what or how) to open each DB, run the macros within it, return all of the data into the same DB the macros are in, close the DB, and move on to the next DB to do the same thing. Am I just confused about how your process would work? (I wouldn't doubt it, I'm kind of a newbie with Access) TIA! Emily

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think this is directed towards me......soooo

    First a question, mostly out of curiosity. Are you running macros or VBA code?


    OK,
    My current queries delete the tables created from the last time I ran the query before the query runs. Then they create a new table with the new records (basically). Is that what you mean by deleting records first?
    No! So every time you run the queries, first the table(s) are deleted, then a make table query runs and records are added?
    I am saying, run a delete query to delete the records in the tables, then append queries to add records.
    Just delete the records, NOT the tables. Then append the (new) records.


    Correct me if I am not understanding please... following your procedure, wouldn't all of the final tables created from the macros, end up in the FE (1 big DB)? If so, that is what I am trying to avoid. I have millions of records in each DB that I am querying & pulling into the tables. The tables end up very large. So because my final tables pretty much max out a DB (they get cranky when I make them too big 2+Gb), I want all of the final tables/data to end up in the original DB's.
    Since you wouldn't be creating tables, the tables stay in their respective BEs. Because the BEs are LINKED to the Master FE AND each table has a unique name (even across the 3 dBs), everything would be as it is now, except the new Master FE that has the macros/VBA code.


    BTW, SQL Server Express can handle up to 10 GB per dB.... if that is an option.


    HiTechCoach (Boyd) and I are suggesting the same thing, he just explained it clearer than I did.

  7. #7
    Emkretsch is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    9
    Quote Originally Posted by ssanfu View Post
    I think this is directed towards me......soooo

    First a question, mostly out of curiosity. Are you running macros or VBA code?


    OK,

    No! So every time you run the queries, first the table(s) are deleted, then a make table query runs and records are added?
    I am saying, run a delete query to delete the records in the tables, then append queries to add records.
    Just delete the records, NOT the tables. Then append the (new) records.



    Since you wouldn't be creating tables, the tables stay in their respective BEs. Because the BEs are LINKED to the Master FE AND each table has a unique name (even across the 3 dBs), everything would be as it is now, except the new Master FE that has the macros/VBA code.


    BTW, SQL Server Express can handle up to 10 GB per dB.... if that is an option.


    HiTechCoach (Boyd) and I are suggesting the same thing, he just explained it clearer than I did.
    AHHHHH, The lightbulb flickers. LOL Now I get what you guys are saying. I will teach myself how to actually put this in motion, and let you know how it goes. I totally get what you are saying now. It will take a good amount of changing & updating names, queries, & spreadsheets, but it sounds like it will get me where I want to be in the end. Thank you everyone!!!!!

    P.S.- I work in a big company (almost 1M employees) and basically, I get what I get, as far as storage & programs, etc... We are on heavy lockdown with ability to do much outside of IT's box.

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

Similar Threads

  1. Replies: 14
    Last Post: 12-18-2015, 02:04 PM
  2. Replies: 5
    Last Post: 12-04-2015, 10:19 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 02-06-2015, 03:22 PM
  5. Replies: 4
    Last Post: 08-11-2014, 01:18 PM

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