Solved

Using VB script to run Access macro at specific time

Posted on 2010-09-14
5
670 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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now