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)
MDSSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

What are you seeing?

Jim.
MDSSAuthor Commented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Nick67Commented:
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'
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Has to be security.

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

Jim.
MDSSAuthor Commented:
It's a 64 bit Windows machine running 32 bit Office Access 2013 ( located in the Program Files (x86) section)
Dale FyeOwner, Developing Solutions LLCCommented:
What version of Office are you running?  Not that it should matter with Shell, but just checking.
MDSSAuthor Commented:
I'm using Office 2013 (Access 2013)
Nick67Commented:
Well.

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

Will that fire?
Nick67Commented:
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
Dale FyeOwner, Developing Solutions LLCCommented:
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

MDSSAuthor Commented:
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"
Nick67Commented:
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.
MDSSAuthor Commented:
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
Nick67Commented:
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
MDSSAuthor Commented:
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
Nick67Commented:
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
MDSSAuthor Commented:
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
Nick67Commented:
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!
MDSSAuthor Commented:
But it works perfectly when executed outside of Access
MDSSAuthor Commented:
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"
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Quotes around the username may get around the issue as well.

Jim.
MDSSAuthor Commented:
Problem Solved ....

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.

Thanks anyway for your help and suggestions.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
Nick67Commented:
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
MDSSAuthor Commented:
It was the only solution offered that worked to solve my problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.