Check if a file exists on FTP, answer with yes or no

We have a system who storing files on a local server, where a part of the filenames contains an unique 8 char code.
This unique 8 char code is also stored in an access database.

Now I search for a function who:
1. On a click, makes this Access database connect to this local server through FTP (yes, ftp is a must).
2. Check if there is a filename having this unique 8 char code.
3. Tell Yes if the file exists, No if it doesn't exist.

And an overkill, but very useful if above is very easy:
4. If yes, click again and the file are transferred and runs with the computers associated software.

Is this possible? How do I do :)
LVL 1
dingirAsked:
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.

Jim P.Commented:
I've only have ever been able to shell FTP commands.  Someone pointed out this site to me to do FTP with api calls.
http://vbnet.mvps.org/index.html?code/internet/ftplist.htm

I have never used any of it, so I am not the expert to ask on this.
harfangCommented:
Hello,

I'm sure there is a better way, but you can always do this, from a batch file or a Shell command:

    ftp -v -s:test.ftp ftp.site.com | find "filetocheck.txt" | find /v "ftp> " > test.out

test.ftp contains this (which can be written from VB, naturally)
-----------------------------
username
password
ls filetocheck.txt
quit
-----------------------------

After running the command, the file test.out will contain the name of the matching file, or nothing. It's easy to read that back into VB to proceed

A similar technique can be used to retrieve the file.

Hopw this helps
(°v°)
dingirAuthor Commented:
Hi Jimpen! I'm about to trying this now.. I'm not sure how I get it into vbscript. I try to use Call Shell(..) as I use in some other similiar cases (just simple upload/download actions). You have any working vbscript-examples?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dingirAuthor Commented:
Hi harfang! Thank's something like that - putted in a Shell, but I can't get that find-command works.. :/
harfangCommented:
Ah, I knew it. Couldn't figure it out? There must be 1000 ways of doing this, but here is one:

Try ? CheckFTP("*.txt")
--------------------------------------------------------------------------------------
Option Explicit

Const FTP_SITE = "ftp.microsoft.com"
Const FTP_LOGIN = "anonymous" & vbCrLf & "i.am@testing.info"
Const FTP_PATH = "deskapps/ACCESS/KB"

Function CheckFTP(pstrFile As String)

    Dim strLine As String
   
    ChDir Environ("TEMP")
   
    ' ftp script:
    Open "~~check.ftp" For Output As #1
    Print #1, FTP_LOGIN
    Print #1, "cd " & FTP_PATH
    Print #1, "ls", pstrFile
    Print #1, "quit"
    Close #1
   
    ' batch file:
    Open "~~check.bat" For Output As #1
    Print #1, "ftp -v -s:~~check.ftp " & FTP_SITE _
        & "|find/v""ftp>"">~~check.out"
    Print #1, "del ~~check.ftp"
    Print #1, "del ~~check.bat"
    Close #1
   
    ' run batch
    CheckFTP = Null
    If Shell("~~check.bat") = 0 Then Exit Function
    Do While Dir("~~check.ftp") <> ""
        DoEvents
        ' should add a timer here...
    Loop
   
    ' read results...
    Open "~~check.out" For Input As #1
    Do Until EOF(1)
        Line Input #1, strLine
        strLine = Trim(strLine)
        If strLine <> "quit" And strLine <> "" Then _
            CheckFTP = CheckFTP + ";" & strLine
    Loop
    Close #1
    Kill "~~check.out"
   
End Function

More about FTP scripting at http://www.ericphelps.com/batch/samples/ftp.script.txt

Cheers!
(°v°)

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
dingirAuthor Commented:
PERFECT! What a nice solution. Thank's a lot :)
harfangCommented:
Glad you like it. It's low-tech (and shows my age) but it does seem to work...
Good luck!
(°v°)
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.