Hi,
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.

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?



mmcdonal

Look it up at: http://wrox.books24x7.com

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:

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!

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:

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

runreport.bat
-------------------------
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

:noparm1
echo error!

:exit1
-----------------------

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:

When i run this code, a cmd window opens and closes, but nothing else happens.

Any ideas? Thanks!