Link to home
Start Free TrialLog in
Avatar of MDSS
MDSS

asked on

Access Shell Command on 64 Bit Computer

I am trying to run the following shell command in Access 2013 without success. (This works fine on a 32 bit machine)

 I set the Sandbox setting to 0.

How do I do this?

Dim RetVal
RetVal = Shell("C:\FT2\FTPTransferData.bat", 0)
Avatar of Nick67
Nick67
Flag of Canada image

I'd do it like  this
Shell "cmd /c C:\FT2\FTPTransferData.bat"
The returned value is of little use
No reason Shell() should not work under 64 bit.  Something else is going on.

What are you seeing?

Jim.
Avatar of MDSS
MDSS

ASKER

Nothing happens when I run it on a 64 bit machine ... no error, it just doesn't execute the bat file .... when I go to the Command Prompt and execute it outside of Access, the bat file executes fine.   Also, when I execute the Shell command on a 32 bit machine, it executes fine from within Access.
64 bit Office?
64 bit Windows?

What I gave you works on 32/64 bit Windows with 32 bit Office.
64 bit Office and WinAPI -- which is what Shell ultimately is -- have quirks.

Please clarify '64 bit'
Has to be security.

What version of Office 32 or 64 bit) and what OS?

Jim.
Avatar of MDSS

ASKER

It's a 64 bit Windows machine running 32 bit Office Access 2013 ( located in the Program Files (x86) section)
What version of Office are you running?  Not that it should matter with Shell, but just checking.
Avatar of MDSS

ASKER

I'm using Office 2013 (Access 2013)
Well.

Does Shell fail or the batch file?
Let's try
Shell "c:\windows\system32\cmd.exe"

Will that fire?
And I suppose we could cheat up a batch

Dim WshShell as object
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd /c C:\FT2\FTPTransferData.bat"
WshShell.AppActivate "c:\windows\system32\cmd.exe"
Set WshShell = Nothing
You might want to try ShellExe, you can find the code here:

http://access.mvps.org/access/api/api0018.htm

I had to modify the declaration as  below to work with 64 bit office:
#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Open in new window

Avatar of MDSS

ASKER

When I execute the following, it opens the cmd window and begins to execute the bat file, but it doesn't complete the bat file and it leaves the cmd window open.

Shell "c:\windows\system32\cmd.exe /k  C:\FT2\Transferdata.bat"
Switch /k is meant to do that (leave the window open)
Good!
Shell isn't busted.

Now, start throwing PAUSE statements into your batch file and see why it falls down on this machine when it is executed by the system rather than the user.
Avatar of MDSS

ASKER

I need the switch of /c or /k in order for it to execute the bat file .... otherwise it simply opens the command window.

The bat file executes the following ftp script:

open ftp.test.net
0124959|testworld
testaccess2!
cd /capture
binary
get capture.csv c:\ft2\capture.csv
quit


The problem occurs just after the get command... when I run the bat file outside of Access I get this:

200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 2150 bytes received in 0.08 seconds  27.56Kbytes/sec.
ftp: quit
221 Bye

From within Access I get this:

200 PORT command successful.
150 Opening BINARY mode data connection.

Then it just hangs there until I close the command window
When I do that, I need two files, an FTP file and a batch file.
While you can specify the FTP file in the batch, it doesn't respect that from the command line

If have this function to use Ping
Public Function Ping(strHost)
    Dim oPing, oRetStatus, bReturn
    Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select * from Win32_PingStatus where address='" & strHost & "'")
 
    For Each oRetStatus In oPing
        If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
            bReturn = False
 
            ' WScript.Echo "Status code is " & oRetStatus.StatusCode
        Else
            bReturn = True
 
            ' Wscript.Echo "Bytes = " & vbTab & oRetStatus.BufferSize
            ' Wscript.Echo "Time (ms) = " & vbTab & oRetStatus.ResponseTime
            ' Wscript.Echo "TTL (s) = " & vbTab & oRetStatus.ResponseTimeToLive
        End If
        Set oRetStatus = Nothing
    Next
    Set oPing = Nothing
 
    Ping = bReturn
