?
Solved

Using VB script to run Access macro at specific time

Posted on 2010-09-14
5
Medium Priority
?
682 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
  • 3
  • 2
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 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