Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old October 25th, 2007, 09:51 AM
Authorized User
Join Date: Oct 2007
Location: Buffalo, NY, USA.
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.

Reply With Quote
  #2 (permalink)  
Old October 26th, 2007, 06:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old October 26th, 2007, 08:26 AM
Authorized User
Join Date: Oct 2007
Location: Buffalo, NY, USA.
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!

Reply With Quote
  #4 (permalink)  
Old October 26th, 2007, 09:35 AM
Authorized User
Join Date: Oct 2007
Location: Buffalo, NY, USA.
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!
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 10: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 04:58 AM
c-shell script timpeters C# 0 October 21st, 2003 12:08 AM

All times are GMT -4. The time now is 03:44 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.