Solved

FTP : VBA Solution needed

Posted on 2004-09-01
9
425 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 49

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 49

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 49

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

19 Experts available now in Live!

Get 1:1 Help Now