[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

open and close database - automate with macro

Posted on 2012-03-15
5
Medium Priority
?
259 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 1200 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 800 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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

656 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