[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

vb code to run shell and then pass results to a variable in msaccess

I am trying to do authentication of a user using ftp.  I have found in the cmd line that for cmd /k ftp -s:" & strPath the code 230 = Logged in and 530 = failed.  I want to read these results and grant permission to my database based on either of these numbers.  

I need to figure out how to write the shell results to a variable or txt file.  Preferably a variable in msaccess.

    If Not IsNull(Me.PWD) And Not IsNull(Me.CDSID) Then
    '    Write to ftp.txt
        Dim strFTP As String, strPath As String
        strFTP = "open xxxxxxxx.xx.xxxxx.com" & vbCrLf & _
            "yyyyyy/" & Me.CDSID & vbCrLf & _
            Me.PWD & vbCrLf & _
        strPath = "C:\WINDOWS\Temp\ftp.txt"
        Open strPath For Output As #1
        Print #1, strFTP
        Close #1

    '   Execute ftp.txt
        Shell ("cmd /k ftp -s:" & strPath)
    '   Write to variable
 >>>This is the part in question!!!
    '   Look for 230(logged in) or 530(failed)

    '   Delete ftp.txt
    End If

1 Solution
'Variables' disappear when Access is closed.  Just create a table with a field and use that as your 'variable'.
Gustav BrockCIOCommented:
You must write the output to a file which you then open and read in.
Here's the command line you need to build:

  ftp -s: scriptfile > ftp.log

When run, ftp.log is created (or written over) with the result you normally would see in the command window.

Gustav BrockCIOCommented:
Or more precisely:

You must redirect the output to a file which you then open and read in.

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Re-direct works, you will need to determine what kind of a time-delay you need in order to let the FTP complete before you go looking at the output file.

and another way that once you add the code it becomes very easy to connect to a server and get results from the connection or error code ...

woodan1Author Commented:
What would the line of code look like.  I'm new to shell in vb.

Shell ("cmd /k ftp -s:" & strPath & ">ftp.log") didn't work

this is the output in shell which I want stored in ftp.log:

ftp> open xxxxxxxx.xx.xxxxx.com
Connected to xxxxxxxx.xx.xxxxx.com
220 xxxxxxxx Microsoft FTP Service (Version 4.0).
User (xxxxxxxx.xx.xxxxx.com:(none)):
331 Password required for xxxxx/yyyyyy.

230-This is a Private Computer Network.
230-Unauthorized use will result in prosecution.
230 User xxxxx/yyyyyy logged in.
ftp> Quit
woodan1Author Commented:
Nevermind I got it to work.  I had to put the directory path.

Shell ("cmd /k ftp -s:" & strPath & ">C:\WINDOWS\Temp\xxx.log")
Gustav BrockCIOCommented:
Fine. Command lines can be tricky.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now