Solved

open and close database - automate with macro

Posted on 2012-03-15
5
252 Views
Last Modified: 2012-03-15
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
Comment
Question by:davetough
5 Comments
 
LVL 6

Accepted Solution

by:
wshark83 earned 300 total points
ID: 37724940
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37725033
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 37725283
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37725459
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
 

Author Closing Comment

by:davetough
ID: 37725603
thanks for help
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question