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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.