Open database, run macro, close database, loop (1 Viewer)

[CBrighton]

Hi guys!

I have a big selection of databases, each with macros which have to be run on a daily basis. What I am looking to do is to create a new database and use that to launch the macros within the other databases in turn.

However, I also have the concern of poor hardware so I don't want too many Access windows open at once. Ideally I'd like to be able to limit the number open or even pause it while each one is being run.

My current plan is either a long macro with a row manually added for each macro which I want to run within each database (or a few lines per macro if that's what's needed) or a table full of database paths & macro names and looping through the table in VBA.

Before I start looking into the best methods of running macros in external databases I thought I'd see if anyone here has knowledge (or better yet a sample database) of the best way to do this.

The most important thing for me will be that it either runs one external macro and pauses until it finishes (the external macros all end with quit to close the database which they are a part of) or to limit the number running at once.

Simply telling my old workstation to similtaniously run 40-50 external macros spread across a few dozen databases will just cause it to grind to a halt!

:edit:

Sadly, this is Access 97.

 

[KenHigg]

If I may be so bold... Sounds like you need to learn vba

You have way too many macros...

 

[The_Doc_Man]

There is such a thing as creating a command line script to repeatedly run MSAccess.EXE on different databases. You should look at the Access Help on the topic of "command line" to see the options available when you try command-line activation.

In each case, the database must contain a macro. It does not have to always have the same name as long as you know what each macro's name is. You use a command-line option /X:macroname to open the database and run that macro. Each such macro should end with Application.Quit so that when done, the macro crawls back into its own hole and doesn't leave anything dangling. If you don't, it runs once and then dies because the prior incarnation is still open.

Here's a couple of catches. Whatever you do in each Access Macro, you must do it so that NO DIALOG occurs. I.e. remember to set warnings true or false as appropriate (and those are macro commands you can use to do that, see SetWarnings action). You have to assure that you have nothing that will fall flat on its face when an error occurs. I.e. debug each macro thouroughly.

At the workstation level, whether you can even do a command-line script might be an issue. If you can do it, then you might want to do this on a timer based on Windows Task Scheduler. If you don't have full admin rights on your workstation because of corporate policies, this part might be difficult.

At the "each database" level, you need to assure that the default login for a database is non-exclusive unless you are the one and only person who EVER opens those databases. If you don't do this and another user is in at the time your scheduled task triggers the script, you will be locked out by the exclusivity lock.

Finally, depending on how you set up user accounts, you might need to concern yourself with the "RunAs" option if you can use the Windows Task Scheduler. By default, WTS runs as SYSTEM, but if you have a different login, that might become a problem. Now, if you didn't set up accounts or special logins, then everything runs as SYSTEM anyway.

So in summary, the good news is that there is a way to do this. The bad news is that the setup for same is quite tedious. The no-so-bad news is that once you get the first one set up correctly, the others will be easier because you will quickly learn what you need to do to set up each database for this automated batch function.

 

[CBrighton]

I "know" (self trained, so I know what I've done or looked into on this forum) VBA, but I have never had to launch a macro on another database so it's not something I know within VBA.

Sadly I have inherited this task which involves dozens of existing databases from another company pre-merger. All our pre-merger databases are much more VBA based.

I'm already going through all the macros to automate date prompts, replace email with VBA code to avoid security prompts, remove messageboxes, etc. It's just the final running of the macros which I am unsure of at the moment.

I'll look into the calling the database via command line. If it works well enough I can create a table with the database path, the macro name & the pause time (before running the next database in the table) and stick it in a VBA loop on a virtual recordset.

:edit:

Or a selection of batch files running in scheduled tasks.

 

[KenHigg]

I "know" (self trained, so I know what I've done or looked into on this forum) VBA, but I have never had to launch a macro on another database so it's not something I know within VBA.

Sadly I have inherited this task which involves dozens of existing databases from another company pre-merger. All our pre-merger databases are much more VBA based.

I'm already going through all the macros to automate date prompts, replace email with VBA code to avoid security prompts, remove messageboxes, etc. It's just the final running of the macros which I am unsure of at the moment.

I'll look into the calling the database via command line. If it works well enough I can create a table with the database path, the macro name & the pause time (before running the next database in the table) and stick it in a VBA loop on a virtual recordset.

:edit:

Or a selection of batch files running in scheduled tasks.

Sorry about my comment about earning vba. The only macro I ever use is autoexec.

You can pass a value, like openargs, to a database when you open it so that may help you. Use /cmd on the command line and 'command' to retrieve it in the app that you are opening.

 

[CBrighton]

I don't even use autoexec as I tend to have a form open with whatever code I may need at startup launching from that.

The only time I really use one is if I'm in a rush and don't know how to do something in VBA, converting a macro to VBA can sometimes be quicker than actively looking for the VBA solution.

However, I am only one of the users of these databases so all I'm doing is creating edited copies of the objects which are more automated rather than trying to rebuild the whole process my own way.

 

[KenHigg]

So back to your original post. You have a lot of mdbs and you want to kick off a a series of marcos in each one of them. And you want to fire these macros from a local mdb. Is this correct so far?

 

[CBrighton]

Yeah.

I'm not bothered if it's a database on a timer event or a batch file / set of batch files in scheduled tasks, as long as I'm able to run certain macros within certain databases without manual intervention.

These databases (most of which are just "open database, run macro, enter date at prompt") take up half of my day to manually run when they really don't need to.