Using VB script to run Access macro at specific time


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.

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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


Function Name   yourFunction()

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

scurvylionAuthor Commented:
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?

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

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


in the macro

quit           'add this
scurvylionAuthor Commented:
Works great! Thanks for your help - appreciate it very much!
scurvylionAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.