Solved

FTP : VBA Solution needed

Posted on 2004-09-01
9
432 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
type of query 11 41
Modal form 11 29
date criteria to pull up records for the last 3 months 39 38
Access Open Report with SQL Parameter 11 29
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

786 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