Using Access to initiate an FTP file transfer

[LittleSmudge]

I have a series of databases that are User Interfaces to a MySQL back end.

These databases are used by users on 6 sites connected via a WAN.

Each user has a copy the the Access Front End on their machine.

When I create an up-issue of a front end the users are prompted to update their local copy.

They do this by opening another access database which simply copies the selected master database to the /Program Files/DBs/ folder on their hard drive.

Now here's the problem.

The biggest FE is running at 30Meg and users are experienceing problems with the WAN dropping out in mid copy.

So is there a way of initiating an FTP transfer rether than a File Copy actoss the WAN
( I'm suggesting this because I understand that FTP will allow for a resume after an interuption of an incomplete transfer - Is that right ? )





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MoLaker]

You should be able FTP via SHELL, but that probably won't do what you're after. To survive transfer interruptions, I think you'll have to have some commercial FTP app. and call that from Access. You might try WS_FTP_LE (freebie) or something like that, but WS_FTP_PRO (not freebie) might be required for tranfers interruption recovery.

An Access database typically has a lot of dead space - meaning, it will zip up substantially - as little as 20% of the original size. You might look into using a self-extracting zip of your FE. 8 mb might not be such a problem.

Also, just in case you aren't already doing so, you should compact your FE before you post it for copying. You might be surprized at the file size change. Your 30 mb FE might really only be 5 mb.

 

[LittleSmudge]

Thanks MoLaker.

I've trained my fingers to the point that I am just unable to save a db at the end of the day without compacting !

The Decompile - Recompile happens a lot less often - but that too can save a few meg.

I'll have to look more closely at ZIP options.


Thanks.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Hap007]

Hi LittleSmudge,

I have a sample program that I could email you, but here is a cut and paste of code to get you started:

Note:
The code assumes several boxes and buttons are on the form, but most important is to add the 'internet control' to the form.

In my example below, I have called this control 'Inet1'

Good Luck,
Hap...

code below
----------------------------------------------------------

Option Compare Database
Option Explicit
Dim ftpDataExpected As Boolean

Private Sub cmdChangeDirectory_Click()
    ' Change directory to txtRemotePath.
    Inet1.Execute txtURL.Text, "CD " ' & Me.txtRemotePath.Text
End Sub

Private Sub cmdDELETE_Click()
    ' Delete the directory in txtRemotePath.
    Inet1.Execute txtURL.Text, "DELETE " '& Me.txtRemotePath.Text
End Sub

Private Sub cmdDIR_Click()
    Inet1.Execute txtURL.Text, "DIR FindThis.txt"
End Sub

Private Sub cmdGET_Click()
    Inet1.Execute txtURL.Text, _
        "GET GetThis.txt C:\MyDocuments\GotThis.txt"
End Sub

Private Sub cmdSEND_Click()
    Inet1.Execute txtURL.Text, "SEND C:\MyDocuments\Send.txt SentDocs\Sent.txt"
End Sub

Private Sub CopyBtn_Click()
    Dim i As Integer
    Dim MyTargetDirectory As String
    Dim MySource As String, MyTarget As String
    Dim MyCommandLine As String
    MyTargetDirectory = "C:\Database\USPSftp\Downloads\"
    
'    MsgBox Me.ListFtpFile.ListCount
'    MsgBox Me.ListFtpFile.ListIndex
    
    If Me.ListFtpFile.ListCount = 0 Then
        MsgBox "Nothing to process"
        Exit Sub
    End If
    
    For i = 0 To 3 'Me.ListFtpFile.ListCount - 1
'       MsgBox Me.ListFtpFile.ItemData(i)
        MyCommandLine = "GET " & Me.ListFtpFile.ItemData(i) & " " & MyTargetDirectory & Me.ListFtpFile.ItemData(i)
        'Copy File from ftp to local drive
        Me.Inet1.Execute , MyCommandLine
    Next i

End Sub

Private Sub DirBtn_Click()
On Error GoTo Err_DirBtn_Click
    Me.TextData = ""
    ftpDataExpected = True
   
    With Inet1
'        .URL = "ftp://107.107.107.107"
'        .UserName = "test"
'        .Password = "testpass"
        .Execute , "DIR" ' Returns Directory
 '       .Execute "CLOSE"
    End With

Exit_DirBtn_Click:
    Exit Sub

Err_DirBtn_Click:
    MsgBox Err.Description
    Resume Exit_DirBtn_Click
    

End Sub

Private Sub Form_Load()
    ftpDataExpected = False
    Me.errText = "Begin at " & Now
End Sub

