FTP a .txt file using a timer event???

[DanAtCDS]

I'm looking for advice on how to best set up a 2000 database that will move a given file to a ftp server using a form's timer event.(i think) Here is the situation: I have set up a database that customer service reps will use in order to track their incoming phone call information. A client would like the call data to be transferred to them every half hour between 10 am and 2 pm via ftp. So my dilemma is that if there are multiple users in the database at any given time, how will I know it's OK to perform the VBA code to write the file, name the file appropriately and place it on the ftp server without making the database unavailable to the end users?? I'm hoping someone has done something similar or at least can give me an idea of what to try.

Thanks in advance for any assistance!
Dan

 

[VBslammer]

Here's some info on how to perform the upload task:

[green]'Select Tools | References....
'Select Microsoft Internet Transfer Control from the available references.
'If it is not available, click the Browse... button. 
'You will be presented with the Add Reference dialog box.
'Locate and select MSINET.OCX. This file is usually present in the System folder.
'Click Open to dismiss the Add Reference dialog box.
'Click OK.[/green]
Function UploadFile(ByVal HostName As String, _
  ByVal UserName As String, _
  ByVal password As String, _
  ByVal LocalFileName As String, _
  ByVal RemoteFileName As String) As Boolean
On Error GoTo ErrHandler

  Dim FTP As Inet

  Set FTP = New Inet
  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

ExitHere:
  On Error Resume Next
  Set FTP = Nothing
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

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

 

[DanAtCDS]

VBSlammer,
I currently have the ftp portion of this taken care of. (Although your code proves to be a little easier to follow)

What I'm really looking for is a way to ensure that my end users won't "mess up" the ftp by being in the database table when the time comes to move the file. Does anyone have thoughts on how to best make sure this doesn't happen? I have thought about kicking users out of the database to perform the ftp, however, that would happen every half hour (between 10am and 2pm) and might cause problems for the reps if they are in the middle of entering data. There will be a back end and front end for this database. Is there any advantage to this?

Any advice is much appreciated!
Dan

 

[VBslammer]

Aren't you doing an export of the call data? I would export the table or a query to file and then upload it - the number of users shouldn't matter.

You can export a table or query to HTML format:

Option Explicit

Private Sub Form_Timer()
  [green]'DoCmd.OutputTo acOutputTable, "tblCallData", acFormatHTML, "C:\CallData.htm"[/green]
  DoCmd.OutputTo acOutputQuery, "qryCallData", acFormatHTML, "C:\CallData.htm"

  UploadFile "ftp.mydomain.com", "username", "password", "c:\CallData.htm", "CallData.htm"
End Sub

How does the client want the data formatted?

VBSlammer

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