Upload and download files in Access

[juricta]

thread705-1073963

I got 1DMF's code to work in Access 2010 for the UPLOAD. I used his form, transferred those variables to "hard code" and run the upload from a form button. I cannot get the DOWNLOAD to work mainly because I do not know what the variables are and what defines them. If I had a form like 1DMF used for uploads I could figure this out. Can anyone help me get all the variables and what they mean?

Thanks,

juricta

 

[strongm]

That's a somewhat complicated method of doing FTP. Nothing wrong with it, particularly if you need the flexibility just that there are easier methods - for example the Windows shell knows how to do FTP ...

[blue][green]' Shell variant
' Requires reference to Microsoft Shell Controls and Automation[/green]
Private Sub DownloadFTP()
    Dim myShell As Shell
    Dim localFolder As Folder [green]' folder on PC[/green]
    Dim ftpFolder As Folder
    
    Set myShell = New Shell
    Set localFolder = myShell.NameSpace("c:\downloads")
    Set ftpFolder = myShell.NameSpace("ftp://ftp.microsoft.com/MISC") [green]'("ftp://username:password@website.com/example.txt") this format if we need user name and password[/green]
    localFolder.CopyHere ftpFolder.Items.Item("disclaim.txt")
End Sub[/blue]

 

[juricta]

strongm,
I am trying to understand your code (as simple as it is). All I need to do is go to an FTP site/folder and download the file that is there.
Am I correct with the changes I made to your code below using my test information? ( I left you code above what I think mine should be)

If I have:
FTP address = 173.201.98.1
FTP file = inventory.txt
FTP Folder = /juricta
FTP User = juricta
FTP PW = BigBubba
Local Folder to Store FTP File=c:/juricta/OLCC
Local Name for FTP File = olcc.txt

Then the code would be:

' Shell variant
' Requires reference to Microsoft Shell Controls and Automation
Private Sub DownloadFTP()
Dim myShell As Shell
Dim localFolder As Folder ' folder on PC
Dim ftpFolder As Folder

Set myShell = New Shell
Set localFolder = myShell.NameSpac("c:/juricta/OLCC")
Set ftpFolder = myShell.NameSpace("ftp://ftp.microsoft.com/MISC") '("ftp://usernameassword@website.com/example.txt") this format if we need user name and password
Set ftpFolder = myShell.NameSpace("ftp://juricta:BigBubba@173.201.98.1/inventory.txt")

localFolder.CopyHere ftpFolder.Items.Item("disclaim.txt")
localFolder.CopyHere ftpFolder.Items.Item("olcc.txt")
End Sub

 

[strongm]

Pretty much. Except the Shell's FTP does not provide the capability to copy with a rename; you'll have to rename the file after copying

[blue][green]' Shell variant
' Requires reference to Microsoft Shell Controls and Automation[/green]
Private Sub DownloadFTP()
    Dim myShell As Shell
    Dim localFolder As Folder [green]' folder on PC[/green]
    Dim ftpFolder As Folder

    Set myShell = New Shell
    Set localFolder = myShell.NameSpace("c:\juricta\OLCC") 
    Set ftpFolder = myShell.NameSpace("ftp://juricta:BigBubba@173.201.98.1/juricta") 
    localFolder.CopyHere ftpFolder.Items.Item("inventory.txt")
End Sub[/blue]

 

[juricta]

Strongm,

I am getting a "Type Mismatch" on the SET FTPFOLDER line of code (line 9) I am using Access 2010 so when I made your reference I had a few different code words:
Copy vs CopyHere (yours)
Files.Items vs Items.Item (yours)

The file I am testing this code on is located as a Subdirectory (directly under the root directory) called Liquor and the file is test1.txt

'CODE for download
' Shell variant
' Requires reference to Microsoft Shell Controls and Automation
Dim myShell As Shell
Dim localFolder As Folder ' folder on PC
Dim ftpFolder As Folder

Set myShell = New Shell
Set localFilder = myShell.NameSpace("C:\juricta\OLCC")
Set ftpFolder = myShell.NameSpace("ftp://jurictaa$$word1@173.201.98.1/Liquor") Type Mismatch occurrs here

localFolder.Copy ftpFolder.Files.Item("test1.txt")

 

