MSACCESS and FTP Commands... How To..

Posted on 2007-10-16
Medium Priority
Last Modified: 2013-12-02
I need a onclick event to do the following

Filename = orderid
Open a FTP location i.e. ftp://website.com
Login username
login password
go to path
check if file exits orderid&".xml"
if exisits then download to local path d:\download
import xml file in to database as table
if not exist then close ftp and
msgbox "File does not exisit" <-- in access

Thats it..

Now I know I can creat a batch file that will run a .scr <scrtipt> file that can login to ftp etc.  but how do i pass a variable from access form to a batch file....  

instead of running a batch file from outside access, doesn't access have a way to access ftp itself?

Question by:Ricky11
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 20085149
<<Now I know I can creat a batch file that will run a .scr <scrtipt> file that can login to ftp etc.  but how do i pass a variable from access form to a batch file....   >>

  You create the file on disk, then execute it with the shell command. ie. something like the function below.

<<instead of running a batch file from outside access, doesn't access have a way to access ftp itself?>>

   Yes. See the code here:


  This is a FTP class that you can drop into Access and use without shelling out to the command line.  This is the route I would go.


Function FTPUploadFile(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUsername As String, strPassword As String, Optional strTransferType As String) As Boolean
        ' Procedure to upload file to FTP site.
        ' Sends e-mail to ITALERT if upload fails and returns false.
        Const RoutineName = "FTPUploadFile"
        Const Version = "1.1"
        Dim strFTPCommandFile As String
        Dim strFTPScriptFile As String
        Dim strFTPLogfile As String

        Dim lngHWnd As Long
        Dim intFileNum As Integer
        Dim strMailMessage As String
        Dim oXYR_SendMail As New XYR_SendMail

10      On Error GoTo FTPUploadFile_Error
        ' Generate file names
20      strFTPCommandFile = "\FTP_" & AppShortName() & ".txt"
30      strFTPScriptFile = "\FTP_" & AppShortName() & ".bat"
40      strFTPLogfile = "\FTP_" & AppShortName() & ".Log"
        ' Write command file
50      Open strFTPCommandFile For Output As #99
60      Print #99, strUsername
70      Print #99, strPassword
80      Print #99, "type " & IIf(strTransferType = "B", "binary", "ascii")
90      Print #99, "put " & Chr$(34) & strLocalFileName & Chr$(34) & " " & strFTPFilename
100     Print #99, "quit"
110     Close #99

        ' Write script file
120     Open strFTPScriptFile For Output As #99
130     Print #99, "@ftp -i -s:" & strFTPCommandFile & " " & strFTPSiteName & " > " & strFTPLogfile
140     Close #99

        ' Execute
150     lngHWnd = Shell(strFTPScriptFile, vbHide)
160     WaitWhileRunning (lngHWnd)

        ' Check log file
170     If IsValidFTP(strFTPLogfile) Then
180       FTPUploadFile = True
190     Else
200       If DebugMode() = True Then
210         Stop
220         FTPUploadFile = False
230       Else
240         oXYR_SendMail.SetParams "ITALERT", ".", "."
250         oXYR_SendMail.Subject = "FTP Upload failed"
260         strMailMessage = "The file: " & strLocalFileName & " did not upload to the FTP site: " & strFTPSiteName & " with username: " & strUsername & " password: " & strPassword & vbCrLf
270         strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
280         strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
290         oXYR_SendMail.Message = strMailMessage
300         oXYR_SendMail.Attachment = Dir(strFTPCommandFile) & ";" & Dir(strFTPScriptFile) & ";" & Dir(strFTPLogfile)
310         oXYR_SendMail.Send
320         FTPUploadFile = False
330       End If
340     End If

350     On Error Resume Next
360     If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
370     If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
380     If Dir(strFTPLogfile) <> "" Then Kill strFTPLogfile
390     Close #99
400     Exit Function

410     UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
420     FTPUploadFile = False
430     Resume FTPUploadFile_Exit
End Function

Author Comment

ID: 20239885
tks.. i just shelled out and ran a macro.. thanks.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 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