Access Front for FTP Upload to Offsite Server 1

[sard0nicpan]

Happy Friday,

Presently, my company has been manually uploading PDF's and other documents to an offsite repository for storage. I'd like to automate the process and integrate it with our own database with an Access front-end(Access 2000). I have all the background info on the formatting that the remote server needs, so that part is easy. What I believe I need is an add-in to Access that will make the FTP upload possible. Am I right in this assumption?

Does anyone have any suggestions? A freeware add-in would be preferable since requisitioning here is a drag. However, an add-in with a free trial period may be helpful to demonstrate the advantages of the process (hopefully making the request for purchase go through). Any Ideas, or products which you like?

Tony

 

[VBslammer]

Look at this:

Upload File Using FTP

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sard0nicpan]

Thanks VBSlammer, but I'm having one problem. I made the reference to MSINET.OCX, and everything compiles just fine (Access 2000, I call the function from a form). However when I call the function (illustrated below, it goes as far as "Set FTP = New Inet" and then returns to my main module, bypassing the rest of the function. Is there something I have forgotten to do?

Function UploadFile(ByVal HostName As String, _
    ByVal UserName As String, _
    ByVal Password As String, _
    ByVal LocalFileName As String, _
    ByVal RemoteFileName As String) As Boolean

    Dim FTP As Inet

    Set FTP = New Inet 'AT THIS POINT, EXECUTION RETURNS TO MODULE
    
With FTP
        .Protocol = icFTP
        .RemoteHost = HostName
        .UserName = UserName
        .Password = Password
        .Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
        Do While .StillExecuting
            DoEvents
        Loop
        UploadFile = (.ResponseCode = 0)
    End With
    Set FTP = Nothing
End Function

 

[VBslammer]

That's odd. I've never encountered that before...

I just tested the code on my box (WinXP Pro / Access 2000) and the upload worked fine. The Inet Object supports events (1 event anyway) so I declared it WithEvents in a form module so I could monitor its state. I added a Microsoft Forms 2.0 Listbox control to the form to post the state changes to, and added an error handler to the routine so I could troubleshoot my connection:

[green]'@------------------------------------------------------@
'@ This is the entire form module.
'@
'@ I used some textboxes to obtain the input values
'@ and a command button to start the upload.
'@
'@ The only problem I had was adding the root folder
'@ prefix "/[URL unfurl="true"]www/"[/URL] to the destination; yours may differ.
'@------------------------------------------------------@[/green]

Option Compare Database
Option Explicit

Private WithEvents oInet As Inet

[green]'@------------------------------------------------------@[/green]

Private Sub Form_Load()
  Set oInet = New Inet
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
  Set oInet = Nothing
End Sub

[green]'@------------------------------------------------------@[/green]

Private Sub cmdUpload_Click()
On Error GoTo ErrHandler

  lstStateChange.Clear   [green]' clear the listbox[/green]

  With oInet
    .Protocol = icFTP
    .AccessType = icDirect
    .RemoteHost = "ftp.mydomain.com"
    .UserName = txtUserID.Value
    .Password = txtPassword.Value
    .Execute .url, "PUT " & txtSource.Value & " /[URL unfurl="true"]www/"[/URL] & txtDestination.Value
    Do While .StillExecuting
        DoEvents
    Loop
    If .ResponseCode = 0 Then
      MsgBox "Upload Successful!", vbInformation, "Inet"
    Else
      MsgBox "Result: " & .ResponseCode & "-" & .ResponseInfo
    End If
  End With
  
ExitHere:
  Exit Sub
ErrHandler:
  MsgBox "Inet Error (" & Err & ") " & Err.Description, vbExclamation, "Inet"
  Resume ExitHere
End Sub

[green]'@------------------------------------------------------@[/green]

Private Sub oInet_StateChanged(ByVal State As Integer)
  Dim strOut As String
  Select Case State
    Case icConnected
      strOut = "Connected"
    Case icConnecting
      strOut = "Connecting"
    Case icDisconnected
      strOut = "Disconnected"
    Case icDisconnecting
      strOut = "Disconnecting"
    Case icError
      strOut = "Inet Error Ocurred"
    Case icHostResolved
      strOut = "Host Resolved"
    Case icNone
      strOut = "No State Change"
    Case icReceivingResponse
      strOut = "Receiving Response"
    Case icRequesting
      strOut = "Requesting"
    Case icRequestSent
      strOut = "Request Sent"
    Case icResolvingHost
      strOut = "Resolving Host"
    Case icResponseCompleted
      strOut = "Response Completed"
    Case icResponseReceived
      strOut = "Response Received"
  End Select
  
  strOut = strOut & " @ " & Now
  
  lstStateChange.AddItem strOut  [green]' add state change to listbox[/green]
  Debug.Print strOut
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sard0nicpan]

VBSlammer,

