• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 937
  • Last Modified:

how to upload with vba a database to a ftp

is possible to make with vba a code to upload the current database to a ftp file (or to upload all tables to a file in the server with a record set)?
  • 7
  • 5
  • 4
  • +2
4 Solutions
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Yes it's possible.  Depending on your needs, there are several approaches.

Simplest is simply linking.  When you link, as part of the file specification you use  FTP://<host>
That doesn't work in all cases.

Beyond that, there are two basic techinques for FTPing a file:

1. Create a batch file and shell out to the command line.

2. Use code and the WININET lib.

Combining that along with other things (like writting a file to disk from the current tables), allows you to do just about anything.

 Fill in some more details on exactly what it is your trying to do and I can offer more detail.

bill201Author Commented:
thanks alot for your answer
i have a database on a server and i want to upload a few tables (not has to be to whole database) to a database on the web

for example the database on the web name is Invoices.mdb

and i want to upload to the database on the server table from current database the tables names are  "InvoiceDetailsT","InvoiceT",'"customerT"
the ftp address for example
ftp://sunshop.com part 55
use name: jimmy
the address to the database is /_databases/invoices.mdb
This link shows one of the ways you can upload your database to the ftp site:
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

But you can't add something to existing database, you can only upload full database (or export some tables to new database and upload it). Then you should import tables (update existing) from uploaded DB to your database on server.
bill201Author Commented:
i try this code in access 2010 but it's dosn't work in this version (i tried to to import InetTransferLib.mda file but it's still dons't working and i when i checked in references it's said missing inettransferlib.mda
You need to import the modules from the mda file to your own database.  Then in the sample code, delete any mention of InetTransferLib.  For example,
Set objFTP = New InetTransferLib.FTP will become Set objFTP = New FTP
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This article has a different approach:


It uses simple command line scripts to handle this. If you don't need anything fancy, this is the easiest way to deal with FTP.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Well I'm back on-line today and since others have jumped in, I'll bow out.

Sorry I didn't get back to this.

bill201Author Commented:
IrogSinta i'm not able to import the  module  InetTransferLib. like i wrote  when i checked in references it's said missing inettransferlib.mda
bill201Author Commented:
welcome back  JDettman i will be very happy to get your solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<i have a database on a server and i want to upload a few tables (not has to be to whole database) to a database on the web

for example the database on the web name is Invoices.mdb

 Just to make sure I understand:

1. The database on the web server is a MDB yes?

2. What happens to the existing tables in the web server DB; are they going to be deleted first and these tables copied, or do the records themselves need to be simply added to the tables?   If yes, what about changed or deleted records?

i'm not able to import the  module  InetTransferLib
You misunderstand.  You need to import the 4 modules that are inside the InetTransferLib.mda file.  Here's a screenshot.  import
bill201Author Commented:
IrogSinta you are right but after i import the modules i get some  compile error like in the line:
   .StartDir = CurDir()
and alos strStatus = String$(MAX_BUFFER, 0)
it's seems that it's need to done some changes in the code to work with this in access 2010

JDettman i want to upload a table direct to a mdb file on the server to delete this table and upload the new table. but if is not possible i will upload the whole database and overwrite the old
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 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 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
  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

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

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

End Function

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.

One other way to do it would be to add an ActiveX control called "Microsoft Internet Transfer Control" to a form.  You can add ActiveX controls from the Control section in the Design tab of the Ribbon.  Name the form Internet_Control  and save it.  Here's the code you would then use in a module:
    Const sourceFile = "C:\pathToDatabase\invoices.mdb"
    Const targetFile = "/_databases/invoices.mdb"

    DoCmd.OpenForm "Internet_Control", , , , , acHidden
    With Forms!Internet_Control!inet1
        .URL = "ftp://sunshop.com"
        .userName = "jimmy"
        .passWord = "123"
        .Execute , "PUT " & sourceFile & " " & targetFile
        Do While .StillExecuting

        DoCmd.HourGlass False
        If (.ResponseCode = 0) Then
            Msgbox "Uploaded"
        End If
        .Execute , "QUIT"
    End With
    DoCmd.CLOSE acForm, "Internet_Control"

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
However, note that the "Microsoft Internet Transfer Control" is not certified for use in the Access environment, so be aware of that. Also, if you're deploying this to others, be sure that they have that control on their machine (and unless you own one of the Microsoft Development Tools that allows the distribution of that control, you cannot legally deploy it).
Good point, Scott.
bill201Author Commented:
thanks every one It is unfortunate that only five hundred points to divide between you
bill201Author Commented:
excellent and advanced answers, thanks a lot
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now