Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 25th, 2007, 09:51 AM
Authorized User
Join Date: Oct 2007
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using Shell to invoke cmd script with argument

I have a query i am trying to make available to users via a simple form that does not run in Access, but does run when submitted to a DB2 UDB prompt... I have the query saved in a .cmd routine that accepts an argument to establish date criteria.

I would like to allow my users to run the query by clicking a button. Is there a way I can use the Shell command (or another command) to invoke my .cmd script, while passing a date argument collected by an input box? I have not found any articles/documentation detailing a way to do this.

Thanks in advance.

Old October 26th, 2007, 06:46 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Where do you want to do this from? You could use the following VBScript, and package it in an HTA:

'================================================= =======
Dim sInput

sInput = InputBox("Please enter a date in the format...")

'Check for proper date format here then type the variable like this:
sInput = CDate(sInput)

Set objShell = CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec _
    ("%comspec% /c MyRoutineName" & sInput)

'================================================= =======

This will run the script and pass an argument. Comspec is the universal name for cmd and will call cmd in any version of Windows.

Did that help any?


Look it up at: http://wrox.books24x7.com
Old October 26th, 2007, 08:26 AM
Authorized User
Join Date: Oct 2007
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts

I think you've got me thinking in the right direction...

I want to run this command by clicking a button in an Access form. The Access DB will be out on a network drive... The .cmd script will be stored in the same directory as the database.

I tried running the routine you wrote up. It did open cmd.exe and it tried to run my script... the thing is, this script needs to run through db2cmd.exe. When i run through cmd.exe, it creates a file (in the directory i am trying to write query results to) containing this error message:

DB21061E Command line environment not initialized.

I registered a new environment variable through windows:

Variable: DB2COM
Value: C:\Program Files\SQLLIB\bin\db2cmd.exe

I modified the script you wrote to call DB2COM instead of COMSPEC, and i get an error:

Run-time error '-2147024894 (80070002)';
The system cannot find the file specified.

The program looks like this now:

sInput = InputBox("Please enter date in MM/DD/YYYY format:", "Input Required")

sInput = CDate(sInput)

Set objShell = CreateObject("WScript.Shell")
Set objExecObject = objShell.Exec("%db2com% /c \\homeserver\shared\AcctSvcs\Jake\ee-household.cmd " & sInput)
Can you see anything that i'm doing wrong?

What is an HTA?

Also, once i get this query to work, i'll want the VBA program to wait for the .cmd script to complete before continuing on to other commands that i'll use to import the query results. Any suggestions on how to get the VBA program to pause?

Thank you very much!

Old October 26th, 2007, 09:35 AM
Authorized User
Join Date: Oct 2007
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts

I have been reading up some more on related topics... i may have an alternative method to get this done. Instead of using one .cmd script designed to run within the db2 command prompt, i am experimenting with using two scripts:

db2cmd -c -w -i %1.bat %2

if "%1" == "" goto noparm1
set Date=%1

db2 connect to <db> user <uid> using <pwd>

db2 -x "select amount, chargeaccountnum, date(decisiontimestamp) from <table> where date(decisiontimestamp)='%Date%' and chargeaccountnum='001845969' and substr(captureseqnum,1,3)='003' and substr(depositaccountnum,1,3)='001' order by amount desc" >> c:\temp\eeHousehold\hh.txt

db2 disconnect <db>
goto exit1

echo error!


when i call:

prompt>initdb2 runreport 10/25/2007

from a standard cmd prompt, this works perfectly.

i have not been able to figure out a way to get this to run when called from VBA. I changed the Exec call to:

Set objExecObject = objShell.Exec("%comspec% /c \\homeserver\shared\AcctSvcs\Jake\initdb2.cmd runreport " & sInput)
When i run this code, a cmd window opens and closes, but nothing else happens.

Any ideas? Thanks!

Similar Threads
Thread Thread Starter Forum Replies Last Post
where is shell script saved on unix box crmpicco Linux 2 August 3rd, 2007 12:56 PM
how to invoke the dll in ASP.NET 2.0 kanmani .NET Framework 2.0 0 January 15th, 2007 11:34 PM
how to call shell cmd to open exe on clientside? nana Javascript How-To 6 May 25th, 2006 03:23 AM
Shell Script techSupport C++ Programming 0 March 10th, 2005 05:58 AM
c-shell script timpeters C# 0 October 21st, 2003 12:08 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.