Solved

open and close database - automate with macro

Posted on 2012-03-15
5
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

724 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