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

Posted on 2005-05-03
Last Modified: 2012-06-27
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" & 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

Question by:woodan1
    LVL 5

    Expert Comment

    'Variables' disappear when Access is closed.  Just create a table with a field and use that as your 'variable'.
    LVL 48

    Accepted Solution

    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.

    LVL 48

    Expert Comment

    by:Gustav Brock
    Or more precisely:

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

    LVL 39

    Expert Comment

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


    Author Comment

    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
    Connected to
    220 xxxxxxxx Microsoft FTP Service (Version 4.0).
    User (
    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

    Author Comment

    Nevermind I got it to work.  I had to put the directory path.

    Shell ("cmd /k ftp -s:" & strPath & ">C:\WINDOWS\Temp\xxx.log")
    LVL 48

    Expert Comment

    by:Gustav Brock
    Fine. Command lines can be tricky.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now