Hi Y'all,

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 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
Option Explicit

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)

End Select

DoCmd.Hourglass False

Exit Sub

MsgBox Err.Description
DoCmd.Hourglass False
Resume Exit_FTPThing_StateChanged
End Sub

' this works fine, indicating I am talking to the ActiveX object OK...
Private Sub btnABOUT_Click()


End Sub

' 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"
DoCmd.Hourglass True
MyFTP.Execute MyFTP.URL, "PUT D:..path..SelectedFileName.ext /ftpdir/"

DoCmd.Hourglass False
Exit Sub

MsgBox Err.Description
DoCmd.Hourglass False
Resume Exit_btnSEND_Click

End Sub

================================================== =====

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 !

- David