Solved

Using VB script to run Access macro at specific time

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

939 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

8 Experts available now in Live!

Get 1:1 Help Now