471,075 Members | 1,135 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Automate macros in 20 access database files

4 Nibble
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
2 Weeks Ago #1
13 8522
zmbd
5,486 Expert Mod 4TB
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?
2 Weeks Ago #2
NeoPa
32,341 Expert Mod 16PB
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.
2 Weeks Ago #3
zmbd
5,486 Expert Mod 4TB
@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!
2 Weeks Ago #4
NeoPa
32,341 Expert Mod 16PB
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.
2 Weeks Ago #5
ADezii
8,816 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub RunMacro(strMacroName As String)
  2. Dim MyDB As DAO.Database
  3. Dim rstDBs As DAO.Recordset
  4. Dim appAccess As Access.Application
  5.  
  6. Set MyDB = CurrentDb
  7. Set rstDBs = MyDB.OpenRecordset("tblDBsToRunMacros", dbOpenForwardOnly)
  8.  
  9. With rstDBs
  10.   Do While Not .EOF
  11.     Set appAccess = New Access.Application
  12.  
  13.     With appAccess
  14.       'Open External DB in Microsoft Access Window.
  15.       .OpenCurrentDatabase rstDBs![DB_Path]
  16.  
  17.       .DoCmd.RunMacro strMacroName      'Run the Macro
  18.       .CloseCurrentDatabase    'Close the DB
  19.       .Quit
  20.     End With
  21.  
  22.     Set appAccess = Nothing
  23.       .MoveNext
  24.   Loop
  25. End With
  26.  
  27. rstDBs.Close
  28. Set rstDBs = Nothing
  29. End Sub
  30.  
Expand|Select|Wrap|Line Numbers
  1. Call RunMacro("mcrDemo")
1 Week Ago #6
hansy
4 Nibble
@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.
1 Week Ago #7
hansy
4 Nibble
@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 ?
1 Week Ago #8
ADezii
8,816 Expert 8TB
..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.
1 Week Ago #9
zmbd
5,486 Expert Mod 4TB
@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.

Attached Images
File Type: jpg Capture.jpg (45.9 KB, 129 views)
1 Week Ago #10
NeoPa
32,341 Expert Mod 16PB
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.
1 Week Ago #11
ADezii
8,816 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. Public Sub RunMacro()
  2. Dim MyDB As DAO.Database
  3. Dim rstDBs As DAO.Recordset
  4. Dim appAccess As Access.Application
  5.  
  6. Set MyDB = CurrentDb
  7. Set rstDBs = MyDB.OpenRecordset("tblDBsToRunMacros", dbOpenForwardOnly)
  8.  
  9. With rstDBs
  10.   Do While Not .EOF
  11.     Set appAccess = New Access.Application
  12.  
  13.     With appAccess
  14.       'Open External DB in Microsoft Access Window.
  15.       .OpenCurrentDatabase rstDBs![DB_Path]
  16.  
  17.       .DoCmd.RunMacro rstDBs![MacroName]   'Run the Macro for the DB
  18.       .CloseCurrentDatabase    'Close the DB
  19.       .Quit
  20.     End With
  21.  
  22.     Set appAccess = Nothing
  23.       .MoveNext
  24.   Loop
  25. End With
  26.  
  27. rstDBs.Close
  28. Set rstDBs = Nothing
  29. End Sub
  30.  
Expand|Select|Wrap|Line Numbers
  1. Call RunMacro()
  2.  
NOTE: A typical tblDBsToRunMacros would now look like:

Attached Images
File Type: jpg tblDBsToRunMacros.jpg (69.9 KB, 121 views)
1 Week Ago #12
isladogs
367 Expert Mod 256MB
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:
Expand|Select|Wrap|Line Numbers
  1. "G:\MyFiles\ExampleDatabases\TestDB\TestDB.accdb" /x "mcrExportDataToExcel"
1 Week Ago #13
hansy
4 Nibble
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.
1 Week Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by mikemalin | last post: by
1 post views Thread by farseer | last post: by
1 post views Thread by MadCrazyNewbie | last post: by
1 post views Thread by dave | last post: by
4 posts views Thread by Richard Finnigan | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.