Access 2010 Automation

I want to automatically start up Access and run a sub procedure. The Access database runs an update process to a PLSQL database. Right now, I have to open the database, click a button, and wait for the process to complete. I'd rather be able to schedule a script that opens the database, clicks the button, and closes the database when the process is complete.

I attempted to write a batch file that will do this. Here's what I tried:

start msaccess.exe /ro "\\PathToAccessDB\UpdateDB.accdb" /x cmdUpdate_Click

The database starts up, but the problem is that the /x option expects a macro, not a subprocedure.
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
create a Function "fncUpdate"  in a regular module
 *copy the codes from the click event of the command button cmdUpdate and put them in "fncUpdate"

create a Macro, and save as "macroUpdate"

In the Action column, place


Function Name  fncUpdate()

place this in your batch file

start msaccess.exe /ro "\\PathToAccessDB\UpdateDB.accdb" /x macroUpdate

Dale FyeCommented:
so create a macro that kicks off the subroutine.

Make sure that your Click event is defined as Public, not Private, as you won't be able to call it from outside your form if it is not public.
just make a macro in your database and name it autoexe
the macro will run when you open the database : )
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

CazarAuthor Commented:
Creating a macro and a separate module works, but I was hoping there was a way to just run the existing subprocedure. I have other processes that I need to automate this way. I want to be able to run the processes manually by clicking a button or through scripting. If I have to create a separate module for the scripting, then I will have to keep the module version and the button click version in sync any time I make changes to the code. I'd rather avoid that maintenance overhead if possible.

I think the best solution might be to call the module function used by the macro from the click event of the button instead of giving the button it's own build event code. Both the button and the macro could use the same code then.
sorry i ment

name your macro

Dale FyeConnect With a Mentor Commented:
"I will have to keep the module version and the button click version in sync "

Not really, just call the function from the click event of your button.
call your function from your  "Autoexec" macro
Dale FyeCommented:
No, don't call the function from the Autoexec, unless you want this function to fire every time you open the application.
@ fyed i thought that was what he wanted. my bad
Rey Obrero (Capricorn1)Commented:

<Creating a macro and a separate module works>

make the click event of the command button Public. ie.,

Public Sub cmdUpdate_Click()  '<<

in the function call it like this

Function fncUpdate()


End Function

change "NameofForm" with the actual name of the form where your command button is

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.