Running Batch Files in Access 2000

[Ant]

Hi,
I have searched this site inside and out on information about running .bat
files through Access. The most common answer is to set a RunApp macro with
the batch files path in quotes as the Command Line. That works...for some of
my batch files. If I make a test batch file with something like ipconfig in
there, it will run. My FTP batch files, however, will not. There are no
spaces in my file path (I:\Ant\NewSources.bat) and all it does is open and
close lightning fast. If I run the process on my own, it will run. Just
trying to add a little more automation to the job! haha, thanks in advance.

 

[bhicks11 via AccessMonster.com]

Hi Ant,

Running FTP is a little different. I do it this way:

EXAMPLE - start by running an VBA event:

Function FTPin()
On Error GoTo FTPinERR

Dim Mfile1, mfile2, Mfile3 As String
Dim Mall As String


DoCmd.SetWarnings False
Call BUtoC
'(just backing up my work first)


Call Shell("C:\YourFolder\PKZIP.EXE /c " & "C:\YourFolder\" & Format(Now(),
"yymmdd") & UCase(Left(fOSUserName(), 2)) & ".ZIP " & "c:\YourFolder\*.txt",
vbMaximizedFocus)

'(zip my work and copy to a file named for ID and date so we don't overwrite)

Call Shell("C:\YourFolder\Submit.bat", vbMaximizedFocus)

'(see Submit.bat below)

FTPinERR:
'MsgBox ERROR$
Resume FTPinEXIT

FTPinEXIT:
Exit Function

End Function


Here's 2 batch files:

SUBMIT.BAT

ftp -s:c:\YourFolder\upftp.bat ftp.YourFTPInfo.net

copy c:\YourFolder\*.zip c:\AnyFolder\sent

' (I am making a copy of my sent work to a folder)

del c:\YourFolder\*.zip

'(clean for the next upload)


UPFTP.BAT

YourFTPsite
YourPassword
lcd c:\YourFolder
binary
prompt
mput *.zip

bell
bye

This ought to save you some time!


Bonnie
http://www.dataplus-svc.com

 

[Ant]

Sorry to sound ignorant, but I am so lost in all of that code! hahaha. All
my batch files do is connect to the FTP and collect extracted data to a text
file. I:\Ant\downloadnewsoureces.bat is the path. I click on that, and it
runs my second batch file that actually inputs the username/pwd and does all
of the real work. I'm quite the noob at VBA, and i apologize. Would I paste
this code in a button?

 

[bhicks11 via AccessMonster.com]

No Ant, you have to change the folders names, file names, etc. to match yours
and you may not want to do it just the way I did. Basically you need two
batch files. Try to follow the logic in the batch files and adjust yours.

Bonnie
http://www.dataplus-svc.com

Sorry to sound ignorant, but I am so lost in all of that code! hahaha. All
my batch files do is connect to the FTP and collect extracted data to a text
file. I:\Ant\downloadnewsoureces.bat is the path. I click on that, and it
runs my second batch file that actually inputs the username/pwd and does all
of the real work. I'm quite the noob at VBA, and i apologize. Would I paste
this code in a button?

Hi Ant,

[quoted text clipped - 71 lines]

 

[Ant]

Ok, I'll give that a shot. The way I have it set-up now, the DOS window says
"Error opening script file ucftp.bat." which is the second .bat file of the
two we are trying to run. If I do it manually, it runs without any problems.
I just made the RunApp macro and thats my result for it. I'll try your
method out and keep my fingers crossed!

 

[Ant]

OK, I got it to work with a few edits to the batch files. I first had to
take out any spaces. Then I wrote this quick instruction guide for everyone
here in the office:

How To Run Batch Files Through MS Access:

1)Make sure your file path has No SPACES
2)Make a macro
3)Set macro action to RunApp
4)In the command line, input your files path in quotes
(ex:"C:\TestFolder\test.bat") then save & name macro
5)If your batch file is running ftp, make sure you put the full path to
where you are saving the text file,including file name (ex:get DistroList.prn
i:\distrolist\DistroList.txt)
6)Drag and drop your macro to your form if you wish to run the macro via
button click.
7)Enjoy!

 

[Gigz1972]

OK, I got it to work with a few edits to thebatchfiles.  I first had to
take out any spaces.  Then I wrote this quick instruction guide for everyone
here in the office:

How ToRunBatchFiles Through MSAccess:

1)Make sure yourfilepath has No SPACES
2)Make amacro
3)Setmacroaction to RunApp
4)In the command line, input your files path in quotes
(ex:"C:\TestFolder\test.bat") then save & namemacro
5)If yourbatchfileis running ftp, make sure you put the full path to
where you are saving the textfile,includingfilename (ex:get DistroList.prn
i:\distrolist\DistroList.txt)
6)Drag and drop yourmacroto your form if you wish torunthemacrovia
button click.
7)Enjoy!

Looking to create the same results using Access 2003 in XP via Macros
to run a batch file that will delete files from within a givin
directory. The path of the directory is c:\tucker\clean.bat. in this
bat file the following exists
cd\
cd c:\tucker\
del *.csv
All I get from Access>Macros>RunApp>C:\Tucker\Clean.bat is a quick
flash of the CMD window and no results. Of course if I run it myself
in Start or by click the bat file works flawlessly. Any ideas? Anyone?

 

[bhicks11 via AccessMonster.com]

Mmm, maybe try leaving the \ of the path. Also, you can use:

Call ShellWait("cmd.exe /c Del c:\tucker\*.csv" )

Bonnie
http://www.dataplus-svc.com

OK, I got it to work with a few edits to thebatchfiles.  I first had to
take out any spaces.  Then I wrote this quick instruction guide for everyone

[quoted text clipped - 13 lines]

button click.
7)Enjoy!

Looking to create the same results using Access 2003 in XP via Macros
to run a batch file that will delete files from within a givin
directory. The path of the directory is c:\tucker\clean.bat. in this
bat file the following exists
cd\
cd c:\tucker\
del *.csv
All I get from Access>Macros>RunApp>C:\Tucker\Clean.bat is a quick
flash of the CMD window and no results. Of course if I run it myself
in Start or by click the bat file works flawlessly. Any ideas? Anyone?