FTP : VBA Solution needed

Hello there,

As a company we email a lot of joblistings to jobboards via my bespoke database solution. However, one particular client is insisting we FTP the file to their site.

I've never used FTP in Access/VBA enviornment - how is it done ?

Many thanks

S.S.
SpencerSteelAsked:
Who is Participating?
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.

Gustav BrockCIOCommented:
Watch for my suggestion here:

  http://www.experts-exchange.com/Databases/MS_Access/Q_21049493.html

This is a proven method.

For single file transfer, also this:

  http://www.experts-exchange.com/Databases/MS_Access/Q_21048082.html

/gustav
0

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hmmm ... if this is really simple, you can use the DOS-based ftp commands. You just write a batch file that runs each command in sequence, at which point the file is uploaded. For example

FTP Host "YourServerName"
FTP CD\YourDirectoryOnTheServer
FTP -s: "YourFile"

It's been a long time since I used this, however, and you'll likely have to play around.

You can also purchase controls that will integrate with ACcess (from Mabry) that handle this for you. Additionally, check the VB sites for more info on this ... while VB uses a control to do this, many of the diehards still write their own code that access the API for this. Check::

www.thevbzone.com
vbnet.mvps.org

If you want to see the ftp commands avaialbe to you, run a command prompt (Run - "cmd") and type "ftp -?" ... the listing I get is below:

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Scott>ftp -?

Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.

FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-w:windowsize] [-A] [host]

  -v             Suppresses display of remote server responses.
  -n             Suppresses auto-login upon initial connection.
  -i             Turns off interactive prompting during multiple file
                 transfers.
  -d             Enables debugging.
  -g             Disables filename globbing (see GLOB command).
  -s:filename    Specifies a text file containing FTP commands; the
                 commands will automatically run after FTP starts.
  -a             Use any local interface when binding data connection.
  -A             login as anonymous.
  -w:buffersize  Overrides the default transfer buffer size of 4096.
  host           Specifies the host name or IP address of the remote
                 host to connect to.

Notes:
  - mget and mput commands take y/n/q for yes/no/quit.
  - Use Control-C to abort commands.
0
SpencerSteelAuthor Commented:
wow ... this sounds really interesting. First of all, as this is very, very simple and to be hardcoded into the app with no user intervention, i'm going to *try* the COMMAND LINE method (one question - dumb question - is there a way of running command lines from within VBA, or do I just open a .cmd file in a shared folder)

If this isn't enough I will be looking at the Gustav solution ...

I'll keep you posted.

Many thanks both

S.S.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Well ... You put your FTP commands in a simple text file:

Host "URL to your FTP"
USER "scott"
PASS "scott"
CWD "\YourDiretory"
CDW "\NewDirectory"
STOR "TheFullPathToYourFile"
etc etc

then call the FTP with Shell"

Shell "ftp s:PathToYourFileAbove"

This site lists the available FTP commands:
http://www.nsftools.com/tips/RawFTP.htm#PASV

Note that not all FTP servers support all commands; you may have to try certain combinations before ensuring this will work. Also, the Access Web has provided a "wrapper" of sorts to this library:
http://www.mvps.org/access/modules/mdl0037.htm

0
SpencerSteelAuthor Commented:
We are very close here ... can't get it to accept a PASSWORD though ...

Have to use this .... (from Access) (for some reason it doesn't like HOST, have to provide FTP site explicitly)

Shell ("ftp -s:c:\FTPtest.txt 195.10.229.4")

In my small FTPtext.txt file, I have

USER myUser
PASS myPass

It bombs out with the login failed for user myUser ... even though the password is correct.

Let me know ...

Thanks

S.S.



0
Gustav BrockCIOCommented:
If the command line level is fine for your purpose, don't waste your time with the native Windows FTP - as you already have seen, error handling is a nightmare.
Having had the troubles with it, I wrote the VBA module for wcl_ftp which you will find a link to as well at the previously posted link. Wcl_ftp will provide the user with a small progress windows listing common errors - much more convenient than a msgbox "Unknown FTP fail. Please try again."

/gustav
0
SpencerSteelAuthor Commented:
Getting 'get' or 'put' are not valid integer types (depending on what I use) - is there a -1/0 equiv ?

Looks dead useful though, thanks

S.S.
0
SpencerSteelAuthor Commented:
ignore last post - my mistake - this is very cool ... will now play with it a bit more - I will split points now and take any support issues to Gustav as you have both helped enormously, although I recommend Gustav's tool from my findings so far.

Peace.

S.S.
0
Gustav BrockCIOCommented:
> Getting 'get' or 'put' are not valid integer types (depending on what I use) - is there a -1/0 equiv ?

That's right and neither do I like it.
But get my module - it's all taken care of:

Public Function RunWclFtp( _
  ByVal strHostname As String, _
  ByVal strUsername As String, _
  ByVal strPassword As String, _
  ByVal strHostDirectory As String, _
  ByVal strHostFilename As String, _
  ByVal strLocalDirectory As String, _
  ByVal strLocalFilename As String, _
  ByVal booUpload As Boolean, _
  ByRef intErrorCode As Integer, _
  Optional ByVal booBinary As Boolean = True, _
  Optional ByVal booPassiveMode As Boolean = False, _
  Optional ByVal strWindowCaption As String, _
  Optional ByVal booWriteFileLog As Boolean = False, _
  Optional ByVal booWriteFileError As Boolean = True, _
  Optional ByVal booAutoClose As Boolean = True) _
  As Boolean

  <snip>

  Const cstrCommandUpload         As String = "put"
  Const cstrCommandDownload       As String = "get"

/gustav
0
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.