• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

open and close database - automate with macro

Hello, is there a way to write a macro to close and open access 2007 database.
( I can have windows scheduler run a bat file to run macro at night)
thank you
2 Solutions
you can do this in the bat file to open the file:

"C:\Program Files\Microsoft Office\Office<xx>\MSACCESS.EXE" "C:\AccessDatbase\<databasename>.<mde/mdf>" /user <username>

which will open up the database for user <username>

or this written in a vbs script:

dim accessApp
    msgbox "Click ok to run batch job",64
   set accessApp = createObject("Access.Application")
accessApp.Run "TimeUpDate"
set accessApp = nothing
msgbox "Job complete", 64
Jeffrey CoachmanMIS LiasonCommented:
As always, remeber that opening any app "unattended" can cause issues.
1. What if the PC is turned off? (what if a day is missed)
2. What if an error occurs?

Remember, if something "goes wrong" in your automated system, you may not know about it for a while... and the error may keep compounding...

So you will have to be sure that you have Iron-clad error handling (and possibly "RollBacks") in place

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Agree with Jeff - be careful when automating Access to run on an unattended machine, and be sure to check regularly to insure your process is actually being run.

If you want to automatically run a macro from the scheduler, then you'll have to alter the suggestion by wshark83 a bit:

"C:\Program Files\Microsoft Office\Office<xx>\MSACCESS.EXE" "C:\AccessDatbase\<databasename>.<mde/mdf>" /x YourMacroName

Note you don't need the /user switch (this is only used with User Level Security), but you do need to identify the Macro you wish to run (which is what the /x switch does). Here's a list of MS Access command line switches:

Jeffrey CoachmanMIS LiasonCommented:
Yes, you can actually exclude my post for any Points consideration.
...as it was just an FYI, and not a solution per se...


davetoughAuthor Commented:
thanks for help

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now