Running VBA macro within Access 2010 from external VBScript

Question

Not sure this is possible, but it seems like it should be doable.... it is 2013 after all! And some forum posts I found suggest it is, but I have not been successful in getting it to work...

I have an Access 2010 db with a macro that downloads files from 3 different web sites and then proceeds to import the data and process it. It runs for 1hr and 17 mins.

I want to schedule this macro to run at 4am so it is all done and dusted by coffee time and work start 8am... So I created a VBScript to run this, which will then be placed into the Task Scheduler for the desired time.

It is a single user DB, my use only on my PC.

I have done some research but I cannot seem to get this working. Here's what I have so far:

Macro inside "Main" module in Access 2010 inside of "Pricing Model.accdb":

Public Sub Download_And_Import()
    ProcStep = ""
    ExecStep = 1
    DoCmd.SetWarnings False
    'Empty the Execution Progress table
    DoCmd.RunSQL "DELETE * FROM EXECUTION_PROGRESS"
    Call Update_EXECUTION_PROGRESS(Format(Now(), "YYYY/MM/DD HH:MM:SS"), ExecStep, "Starting Download_Files Main Procedure...")
    Call Download_Files.Download_Files
    Call Update_EXECUTION_PROGRESS(Format(Now(), "YYYY/MM/DD HH:MM:SS"), ExecStep, "Finished Download_Files Main Procedure...")
    Call Update_EXECUTION_PROGRESS(Format(Now(), "YYYY/MM/DD HH:MM:SS"), ExecStep, "Starting Import_Files Main Procedure...")
    Call Import_Files.Import_Files
    Call Update_EXECUTION_PROGRESS(Format(Now(), "YYYY/MM/DD HH:MM:SS"), ExecStep, "Finished Import_Files Main Procedure, closing application now...")
    DoCmd.SetWarnings True
End Sub

I then created the following VBScript to run Access VBA Macro externally:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("G:\Pricing DB\Pricing Model.accdb")
accessApp.Run "Download_And_Import"
accessApp.Quit
set accessApp = nothing

I get the message "Illegal function call, Line 4" which is the step:

accessApp.Run "Download_And_Import"

Any ideas / help is highly appreciated! Thanks in advance!

M

Code worked fine for me after I defined the variables ProcStep and ProcExec. Make the application visible (accessApp.Visible = True), so you can use the macro editor for debugging. — Ansgar Wiechers
– Ansgar Wiechers, Commented Jul 14, 2013 at 9:32
I'm unable to test this right now, but try adding parentheses: accessApp.Run ("Download_And_Import"). — UberNubIsTrue
– UberNubIsTrue, Commented Jul 14, 2013 at 13:11
Hi Ansgar Wiechers, I am sorry but I am new to VBS (although I have a bit of VBA experience). On your suggestion, when I add the line (accessApp.Visible = True) Access opens up when I run the VBS but I still get an Illegal Function Call msg. I am not sure what you are referring to with defining ProcStep and ProcExec, can you elaborate pls. Thanks for the help. — TheRealPapa
– TheRealPapa, Commented Jul 14, 2013 at 23:55
Hi UberNubIsTrue, I tried your suggestion and I get an "Illegal Function Call" message on line 4 (the Macro run call). Thanks! — TheRealPapa
– TheRealPapa, Commented Jul 14, 2013 at 23:56
@user2469528 - The only other suggestion I could throw out there is to make sure your Sub is not named the same as the module it resides in. This has thrown errors for me in the past when trying to use application.run. — UberNubIsTrue
– UberNubIsTrue, Commented Jul 15, 2013 at 11:59

HansUp · Accepted Answer · 2013-07-14 21:19:20Z

3

I don't see anything wrong with the VBScript itself. I copied your code, changed the db file name and procedure name, and it ran without error. So my guess is the VBScript error "bubbles up" from Access when it tries to run the procedure.

Open Pricing Model.accdb in Access, go to the Immediate window (Ctrl+g), type in the following line and press Enter

Application.Run "Download_And_Import"

If that throws an error you surely have a problem in that procedure. If it doesn't throw an error, you could still have trouble running it from a scheduled task if the procedure requires Windows permissions and you haven't set the task to run under your user account.

If you need to troubleshoot the procedure, first add Option Explicit to the module's Declarations section. Then run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about. Repeat until the code compiles without error.

Disable DoCmd.SetWarnings False during troubleshooting because it suppresses information. Consider whether substituting the DAO Database.Execute method for DoCmd.RunSQL will allow you to avoid turning SetWarnings off at all.

answered Jul 14, 2013 at 21:19

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheRealPapa Over a year ago

Hi Hans Up, thanks for the suggestion. I tried your suggestion (Ctr+G) and worked just fine. There are no errors in the Access VBA when I execute it manually (or via Ctrl+G). By the way I am trying to run the VBScript by double clicking it. Once it works manually I will schedule it, so I have not made it past that manual step yet. As such it is running with my access rights (or trying to!). Thanks again.

HansUp Over a year ago

Try running the script from a command window using cscript YourScriptName.vbs I'm not confident that will give us any better information, but I have nothing else to suggest.

Andy G · Accepted Answer · 2013-07-14 14:09:17Z

0

You could try including the ProjectName:

accessApp.Run "[Pricing Model].Download_And_Import"

The project-name defaults to the database name, and square-brackets are needed because of the spaces.

This shouldn't be necessary, because your Sub is Public, but worth a try.

answered Jul 14, 2013 at 14:09

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

5 Comments

TheRealPapa Over a year ago

Hi Andy G, thanks for the suggestion, but it did not work. It threw out the error "MS Access cannot find the procedure [Pricing Model]". By the way I am running the VBS by double clicking it. Once it works manually I will schedule it.

Andy G Over a year ago

What does Update_EXECUTION_PROGRESS do and where is it stored?

TheRealPapa Over a year ago

Hi Andy G, Update_EXECUTION_PROGRESS is essentially a procedure to update a table in the same DB where I store progress messages. Thanks.

Andy G Over a year ago

Does it run the deletion query at least? I would try commenting out some of the other procedure calls. Debugging: break it down to isolate the problem ;). The illegal function call can refer to one of these lines, when run externally from a script.

TheRealPapa Over a year ago

Hi Andy G, thanks for the suggestion, but the VBA piece works end to end perfectly. I have run it manually many times now and completes OK. The "illegal function call" msg I get points to the VBScript line 4 where I try and run the VBA inside the Access DB (accessApp.Run "Download_And_Import"). I even tried commenting all entries in my main Access VBA (Download_And_Import) and placed only a MSGBOX statement to see if the VBScript triggers it, same illegal function call msg comes back, pointing to line 4 in VBS.

Marcos J.D Junior · Accepted Answer · 2017-04-14 18:16:01Z

0

I ran into the same (msg) issue the script was OK! Problem was i was testing the script with the database opened as I was in development mode, as soon i closed the Database the script worked well.Close the database before testing the script.

answered Apr 14, 2017 at 18:16

Marcos J.D Junior

3171 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

Running VBA macro within Access 2010 from external VBScript

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related