Results 1 to 1 of 1
2003-06-17, 14:58 #1
- Join Date
- Aug 2001
- Spring City, Pennsylvania, USA
- Thanked 0 Times in 0 Posts
FTP Via ActiveX in a VBA App (VBA (Access 2000))
I had written last month to ask if anybody knew how to use an ActiveX object to be able to run FTP uploads from within an Access VBA application. (My Access app tracks hundreds of filenames, which can be selected via query. I then wish to be able to log into a particular FTP site and upload the chosen files.)
I received what I considered to be a very courteous and helpful reply (message 260446) from WENDELLB (gramps), one of the Access moderators. He referenced message 258760, which put me onto the MSINET.OCX ActiveX file that ostensibly handles HTTP and FTP calls.
I had this file to-hand, plus there's lots of copies on the web (latest version appears to be 220.127.116.11). I duly registered the ActiveX file, and added it to my Tools|References in Access, built a form and inserted the ActiveX thingie into it, but cannot get it to work but in a limited fashion. Maybe someone of you can spot my error from the attached code, below.
The code assumes the presence of inserted ActiveX objects "RichText" (a smart text box) and "FTPThing" (the FTP object) and a field called fldSTATUS and a command button called btnSEND on the form. The FTP object is referred to as "Inet".
Option Compare Database
Dim MyFTP As Inet
Private Sub FTPThing_StateChanged(ByVal State As Integer)
On Error GoTo Err_FTPThing_StateChanged
' retrieve server response using the GetChunk method when State = 12
Dim vtData As Variant ' data variable
Select Case State
Case icNone '0
fldSTATUS = fldSTATUS & "Response Code 00 - NOTHING" & vbCrLf
Case icResolvingHost '1
fldSTATUS = fldSTATUS & "Response Code 01 - HOST RESOLVING" & vbCrLf
Case icHostResolved '2
fldSTATUS = fldSTATUS & "Response Code 02 - HOST RESOLVED" & vbCrLf
Case icConnecting '3
fldSTATUS = fldSTATUS & "Response Code 03 - CONNECTING" & vbCrLf
Case icConnected '4
fldSTATUS = fldSTATUS & "Response Code 04 - CONNECTED" & vbCrLf
Case icRequesting '5
fldSTATUS = fldSTATUS & "Response Code 05 - REQUESTING" & vbCrLf
Case icRequestSent '6
fldSTATUS = fldSTATUS & "Response Code 06 - REQUEST SENT" & vbCrLf
Case icReceivingResponse '7
fldSTATUS = fldSTATUS & "Response Code 07 - RECEIVING RESPONSE" & vbCrLf
Case icResponseReceived '8
fldSTATUS = fldSTATUS & "Response Code 08 - RESPONSE RECEIVED" & vbCrLf
Case icDisconnecting '9
fldSTATUS = fldSTATUS & "Response Code 09 - DISCONNECTING" & vbCrLf
Case icDisconnected '10
fldSTATUS = fldSTATUS & "Response Code 10 - DISCONNECTED" & vbCrLf
Case icError '11
fldRICHTEXT = "Error " & MyFTP.ResponseCode & " - " & MyFTP.ResponseInfo
Case icResponseCompleted ' 12
fldRICHTEXT = "Response Code " & Format(icResponseCompleted) & vbCrLf & MyFTP.GetChunk(100000)
' this works fine, indicating I am talking to the ActiveX object OK...
Private Sub btnABOUT_Click()
' this one crashes and says "Execute failure" but it WILL report the status changes to fldSTATUS...
Private Sub btnSEND_Click()
On Error GoTo Err_btnSEND_Click
MyFTP.Protocol = icFTP
MyFTP.RemotePort = 21
MyFTP.UserName = "myusername"
MyFTP.Password = "mypassword"
MyFTP.RequestTimeout = 100
MyFTP.URL = "FTP://ftp.my_ftp_site.net"
MyFTP.Execute MyFTP.URL, "PUT D:..path..SelectedFileName.ext /ftpdir/"
The examples like this one I have been able to find in the MS Knowledgebase and elsewhere assume Visual Basic 6.0 (not VBA), but I have tried this code there with no success either. At least one example hinted there were bugs in some of the methods for this object. Any light you could shine on this would be very appreciated. Alternatively, if you have a thought on another way to transfer files via FTP from within a VBA application, (other than a Shell to the FTP.EXE command in DOS, which is what I am using now but trying to replace), I'd be pleased to hear of it!
Big thanks if you've waded this deep into my swamp !