Solved

FTP : VBA Solution needed

Posted on 2004-09-01
9
437 Views
Last Modified: 2009-01-29
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.
0
Comment
Question by:SpencerSteel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 11950854
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 11950905
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
 

Author Comment

by:SpencerSteel
ID: 11950975
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84
ID: 11951105
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
 

Author Comment

by:SpencerSteel
ID: 11951305
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 11951472
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
 

Author Comment

by:SpencerSteel
ID: 11952461
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
 

Author Comment

by:SpencerSteel
ID: 11952557
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
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 11952607
> 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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

752 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