I tried the code you have above, and received the 429 error: Can't create ActiveX object. It turns out to be a common error (I am running Access 2000 on an XP). I thought I had a solution, but it turns out I did not. I made sure the msinet file was properly registered with the Run command ("Regsvr32 in msinet.ocx succeeded"). Anyway, it was reported back to me that the registration was successful DllRegisterServer in msinet. However, I still get the 429 error. I'm trying to figure what else I could be missing. Any ideas?

Thanks,
Tony

 

[PHV]

Perhaps yet another WinXP SP2 issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sard0nicpan]

PH,

I'm not sure what you mean. Do you mean a problem caused by SP2, or one solved with SP2? In my case, I have SP2 installed for almost 5 months. For the most part it has improved overall performance, expecially with my sqlserver connections (a whole other topic). Could please clarify?

Thanks,

Tony

 

[PHV]

I mean that SP2 enforces drastically network security and I wonder if the 429 error is security related or not.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

The dependency file (msinet.dep) contains this:

[Version]
Version=6.0.81.69

; Default Dependencies ----------------------------------------------

[MSInet.ocx]
Dest=$(WinSysPath)
Register=$(DLLSelfRegister)
Version=6.0.81.69
Uses1=ComCat.dll
Uses2=
CABFileName=MSInet.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=MSInet.inf

[ComCat.dll]
Dest=$(WinSysPathSysFile)
Register=$(DLLSelfRegister)
Uses1=
;
; [i]Regional Info omitted[/i]
;

So you should have these system32 files at minimum:
[tt]
msinet.ocx
msinet.dep
comcat.dll
[/tt]
Here's the MSDN explanation of the error in detail:
[navy]
Creating objects requires that the object's class be registered in the system registry and that any associated dynamic-link libraries (DLL) be available. This error has the following causes and solutions:
[ul][li]The class isn't registered. For example, the system registry has no mention of the class, or the class is mentioned, but specifies either a file of the wrong type or a file that can't be found.
If possible, try to start the object's application. If the registry information is out of date or wrong, the application should check the registry and correct the information. If starting the application doesn't fix the problem, rerun the application's setup program.

[/li]
[li]A DLL required by the object can't be used, either because it can't be found, or it was found but was corrupted.
Make sure all associated DLLs are available. For example, the Data Access Object (DAO) requires supporting DLLs that vary among platforms. You may have to rerun the setup program for such an object if that is what is causing this error.

[/li]
[li]The object is available on the machine, but it is a licensed Automation object, and can't verify the availability of the license necessary to instantiate it.
Some objects can be instantiated only after the component finds a license key, which verifies that the object is registered for instantiation on the current machine. When a reference is made to an object through a properly installed type library or object library, the correct key is supplied automatically.

[/li]
[li]If the attempt to instantiate is the result of a CreateObject or GetObject call, the object must find the key. In this case, it may search the system registry or look for a special file that it creates when it is installed, for example, one with the extension .lic. If the key can't be found, the object can't be instantiated. If an end user has improperly set up the object's application, inadvertently deleted a necessary file, or changed the system registry, the object may not be able to find its key. If the key can't be found, the object can't be instantiated. In this case, the instantiation may work on the developer's system, but not on the user's system. It may be necessary for the user to reinstall the licensed object.

[/li]
[li]You are trying to use the GetObject function to retrieve a reference to class created with Visual Basic.
GetObject can't be used to obtain a reference to a class created with Visual Basic.

[/li]
[li]Access to the object has explicitly been denied.
For example, you may be trying to access a data object that's currently being used and is locked to prevent deadlock situations. If that's the case, you may be able to access the object at another time.

[/li][/ul][/navy]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[sard0nicpan]

Hey VBSlammer, I didn't forget about you, I just have been under the weather bigtime with the flu. Kudos and a star for you for tracking down my problem: I did not have the msinet.dep file--so I just re-installed vb6 and my code now works!

Tony

 

[VBslammer]

Fantastic! I'm glad it was easy to fix...

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[AccessIsMyFriend]

I am having the same problem but I can't find a copy of msinet.dep anywhere. I have the other two files. Any idea where I can get msinet.dep?

Thanks,
Matthew

 

[VBslammer]

Here's mine, copy it into notepad and save as c:\windows\system32\msinet.dep
It's worth a try...

; Dependency file for setup wizards.

[Version]
Version=6.0.81.69

; Default Dependencies ----------------------------------------------

[MSInet.ocx]
Dest=$(WinSysPath)
Register=$(DLLSelfRegister)
Version=6.0.81.69
Uses1=ComCat.dll
Uses2=
CABFileName=MSInet.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=MSInet.inf

[ComCat.dll]
Dest=$(WinSysPathSysFile)
Register=$(DLLSelfRegister)
Uses1=

; Localized Dependencies -------------------------------------------- 

