How to Run Scripts and Open Other Applications from MS Access Using VBA

In this episode, we're going to explore one way that we can use our MS Access app to start other applications and scripts, like our Python scripts that can give us all kinds of goodies. Once Access applications become more mature, they usually have some requirement to open other applications or files on the user's computer, to assist the user in their tasks. WScript.Shell allows us to use the Windows Script Host to open these resources for the user. Let's go and open some scripts from Access!
Join me on Patreon!
/ mackenziedataengineering
Want the code from this video?
mackenziemackenzie.com/downloads
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba #python

[@interestingamerican3100]

This channel has all sorts of golden nuggets! I was just asking myself this question the other day.

[@seanmackenziedataengineering]

Eventually we all run into that situation where we need to run something outside of Access!

[@RS-tx4bu]

As always, you over-delivered Sean! Excellent video. Thanks for the solution. Very powerful stuff.

[@seanmackenziedataengineering]

Much appreciated, thanks for the feedback!

[@garycurtis]

Hi Sean, thanks for the video. Question, I have two scripts to run, how would I write the code for that?

[@seanmackenziedataengineering]

In your button code, you can just make two lines with RunFromShell in it. One for each script you need to run. Note that tasks in those may execute asynchronously. The second may not wait for the first to finish before starting the second. I believe there is a switch to make it wait, or you can try other methods.

[@dbdata9836]

thanks

[@seanmackenziedataengineering]

Thanks for checking it out!

[@gerfer6261]

Hi SM Is it possible to run “saved Export” from vb script please (then able to use task scheduler)

[@seanmackenziedataengineering]

You can run vbs files from the Task Scheduler, but I have found it *much* better to always run your vbs in a bat or cmd file, then schedule the bat or cmd file in the scheduler. Basically, make a mybatch.txt file with one line, C:\mypath\myscript.vbs, then rename the txt file to mybatch.cmd (the icon will change). The schedule mybatch.cmd in your Scheduler. This adds to the stability of running vbs under the scheduler.

[@rebenreben2789]

Hi . l have run error (method run of object 'Iwshshell3'failed

[@seanmackenziedataengineering]

Can you post your code?

[@andriyos_]

Sorry, but work path not correct! *.bat file placed in wscript_shell folder but when run show folder "...\Documents"! How can runed *.dat file from wscript_shell folder?

[@seanmackenziedataengineering]

You can try changing the path to "myfile.bat", or "~\myfile.bat" to run from the same folder. Or ".\myfile.bat". Let me know how it goes!

[@andriyos_]

​@@seanmackenziedataengineering doesn't work because wrong paths! For example. The MS Access file is located in c:\Users\sean\Documents. The Batch file is located in c:\dev\wscript_shell. If you run the batch file without MS Access (from windows folder) we will see in the batch window c:\dev\wscript_shell>........ When run the batch file from MS Access we will see in the batch window c:\Users\sean\Documents>..... How to make it so that when run the batch file from MS Access, we see in the batch window c:\dev\wscript_shell - the folder where the batch file is located?

[@seanmackenziedataengineering]

@@andriyos_ Ah ok. You can add one line to batch file, at the very start: cd C:\dev\wscript_shell Then it will run everything from that directory and show it in the command line.

[@simonmaersk]

Hi Sean. Yet another useful video, as always - Thank you. I have tried using this exact technique, however for some reason, it won't run files that are located on my root drive, or in other app folders such as Program Files. It works perfectly fine when the files are located on the desktop or in Documents. Do you know of a way to fix this issue? Thanks

[@seanmackenziedataengineering]

This is almost always due to permissions issues. Either Access does not have permission to access those locations. Are you running O365?

[@simonmaersk]

@@seanmackenziedataengineering Hello again. Thanks for your response. I actually resolved the issue. I had forgotten to use the double-double quotes in the file path for the Shell.Run function 🤣

[@simonmaersk]

@@seanmackenziedataengineering I actually used your vbScript Deploy template to make an automatic updater for an Access front-end I made for the company I work for. I modified your script so that it now works as a launcher for the front-end itself. Basically, if file doesn't exist or is not up-to-date, then copy file from network to deploy folder. Then run it with the Shell.Run function. So basically when the front-end user opens the shortcut on their desktop, it will run the vbScript, which then launches the Access file. What is your opinion on this technique? It works fine on my own computer, but I'm worried that other people might run into permission issues

[@seanmackenziedataengineering]

@@simonmaersk Ah ok! That will do it. Glad it worked for you!

[@seanmackenziedataengineering]

@@simonmaersk If it works, perhaps test it and see how it does with end-users.

[@gerfer6261]

Handy tool📎 Like the way handle the vbs file Is there a way to compress or hide the script from the user please?

[@seanmackenziedataengineering]

Yes, you can hide the batch file window by using a zero as a second argument: objShell. Run strFile, 0 or, set back to visible run: objShell. Run strFile, 1

[@gerfer6261]

Give it a go Nice tip 📎🗳

[@gerfer6261]

Hi Sean How to run a query from vbscript please ( for automation purposes) using task scheduler

[@gerfer6261]

Hi Guru I use Task schedular to run VBS back up on handful of specific users Down side of it is user need to log on the day/ time for it to happen. Q = How can I use environ(“”username”) and use select statement to run the VBS file On Form “On load “ event using Case Statement please? ( limiting on selected users) Or any other way more effective🤔

[@seanmackenziedataengineering]

The nice thing of using VBS is that you can configure it to run anytime, and you really don't need to have any user open Access or anything like that. It sounds like you are not backing up files, but instead are selecting data from your Access db. When you use Windows Task Scheduler, there is a way you can specify for the script to "run whether user is logged on or not". You will choose the account that will execute the script. So, why not use an admin or service login? You can securely store credentials with the task. Then, by using one account, you can run the same task for all users. There are a couple of gotchas, but I can explain more with screenshots if you want to email me.

[@gerfer6261]

❤ thanks Admin rights is the issue