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

bill201Asked: 2012-12-24

Who is Participating?

Solutions

LVL 59

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented: 2012-12-31

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

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.

LVL 59

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2012-12-24

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.

Jim.

bill201Author Commented: 2012-12-24

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
password:123
the address to the database is /_databases/invoices.mdb

LVL 29

IrogSintaCommented: 2012-12-25

This link shows one of the ways you can upload your database to the ftp site:
http://access.mvps.org/access/modules/mdl0037.htm

LVL 41

als315Connect With a Mentor Commented: 2012-12-25

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: 2012-12-25

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

IrogSintaCommented: 2012-12-25

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 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented: 2012-12-27

This article has a different approach:

http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm

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 59

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2012-12-27

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.

Jim.

bill201Author Commented: 2012-12-27

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: 2012-12-27

welcome back JDettman i will be very happy to get your solution

LVL 59

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2012-12-27

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

Jim.

LVL 29

IrogSintaCommented: 2012-12-27

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.

bill201Author Commented: 2012-12-27

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 29

IrogSintaConnect With a Mentor Commented: 2012-12-31

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

        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 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2012-12-31

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