[strongm]

>when I made your reference I had a few different code words

Seems to me that you must have another reference in your project which also has a Folder object. Specifically I suspect that you have the Microsoft Scripting Runtime library referenced. So you need to be more explicit in your declarations to ensure that you are using the correct library:

Dim myShell As Shell
Dim localFolder As Folder ' folder on PC
Dim ftpFolder As Folder

should become

Dim myShell As Shell32.Shell
Dim localFolder As Shell32.Folder ' folder on PC
Dim ftpFolder As Shell32.Folder

 

[juricta]

strongm,

1. Fixing the references, I had to change back to some of your original code (last line used to copy FTP file) It now reads:
localFolder.CopyHere ftpFolder.Items.Item("Test1.txt")

2. The code now runs without erros AFTER I noticed a typo on my part: localFolder was spelled localFilder

3. The issue I have now is that I cannot find the file Test1.txt (files only says "Test" so it is 6K in size) in OLCC. I ran the code and waited about 5 minutes and still cannot find the file. When I stepped through the code, using both the URL address and the IP Address, and hovered cursor over the variables localFolder and ftpFolder here is what they are defined as:
localFolder="OLCC"
ftpFolder="Liquor on

http://www.randdcomputers.com"

od "Liqour on 173.201.98.1" (if using IP Address)
Can you further help? Here is my code.

Private Sub DownloadFTP_Click()
'CODE for download
' Shell variant
' Requires reference to Microsoft Shell Controls and Automation
Dim myShell As Shell32.Shell
Dim localFolder As Shell32.Folder ' folder on PC
Dim ftpFolder As Shell32.Folder

Set myShell = New Shell
Set localFolder = myShell.NameSpace("C:\juricta\OLCC")
'Set ftpFolder = myShell.NameSpace("ftp://jurictaa!!word1@173.201.98.1/Liquor/")
Set ftpFolder = myShell.NameSpace("ftp://jurictaa!!word1@

http://www.randdcomputers.com/Liquor/")

localFolder.CopyHere ftpFolder.Items.Item("Test1.txt")
End Sub

 

[strongm]

http://www.randdcomputers.com/liquor

does not exist, as far as I can tell.

 

[juricta]

http://www.randdcomputers.com/liquor

DOES NOT exist. It should be Liquor with capital "L"

if you go to this site you will see the contents of the file Test3.txt (which is Test 3)
http:

http://www.randdcomputers.com/Liquor/Test3.txt

(with CAPITAL "L")

If you go to this site you will see the comment "pageok"
173.201.98.1/Liquor/Test3.txt

'CODE for download
' Shell variant
' Requires reference to Microsoft Shell Controls and Automation
Dim myShell As Shell32.Shell
Dim localFolder As Shell32.Folder ' folder on PC
Dim ftpFolder As Shell32.Folder

Set myShell = New Shell
Set localFolder = myShell.NameSpace("C:\juricta\OLCC\")
Set ftpFolder = myShell.NameSpace("ftp://jurictaltbgyd!982@173.201.98.1/Liquor")

localFolder.CopyHere ftpFolder.Items.Item("Test3.txt")

End Sub

 

[juricta]

ftp://

http://www.randdcomputers.com/Liquor/

will show the necessary files but I still cannot seem to find the download file (Test1,2,or3). It happens so fast I cannot see what is happening??

Yesterday the Server was having issues.

 

[strongm]

In which case, assuming that c~:\juricta\OLCC exists (and that you haven't changed the password, the following (no real change, just updated to reflect your current settings) should work (it does here):

[blue]Option Explicit

' Shell variant
' Requires reference to Microsoft Shell Controls and Automation
Private Sub DownloadFTP()
    Dim myShell As Shell
    Dim localFolder As Folder ' folder on PC
    Dim ftpFolder As Folder

    Set myShell = New Shell
    Set localFolder = myShell.Namespace("c:\juricta\OLCC")
    Set ftpFolder = myShell.Namespace("ftp://juricta:Dltbgyd!982@[URL unfurl="true"]www.randdcomputers.com/Liquor/")[/URL]
    localFolder.CopyHere ftpFolder.Items.Item("Test3.txt")
End Sub[/blue]

I'm afraid I can't explain why you cannot find the downloaded file.