; ** German (DE) ***
; (0007 = German)
;
[MSInet.ocx <0007>]
Uses1=InetDE.dll
Uses2=

[InetDE.dll <0007>]
Uses1=
CABFileName=InetDE.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetDE.inf

; ** French (FR) ***
; (000C = French)
;
[MSInet.ocx <000C>]
Uses1=InetFR.dll
Uses2=

[InetFR.dll <000C>]
Uses1=
CABFileName=InetFR.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetFR.inf

; ** Italian (IT) ***
; (0010 = Italian)
;
[MSInet.ocx <0010>]
Uses1=InetIT.dll
Uses2=

[InetIT.dll <0010>]
Uses1=
CABFileName=InetIT.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetIT.inf

; ** Spanish (ES) ***
; (000A = Spanish)
;
[MSInet.ocx <000A>]
Uses1=InetES.dll
Uses2=

[InetES.dll <000A>]
Uses1=
CABFileName=InetES.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetES.inf

; ** Japanese (JP) ***
; (0011 = Japanese)
;
[MSInet.ocx <0011>]
Uses1=InetJP.dll
Uses2=

[InetJP.dll <0011>]
Uses1=
CABFileName=InetJP.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetJP.inf

; ** Korean (KO) ***
; (0012 = Korean)
;
[MSInet.ocx <0012>]
Uses1=InetKO.dll
Uses2=

[InetKO.dll <0012>]
Uses1=
CABFileName=InetKO.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetKO.inf

; ** Chinese Traditional (CHT) ***
; (0404 = Chinese Traditional)
;
[MSInet.ocx <0404>]
Uses1=InetCHT.dll
Uses2=

[InetCHT.dll <0404>]
Uses1=
CABFileName=InetCHT.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetCHT.inf

; ** Chinese Simplified (CHS) ***
; (0804 = Chinese Simplified)
;
[MSInet.ocx <0804>]
Uses1=InetCHS.dll
Uses2=

[InetCHS.dll <0804>]
Uses1=
CABFileName=InetCHS.cab
CABDefaultURL=http://activex.microsoft.com/controls/vb6
CABINFFile=InetCHS.inf

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[AccessIsMyFriend]

No luck for me with that.... Still getting the same 429 error when I try to set the new inet.

I have Access 2000 and its included version of VB, which doesn't install MSINET when installed. I wonder if there are other files I don't have or if MSINET is one of the classes that requires a license file perhaps?

Any thoughts on ways to fix this or other controls that support FTP?

Thanks, I really appreciate the help.
Matthew

 

[pujas]

I'am also getting the same error
I have registered MSINET.ocx
amd i have msinet.dep and msinet.oca in system32 and also registered comcat.dll

Do i need any more files??

Also i have only copied msinet.dep and msinet.oca. Is there any more steps to be done with these files.

 

[pujas]

Though i'll share this with u.

CreateObject did not work for me when i was running the ocx on a diff machine
But set inet1 = new Inet worked.

You have to go to project-> references and add the msinet.ocx first.

Hope this helps others

 

[prZeus]

Hi there i am having the same problem with msinet.ocx. I found out that installing and the uninstalling Visual Studio 6 fixes the problem and you don't have to register the ocx file.
sard0nicpan installed VB6 that's why it worked while pujas just copied the file. I am tring to figure out a way to work around this because I need to install and uninstall VS 6 in every pc want to run my application.
Any ideas?

Thanks

 

[1DMF]

I've got this problem also, but cannot re-install Visual Studio as I'm using MS Access, any ideas how to fix this?

I've created the .dep file, i've referenced it via the tools option and i've also run Regsvr32, none of this has helped

Any ideas anyone?

 

[sard0nicpan]

Hey 1DMF,

Basically I was in the same position as you. I'm assuming that you are working with Access 2000, standard addition. My suggestion is not Kosher (but probably neither legal or illegal, see below), however it worked for me.

Basically I borrowed a friend's copy and installed it on my desktop at work. I then checked to see if my code would then work and it did. Due to licensing issues, I had to get his VB6 off my desktop, however rather than uninstalling, I deleted it (uninstalling it disabled my upload script--again).

You must know someone who can lend you the software. As long as you get it off your hard drive, you should be OK so far as licensing issues (I don't think anyone would notice you have References in your library that you should'nt).

SP

 

[1DMF]

Thanks for the offer, but I spent 8 hours last night learning how to use the wininet.dll instead. It's stupid that all the msinet.ocx does is work as an ActiveX wrapper for the wininet.dll anyway and you need a developers licence to use - go figure.

If your interested I can supply you the code for using the wininet.dll instead, or if you know how to use it, do you know anything about decaring the functions for OpenFile & InternetWriteFile, at the moment I have it working with FtpPutFile, which is fine but the other method gives you greater flexability to include a progress meter by uploading in chunks.

regards,

1DMF