473,215 Members | 1,264 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,215 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
Jul 29 '22 #1
13 14512
zmbd
5,501 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?
Jul 29 '22 #2
NeoPa
32,554 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.
Jul 29 '22 #3
zmbd
5,501 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!
Jul 29 '22 #4
NeoPa
32,554 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.
Jul 30 '22 #5
ADezii
8,834 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")
Jul 30 '22 #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.
Jul 30 '22 #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 ?
Jul 30 '22 #8
ADezii
8,834 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.
Jul 30 '22 #9
zmbd
5,501 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, 235 views)
Jul 31 '22 #10
NeoPa
32,554 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.
Jul 31 '22 #11
ADezii
8,834 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, 213 views)
Jul 31 '22 #12
isladogs
451 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"
Aug 1 '22 #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.
Aug 3 '22 #14

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

Similar topics

10
by: MHenry | last post by:
Hi, We were going merrily along for 6 years using this database to record all client checks that came into our office, including information about what the checks were for. Suddenly, network...
6
by: ronwer | last post by:
Hello, The title doesn't completely cover the question I have, but it's a bit more complicated problem we have. We are using a database, based on Acces, but developed by a third party...
1
by: mikemalin | last post by:
I've done some research on the net and think I have an answer, but I'm just looking for some other opinions. I would like to create an access database file to distribute to others. I would use...
2
by: James | last post by:
Dear Access Guru's, Hopefully you can help me (as Microsoft don't seem to be able to) We have an Access database on a Windows 2003 server with 5 CALS in our office. Access is loaded onto...
5
by: rosli bakar | last post by:
I'm quite new to VB and need help urgently . I have 2 Access database files which need to connected/join to each other. Database file 1 is in c:\dha\ABC.mdb table: LogAlarm Work Week | Alarm...
1
by: farseer | last post by:
Hi, i would like to write an app that will do the following: 1. download a csv file from an http url. fields are delimited by the "|" 2. i would like to import this into an access database...
1
by: MadCrazyNewbie | last post by:
Hey Group, Wonder if somebody could help me please? Im after a way in either Access or SQL to be able to store .xls, .doc, .pdf & a few other files? Is this possible, anybody got any links...
5
by: willington79 | last post by:
Hello all, We have a payroll application that runs with MS Access. It is comprised of several different database files and one ms access mdb file for each client. This means there can be from...
1
by: dave | last post by:
Hello: In Visual Studio 6.0 there is "VisData" to create Access database files. (file.MDB". Q: Does Visual Studio 2005 have tool(s) that create Access database files (file.MDB) ? thank...
4
by: Richard Finnigan | last post by:
Hi I'm having real difficulties getting a very simple access database file to show its data in visual studio data controls with various error messages occuring. I've changed the permissions on...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.