Solved

Using VB script to run Access macro at specific time

Posted on 2010-09-14
5
674 Views
Last Modified: 2012-05-10
Hi,

This is part of a project that I am doing. I am at the stage where I have successfully automated the import of .csv files into an Access database (with lots of help from EE). Now I want to automate the process so that every night at say 2:00 AM a VB script runs and opens the Access 2007 database and runs a macro (which calls the VBA code to update the database) and then closes.

I believe that this should be pretty easy to operationalize but I have no experience working with VB scripts at all so any help that I can get would be most appreciated.

Thanks,
scurvylion
0
Comment
Question by:scurvylion
[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
  • 3
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33672982
you can do this using a .bat file that you can schedule to run using the  Scheduled Tasks from Control panel

in Notepad type , something like this

@echo off
"C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE" "C:\FolderName\yourDB.mdb"  /Excl /X yourMacro


save it as .bat file

create a macro, save as yourMacro  or any name with this codes

Action
Runcode


Function Name   yourFunction()


yourFunction is the name of the function that update the database) and then closes.





0
 

Author Comment

by:scurvylion
ID: 33674119
Hi capricorn1,

It ran great but the database didn't close and the svchost.exe continued to run. is there another command that I need in this script file or is the error in my VBA code in the macro?

scurvylion
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33674174
if your vba codes does not include to close the application

docmd.quit   ' you can add this at the end of the vba code

or

in the macro

action
runcode
quit           'add this
0
 

Author Comment

by:scurvylion
ID: 33675426
Works great! Thanks for your help - appreciate it very much!
0
 

Author Closing Comment

by:scurvylion
ID: 33675433
Thanks!!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 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