Solved

how to upload with vba a database to a ftp

Posted on 2012-12-24
21
848 Views
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)?
0
Comment
Question by:bill201
  • 7
  • 5
  • 4
  • +2
21 Comments
 
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.

Jim.
0
 

Author Comment

by:bill201
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
ftp://sunshop.com part 55
use name: jimmy
password:123
the address to the database is /_databases/invoices.mdb
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38720117
This link shows one of the ways you can upload your database to the ftp site:
http://access.mvps.org/access/modules/mdl0037.htm
0
 
LVL 39

Assisted Solution

by:als315
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.
0
 

Author Comment

by:bill201
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
0
 
LVL 29

Expert Comment

by:IrogSinta
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
0
 
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:

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

Jim.
0
 

Author Comment

by:bill201
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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

Jim.
0
 
LVL 29

Expert Comment

by:IrogSinta
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
0
 

Author Comment

by:bill201
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
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 38732543
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.
0
 
LVL 29

Assisted Solution

by:IrogSinta
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 = "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

0
 
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).
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38733653
Good point, Scott.
0
 

Author Comment

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

Author Closing Comment

by:bill201
ID: 38733679
excellent and advanced answers, thanks a lot
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to dynamically set the form action using jQuery.
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now