how to upload with vba a database to a ftp

Posted on 2012-12-24
Last Modified: 2012-12-31
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)?
Question by:bill201
  • 7
  • 5
  • 4
  • +2
LVL 57
ID: 38718489
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.


Author Comment

ID: 38718519
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 part 55
use name: jimmy
the address to the database is /_databases/invoices.mdb
LVL 29

Expert Comment

ID: 38720117
This link shows one of the ways you can upload your database to the ftp site:
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

LVL 40

Assisted Solution

als315 earned 50 total points
ID: 38720243
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.

Author Comment

ID: 38720475
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
LVL 29

Expert Comment

ID: 38720654
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
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 38723348
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.
LVL 57
ID: 38723943
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.


Author Comment

ID: 38724494
IrogSinta i'm not able to import the  module  InetTransferLib. like i wrote  when i checked in references it's said missing inettransferlib.mda

Author Comment

ID: 38724499
welcome back  JDettman i will be very happy to get your solution
LVL 57
ID: 38724527
<<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?

LVL 29

Expert Comment

ID: 38725773
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

Author Comment

ID: 38725887
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
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 38732543

 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.

LVL 29

Assisted Solution

IrogSinta earned 200 total points
ID: 38732839
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 = ""
        .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

LVL 84
ID: 38733599
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).
LVL 29

Expert Comment

ID: 38733653
Good point, Scott.

Author Comment

ID: 38733677
thanks every one It is unfortunate that only five hundred points to divide between you

Author Closing Comment

ID: 38733679
excellent and advanced answers, thanks a lot

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question