• 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
0
davetough
Asked:
davetough
2 Solutions
 
wshark83Commented:
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.OpenCurrentDataBase("C:\<databaselocation>\<filename>")
accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing
msgbox "Job complete", 64
0
 
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?
...etc

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

JeffCoachman
0
 
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:

http://support.microsoft.com/kb/209207
0
 
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...

;-)

Jeff
0
 
davetoughAuthor Commented:
thanks for help
0

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