current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question
1  
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 Jul 14 '13 at 9:32
    
I'm unable to test this right now, but try adding parentheses: accessApp.Run ("Download_And_Import"). –  UberNubIsTrue Jul 14 '13 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 Jul 14 '13 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 Jul 14 '13 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 Jul 15 '13 at 11:59
show 1 more comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  TheRealPapa Jul 14 '13 at 23:46
    
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. –  HansUp Jul 15 '13 at 2:19
add comment
up vote 0 down vote

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.

share|improve this answer
    
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. –  TheRealPapa Jul 14 '13 at 23:43
    
What does Update_EXECUTION_PROGRESS do and where is it stored? –  Andy G Jul 14 '13 at 23:55
    
Hi Andy G, Update_EXECUTION_PROGRESS is essentially a procedure to update a table in the same DB where I store progress messages. Thanks. –  TheRealPapa Jul 15 '13 at 0:00
    
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. –  Andy G Jul 15 '13 at 0:04
    
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. –  TheRealPapa Jul 15 '13 at 0:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.