how to upload with vba a database to a ftp

Bill,

 OK, this will be a 3 step process:

1. Export tables to files.
2. Send the files via FTP
3. Delete/clear the table and import the data.

For step one, use the following code in the DB:

Sub ExportAllTables()


  Dim db As DAO.DATABASE
  Dim tdf As DAO.TableDef
 
  Set db = CurrentDb()
 
  For Each tdf In db.TableDefs
    If Left(tdf.Name, 4) <> "Msys" Then
    DoCmd.TransferText acExportDelim, , tdf.Name, "C:\TEMP\" & tdf.Name & ".txt", True
    End If
  Next
 
  Set tdf = Nothing
  Set db = Nothing
 
End Sub

  This requires DAO and will export every table in the current DB().

With that, you now need to send the files.  You can call FTP via Shell() or use the WININET lib routines that Ron pointed to.  I find Shell() a bit simpler, but it's a little more messy as you need code to wait for the shelled process to finish.  You 'd see code something like this:

Function FTPUploadFile(strLocalFileName As String, strFTPFilename As String, strFTPSiteName As String, strUsername As String, strPassword As String, strMode As String, Optional strTransferType As String) As Boolean

      ' Procedure to upload file to FTP site.
      ' Sends e-mail to ITALERT if upload fails and returns false.

          Const RoutineName = "FTPUploadFile"
          Const Version = "1.3"

          Dim strFTPCommandFile As String
          Dim strFTPScriptFile As String
          Dim strFTPLogfile As String

          Dim lngHWnd As Long
          Dim intFileNum As Integer
          Dim strMailMessage As String

          Dim oOCS_SendMail As New OCS_SendMail

10        On Error GoTo FTPUploadFile_Error

          ' Generate file names
20        strFTPCommandFile = "\FTP_" & AppShortName() & ".txt"
30        strFTPScriptFile = "\FTP_" & AppShortName() & ".bat"
40        strFTPLogfile = "\FTP_" & AppShortName() & ".Log"

          ' Write command file
50        intFileNum = FreeFile
60        Open strFTPCommandFile For Output As #intFileNum
70        Print #intFileNum, strUsername
80        Print #intFileNum, strPassword
90        If strMode = "PASSIVE" Then
100           Print #intFileNum, "PASSIVE"
110       End If
120       Print #intFileNum, "type " & IIf(strTransferType = "B", "binary", "ascii")
130       Print #intFileNum, "put " & Chr$(34) & strLocalFileName & Chr$(34) & " " & strFTPFilename
140       Print #intFileNum, "quit"
150       Close #intFileNum

          ' Write script file
160       intFileNum = FreeFile

170       Open strFTPScriptFile For Output As #intFileNum
180       Print #intFileNum, "@ftps -s:" & strFTPCommandFile & " " & strFTPSiteName & " > " & strFTPLogfile
190       Close #intFileNum

          ' Execute
200       lngHWnd = Shell(strFTPScriptFile, vbHide)
210       WaitWhileRunning (lngHWnd)

          ' Check log file
220       If IsValidFTP(strFTPLogfile) Then
230           FTPUploadFile = True
240       Else
250           If DebugMode() = True Then
260               Stop
270               FTPUploadFile = False
280           Else
290               oOCS_SendMail.SetParams "ITALERT", ".", "."
300               oOCS_SendMail.Subject = "FTP Upload failed"
310               strMailMessage = "The file: " & strLocalFileName & " did not upload to the FTP site: " & strFTPSiteName & " with username: " & strUsername & " password: " & strPassword & vbCrLf
320               strMailMessage = strMailMessage & "Command, script, and log files are attached." & vbCrLf & vbCrLf
330               strMailMessage = strMailMessage & "App name:" & AppShortName() & " Version: " & AppVersion()
340               oOCS_SendMail.Message = strMailMessage
350               oOCS_SendMail.Attachment = Dir(strFTPCommandFile) & ";" & Dir(strFTPScriptFile) & ";" & Dir(strFTPLogfile)
360               oOCS_SendMail.Send
370               FTPUploadFile = False
380   Stop
390           End If
400       End If

FTPUploadFile_Exit:
410       On Error Resume Next

420       If Dir(strFTPCommandFile) <> "" Then Kill strFTPCommandFile
430       If Dir(strFTPScriptFile) <> "" Then Kill strFTPScriptFile
440       If Dir(strFTPLogfile) <> "" Then Kill strFTPLogfile

450       Close #intFileNum

460       Exit Function

FTPUploadFile_Error:
470       UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
480       FTPUploadFile = False
490       Resume FTPUploadFile_Exit

End Function

Select all Open in new window

  This routine uploades a single file, but depending on the FTP client your using, you could use a single MPUT() command to move all the files at one time.

 What it does is creates a batch file with the commands as if you were typing them, then executes that, waits for it to finish, and then checks the log file to see if it was sucessfull or not.

 Once the files are on the remote server, you will need code running in a DB that checks the drop box (directory where the FTP files were placed) to see if anything is there using DIR().

 If it sees files, then for each file it will need to:

1. Either drop or clear the current table
2. Import the data.
3. Fill the table if required.

 There are a bunch of ways to do this and the method you chosoe would depend on things like relationships existing, related records, etc.

 Generally you'll find that it's easier to delete from tables and then import directly into them using DoCmd.TransferText again.

 I'll stop there and see if this is a start for you or not.

 You may want to simply upload a complete DB rather then taking this approach.

Jim.