Private Sub Inet1_StateChanged(ByVal State As Integer)
    ' Retrieve server response using the GetChunk
    ' method when State = 12.

    Dim vtData As Variant ' Data variable.
    Dim vtDataMore As Variant ' Data variable.
    Dim bDone As Boolean: bDone = False
    
    Me.errText = Me.errText & Chr$(13) & Chr$(10) & "ftp state: " & Str$(State) & " at " & Now
    If Inet1.ResponseCode <> 0 Then
        Me.errText = Me.errText & Chr$(13) & Chr$(10) & " response code =" & Inet1.ResponseCode & ":" & Inet1.ResponseInfo
    End If
    
    Select Case State
    Case icError ' 11 These are expected errors like file exists 80
        ' In case of error, return ResponseCode and
        ' ResponseInfo.
        vtData = Inet1.ResponseCode & ":" & Inet1.ResponseInfo
        Me.TextData = vtData
    Case icResponseCompleted ' 12
        vtData = Inet1.GetChunk(1024)
        Do While Not bDone
            ' Get next chunk.
            vtDataMore = ""
            vtDataMore = Inet1.GetChunk(1024)
            If Len(vtDataMore) = 0 Then
                bDone = True
            Else
                'Save and go back for more
                vtData = vtData & vtDataMore
            End If
        Loop
        Me.TextData = vtData
        If ftpDataExpected = True Then
            ParseDataToList vtData
            ftpDataExpected = False
        End If
    Case Else
        Beep
        ' ... Other cases not needed.
    End Select
    
    
End Sub
Private Function ParseDataToList(MyDataIn As Variant) As Integer
    Dim iStart As Long, i As Long
    Dim MySearch As String
    Dim MyParsedData As String
    Dim MyWorkData As String
    MyParsedData = ""
    MySearch = Chr$(13) & Chr$(10)
    MyWorkData = Trim$(MyDataIn & " ")
    
    i = InStr(MyWorkData, MySearch)
    Do While i > 0
        If Len(MyParsedData) > 0 Then
            MyParsedData = MyParsedData & ";" & Mid$(MyWorkData, 1, i - 1)
        Else
            MyParsedData = Mid$(MyWorkData, 1, i - 1)
        End If
        MyWorkData = Mid$(MyWorkData, i + 2)
        i = InStr(MyWorkData, MySearch)
    Loop
    
    If Len(MyParsedData) > 0 Then
        Me.ListFtpFile.RowSource = MyParsedData
    Else
        Me.ListFtpFile.RowSource = ""
    End If
End Function
Private Sub Inet1_StateChanged_Works(ByVal State As Integer)
    ' Retrieve server response using the GetChunk
    ' method when State = 12.

    Dim vtData As Variant ' Data variable.
    Select Case State
    ' ... Other cases not shown.
    Case icError ' 11
        ' In case of error, return ResponseCode and

' ResponseInfo.
        vtData = Inet1.ResponseCode & ":" & _
        Inet1.ResponseInfo
    Case icResponseCompleted ' 12
        vtData = Inet1.GetChunk(1024)
    End Select
    Me.TextData = vtData
    
End Sub

Private Sub CloseFtpBtn_Click()
On Error GoTo Err_CloseFtpBtn_Click

    With Inet1
        .URL = ""
        .UserName = ""
        .Password = ""
        .Execute "CLOSE"
    End With

Exit_CloseFtpBtn_Click:
    Exit Sub

Err_CloseFtpBtn_Click:
    MsgBox Err.Description
    Resume Exit_CloseFtpBtn_Click
End Sub

Private Sub openFtpBtn_Click()
On Error GoTo Err_openFtpBtn_Click
    ftpDataExpected = True
    Me.TextData = ""
    
    With Inet1
        .URL = "ftp://107.107.107.107"
        .UserName = "test"
        .Password = "testpass"
        .Execute , "DIR" ' Returns Directory
 '       .Execute "CLOSE"
    End With

Exit_openFtpBtn_Click:
    Exit Sub

Err_openFtpBtn_Click:
    MsgBox Err.Description
    Resume Exit_openFtpBtn_Click
    
End Sub

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[eireanacht]

I have an FTPClient module that works for something that sounds very much like what you're talking about. In fact, I have a database which creates a dataset which I then FTP, using Access only, directly to an Internet site I happen to manage. If you would like to see what I have, just let me know. It's a fairly large module, and I assume it would be problematic to post the entire thing here. If you have a "safe" email address I can send it to, I'd be happy to pass it along to you.

 

[LittleSmudge]

Thanks eireanacht

work commitments elsewhere have dragged my attention away from this topic recently - but it is still an issue for me.

I haven't even had time to look at Hap007's suggestion above yet.

However, if you'd like to send the item to by address ( as in the signature below ) then I'll look at it along with Hap007's when I can.


Thanks folks.
I appreciate your ideas and assistance - even if I can't action them as swiftly as I'd like.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[tobsters]

Great thread with good info in all the posts so far...

I find myself also needing to be able to FTP from inside of Access. Hap007 could you email me (address below) your code you mentioned in your reply to LittleSmudge for accomplishing this? Also you mention the 'internet control' was important, could you be more specific of which one you used?

I spent some time trying to port over some legacy VB6 code but ran in to some issues with porting some With Events logic from VB to Access.

I would like to try another approach and yours showed up high in my Google search on the subject.

Thanks in advance...
tobsters(at)gmail(dot)com

 

[markphsd]

eireanacht

Would you post me that email too. Or, I'm really just looking to find out how to send a file to a subfolder of the ftp site, I can only seem to get it on the root directory.

Thanks.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.