Automate macros in 20 access database files

[hansy]

I an new to MS access and I took over someone's role where it involves running almost 20 MS access databases everyday taking me close to 2 hours . I really wanna automate it.

1. I open each MS access database file and then run a macro in it and close file . There are several macros in each file and lot of tables but everyday i just run one in each.
2. Repeat same step for other 20 MS access files

How can i automate it so that each access file opens by itself > execute the specified macro > close > move to other file

[zmbd]

Hello Hansy,

Would you take a moment to clarify something for me:
Do you mean Access-Macros Or do you mean Access-VBA
These are very different beasties and call/implementing them are quite different.
The confusion comes from MSExcel calling VBA-Scripts "Macros;" however, MSAccess has two distinct programing environments, Macros and VBA scripts.

... another question comes to mind
What do these 20 different databases do and are they related?

[NeoPa]

Hi Hansy.

Invoking a Database From the Command Line should hopefully give you all you need to look into this.

It uses the VBA approach exclusively.

[zmbd]

@NeoPaInvoking a Database From the Command Line should hopefully give you all you need to look into this

Hopefully this works for Hansy; however, if Hansey is dealing with a highly locked down PC the command line scripts and access to the scheduler are locked out by GPO-Sec.
I've had to find workarounds for all of my dot-BAT, dot-PS1 files, and all of my scheduled runs were removed by GPO and IT-Security refuses to allow an exception - drives me bonkers!

[NeoPa]

Hi Z.

That sucks. I've never worked anywhere where even CMD files are locked down.

Not that the basic concept relies on the CMD file though, as you can run the same script (series of commands to invoke Access in this case) from another database if you wanted to.

[ADezii]

First and foremost, I'd like to say hi to two of my favorite people in the Access world, namely NeoPa and zmbd. I hope that both of you are doing well. I came up with, what I thought, could be a possible solution to your problem in a record amount of time, so please keep in mind that there is probably much room for improvement. The above being said, I assumed that you meant literal Access Macros, but it really doesn't make a difference since the Logic would pretty much be the same in either case. In any event, here it goes:

1. I created a Table named tblDBToRunMacros that consisted of only 2 Fields, namely: [ID]{AUTO -PK} and [DB_Path]{TEXT}.
2. [DB_Path] contains the Absolute Path to your 20 or more External DBs.
3. I created a Sub-Routine named RunMacro() that accepts a single Macro Name to Execute in each of the DBs.
4. This Sub-Routine is called and an Argument is passed to it, in this case mcrDemo.
5. In RunMacro(), each DB in tblDBToRunMacros is opened as the Current Database and a Macro (mcrDemo) is Executed.
6. The above generalities aside, I tested this approach on multiple Northwind Databases and it actually worked quite well. The same Macro (mcrDemo) was Executed in each DB.
7. I posted the relevant Code along with the actual Call below. Needless to say, there are many things that can still go wrong in actual execution.

Public Sub RunMacro(strMacroName As String)
Dim MyDB As DAO.Database
Dim rstDBs As DAO.Recordset
Dim appAccess As Access.Application
 
Set MyDB = CurrentDb
Set rstDBs = MyDB.OpenRecordset("tblDBsToRunMacros", dbOpenForwardOnly)
 
With rstDBs
  Do While Not .EOF
    Set appAccess = New Access.Application
 
    With appAccess
      'Open External DB in Microsoft Access Window.
      .OpenCurrentDatabase rstDBs![DB_Path]
 
      .DoCmd.RunMacro strMacroName      'Run the Macro
      .CloseCurrentDatabase    'Close the DB
      .Quit
    End With
 
    Set appAccess = Nothing
      .MoveNext
  Loop
End With
 
rstDBs.Close
Set rstDBs = Nothing
End Sub
 

Call RunMacro("mcrDemo")

[hansy]

@zmbd They seem as macros to me ( do not see any vba scripting) . The macros in each of the files read input from a specific file in our ftp location and update each tab in our excel (access excel file). I am sorry if i cant explain it well cos I have no idea on it , I just run each macro in these files and it updates our excel with the data we want. Process works wonderfully but is time taking and manual.

[hansy]

@adezii Hello , thankyou ..I could try this out but where would I define this code ? and do macros that i need to run in each of the separate access database be renamed as same name in all ?

[ADezii]

..I could try this out but where would I define this code
One possibility would be in the Click() Event of a Command Button.

do macros that i need to run in each of the separate access database be renamed as same name in all ?
Not at all. You could add the Macro Name along with Database Name in tblDBsToRunMacros. You would then need to pass another Argument (strMacroName As String) to the RunMacr() Sub-Routine and process accordingly.

[zmbd]

@ADeziiThe above being said, I assumed that you meant literal Access Macros, but it really doesn't make a difference since the Logic would pretty much be the same in either case. In any event, here it goes:

🙋 Hello There yourself...
👍👍👊 I'm going to steal the table idea - I haven't tested this; however, nicely done. I have several DB that I've simply hard-coded into the VBA that run to update the databases; however, in my case I'm importing CSV files that we receive from various instruments into the database. I used to do some of this with batch/PowerShell files and the scheduler before IT locked everything down!

@hansyThey seem as macros to me ( do not see any vba scripting)

🐧Curious about why one is pulling the data to Excel. If this is for data manipulation the Excel workbooks can be linked to the Access data tables - this should eliminate the need to run each of the DBs. You might want to start a new thread if you're interested in doing this... we'd need a lot more information.
🐧Realized a picture saves a thousand words... if you're running under the [Macros] tab, then, you're not running a VBA script.

[NeoPa]

Hi guys.

Just a quick "Hello" for my old friend ADezii (and one for my much newer friend zmbd too of course). Both extremely competent Access experts in their own rights.

[ADezii]

@hansy:
Regarding your mentioning of various Macro Names for each DB, you can simply add a [MacroName] Field to tblDBsToRunMacros and reference it within the Recordset Loop. There is also the added benefit of not passing a Macro Name to the RunMacro() Procedure.

Public Sub RunMacro()
Dim MyDB As DAO.Database
Dim rstDBs As DAO.Recordset
Dim appAccess As Access.Application
 
Set MyDB = CurrentDb
Set rstDBs = MyDB.OpenRecordset("tblDBsToRunMacros", dbOpenForwardOnly)
 
With rstDBs
  Do While Not .EOF
    Set appAccess = New Access.Application
 
    With appAccess
      'Open External DB in Microsoft Access Window.
      .OpenCurrentDatabase rstDBs![DB_Path]
 
      .DoCmd.RunMacro rstDBs![MacroName]   'Run the Macro for the DB
      .CloseCurrentDatabase    'Close the DB
      .Quit
    End With
 
    Set appAccess = Nothing
      .MoveNext
  Loop
End With
 
rstDBs.Close
Set rstDBs = Nothing
End Sub
 

Call RunMacro()
 

NOTE: A typical tblDBsToRunMacros would now look like:

[isladogs]

Just a thought.
Why not use code to start each of the databases in turn using the /x command line switch ro run a specified macro
For example:

"G:\MyFiles\ExampleDatabases\TestDB\TestDB.accdb" /x "mcrExportDataToExcel"

[hansy]

Thankyou everyone for your suggestions. I am going to try them and see what works in my situation since I am a newbie to Access so it will take bit of time for me to implement them.