End Function

Open in new window


I check if the host is up
If Ping("10.0.1.10") = False Then
    MsgBox "The recorder is off, and the files cannot be refreshed"
    Exit Sub
End If

Open in new window


I then run
Shell "cmd /c x:\pressuretest\ProgramFiles\ftpGetList.bat x:\pressuretest\ProgramFiles\ftpGetList.txt"
Here's ftpGetList.bat

x:
cd \
cd PressureTest
ftp.exe -i -v -n -s:x:\pressuretest\programfiles\ftpGetList.txt

Look at the -s switch. It's there, but not respected
Here's the contents

open 10.0.1.10
user NAME PASSWORD
cd sdmmc
cd vrd
ls . list.csv
dir . dir.csv
bye
Avatar of MDSS

ASKER

Yes, I have two files....

The bat file (FTPTransferData.bat) looks like this:

@ECHO On
ftp -s:c:\ft2\FTPDownloadData.txt


The script file (FTPDownloadData.txt) looks like this:

open ftp.test.net
 0124959|testworld
 testaccess2!
 cd /capture
 binary
 get capture.csv c:\ft2\capture.csv
 quit
It's not enough to Shell the batch file in automation
It wants the other file too

Shell "cmd.exe /c  C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt"
or
Shell "cmd.exe /k  C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt"

should get 'er dun if those are the correct paths
Avatar of MDSS

ASKER

Same result as before ....

It hangs up on

200 PORT command successful.
 150 Opening BINARY mode data connection.

when running it manually it gives me:

200 PORT command successful.
 125 Data connection already open; Transfer starting.
 226 Transfer complete.
 ftp: 2150 bytes received in 0.08 seconds  27.56Kbytes/sec.
 ftp: quit
 221 Bye
Well, clearly your FTP file isn't doing the job

Note that mine has
user username password
This is very different
0124959|testworld
and what is this meant to do?
testaccess2!

But I think the grief is here
get capture.csv c:\ft2\capture.csv

These are mine
get "180314REES~DS.D05"

Throw some quotes in and see if it goes!
Avatar of MDSS

ASKER

But it works perfectly when executed outside of Access
Avatar of MDSS

ASKER

So this is what I know:

From the Run screen the following works correctly:

C:\RT2\FTPTransferData.bat

From the Run screen the following works correctly:

C:\Windows\System32\cmd.exe /c C:\RT2\FTPTransferData.bat C:\RT2\FTPDownloadData.txt

From within Access the following gets hung up:

Shell "C:\Windows\System32\cmd.exe /c C:\RT2\FTPTransferData.bat C:\RT2\FTPDownloadData.txt"
<<It's not enough to Shell the batch file in automation
It wants the other file too>>

You can do it either way and I do it the other way, specifying  a script switch (-s) as part of the batch file and only call the batch file.  Works fine.

What it is is the pipe delimiter (vertical bar) in the username.

A vertical pipe inside of a batch file is not a good idea.   Change the user name.

And no points please.

Jim
Quotes around the username may get around the issue as well.

Jim.
ASKER CERTIFIED SOLUTION
Avatar of MDSS
MDSS

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<Turns out there was nothing wrong with the original bat or script file .... the standard Microsoft FTP client causes the error ... I replaced it with WinSCP and it works fine.>>

 I would disagree. I have used the standard Microsoft, MoveIt, and WinSCP clients for many years and have never had a problem with any calling them from Access.

 Something in the batch file is an issue for the Microsoft client.

Jim.
the standard Microsoft FTP client causes the error

Not really, since my posted examples are using the standard Windows FTP client and work fine.
How's about
'the syntax of my batch and ftp files doesn't play nicely with running it through Shell'

Glad you got it figured out, though
Avatar of MDSS

ASKER

It was the only solution offered that worked to solve my problem.