FTP with Access - Here is the Anwer 13

[1DMF]

I've spent days researching and pullling my hair out trying to use the wininet.dll and finally I have cracked it.

For all those wanting to FTP with Access here is the answer.

==========================================================
Firstly define the constants

' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True

Then declare the wininet.dll functions (i've included more than i use should you want to use them)

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias "FtpSetCurrentDirectoryA" _
    (ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias "FtpGetCurrentDirectoryA" _
   (ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String, lpdwCurrentDirectory As Long) As Boolean
   
Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA" _
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long, ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
      ByVal lpszRemoteFile As String, _
      ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean
      
Public Declare Function FtpDeleteFile Lib "wininet.dll" _
    Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
    ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias "InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
      ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal dwFlagsAndAttributes As Long, _
      ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
      Alias "InternetGetLastResponseInfoA" _
       (ByRef lpdwError As Long, _
       ByVal lpszErrorBuffer As String, _
       ByRef lpdwErrorBufferLength As Long) As Boolean

I then wrote this function to upload

Function FTPFile(ByVal HostName As String, _
    ByVal UserName As String, _
    ByVal Password As String, _
    ByVal LocalFileName As String, _
    ByVal RemoteFileName As String, _
    ByVal sDir As String, _
    ByVal sMode As String) As Boolean
    
    On Error GoTo Err_Function
        
' Declare variables
Dim hConnection, hOpen, hFile  As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE (100) elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT, UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection, INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE, IIf(sMode = "Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
    MsgBox "Internet - Failed!"
    ShowError
    FTPFile = False
    GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName & ")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE
        
    ' Update progress meter
    Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)
        
    'Get file data
    Get iFile, , FileData
      
    ' Write chunk to FTP checking for success
    If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0 Then
        MsgBox "Upload - Failed!"
        ShowError
        FTPFile = False
       GoTo Exit_Function
    Else
        ' Check buffer was written
        If iWritten <> BUFFER_SIZE Then
            MsgBox "Upload - Failed!"
            ShowError
            FTPFile = False
            GoTo Exit_Function
        End If
    End If
    
Next iLoop

' Handle remainder using MOD

    ' Update progress meter
    Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

    ' Get file data
    Get iFile, , FileData
    
    ' Write remainder to FTP checking for success
    If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE, iWritten) = 0 Then
        MsgBox "Upload - Failed!"
        ShowError
        FTPFile = False
        GoTo Exit_Function
    Else
        ' Check buffer was written
        If iWritten <> iSize Mod BUFFER_SIZE Then
            MsgBox "Upload - Failed!"
            ShowError
            FTPFile = False
            GoTo Exit_Function
        End If
    End If
               
Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

You will need the following ShowError routine that is used above

Sub ShowError()
   Dim lErr As Long, sErr As String, lenBuf As Long
   'get the required buffer size
   InternetGetLastResponseInfo lErr, sErr, lenBuf
   'create a buffer
   sErr = String(lenBuf, 0)
   'retrieve the last respons info
   InternetGetLastResponseInfo lErr, sErr, lenBuf
   'show the last response info
   MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
End Sub

Then simply call the function from anywhere like so

' Upload file
If FTPFile("ftp.domain.com", "myUserName", "myPassword", "Full path and Filename of local file","Target Filename without path", "Directory on FTP server", "Upload Mode - Binary or ASCII") Then
    MsgBox "Upload - Complete!"
End If

I hope it is of help to someone and saves them the time and hastle it took me to get it working.

If you want a full working example then follow this link,

http://www.homeloanpartnership.co.uk/FTP.zip

I have uploaded an Access MDB in 97, 2000 & 2003 format.

Enjoy and happy FTP'ing
1DMF

 

[pookie62]

Oke, let me try to explain:
In MSAccess created a scoring db. In this db are several matches, which are held on different locations, so the MSdb is distributed to those locations.
When a match is done, the organising party sends me an export with that match data in it.
I import this in my own copy of the MSdb (kind of masterdb)
Then I import with Navicat the data into my private MySQL db, dump the tables in sql files and finally upload these sql files with PHPadmin into the online (ISP hosted) MySQL.

With html/php I present the results of every match, and the new competition results (these calculations are done in the MSdb.
Check the site if you want to see what I mean, it is in Dutch, but you'll get the main idea..

http://www.actionshooting.nl

And please don't be chocked by the sport I do..;-)
Appreciate your help !

 

[1DMF]

so you have a local mdb that you upload ?

I have no experience with SQL files from one DB to another, do you use some kind of DTS to do this, if you are passing actual DB file with the data then you need to store the files in the local db before upload to your website - is that right?

 

[pookie62]

I have a local db from which I transfer data, to update the data, to my private home MySQL database.
In home MySQL I dump the updated table to a so called .sql file.
With PHP Admin (for managing the online MySQL db) I can read and thus import these sql files.
Then the online MySQL db is updated..
This process is now done manually by myself, making me a required keyperson for this job which I would like to prevent.
Much more convenient would be:
The matchresults (somewhere in the country)uploading the latest data by FTP-ing to website (succesfull now !) then a php script that reads this new uploaded file and updates the online MySQL db
So the first part is taken care of (FTP the new data) now the second part (updating the MySQL db with script is the issue I'm facing..
Hope I cleared it somewhat...

 

[1DMF]

so you have .sql files on the web server, you need to write a script to read .sql files and update the master web DB.

What language are you using on the webserver ? PHP, I only know perl and have no idea what a .sql file is.

is .sql a standard file format ?

can you set up a cron job or similar with the MYSQL to do a DTS against these files?

 

[pookie62]

Hi 1DMF,
Almost..
I create sql files locally (on my own private webserver running also MySQL for testing purposes)
With PHPAdmin (grafical UI for managing your online db) I can import these sql files
sql file is created when you dump the table. Can also have txt or csv and some more extensions.
Since I do all this manually I want to automate the update of the online db.
I don't know what a cron job is, but I think somekind of scheduled task ? Then the answer is no.. ISP won't let me do that I think..
DTS is no option either, the MySQL version of this ISP is too old.
And since you don't know php...I allready posted a new thread in the PHP Forum..
Thanks again for your efforts and time !!

 

[1DMF]

My approach would be to upload the files, have a script issue the commands to the sql server (if possible) to import the data.

Can you upload them as csv or xml instead, write a script to read the files, build an array to hold the data and run a loop inserting/updating the records in SQL?

good luck anyway, hope you get your system working, sounds like a good project to get your teeth into.

regards,

1DMF

 

[foto66]

1DMF

just wanted to thank you for posting your code. what a headache this was going to be for me without your help.

i am going to be uploading ascii files generated from a series of locations/clients (who are using an access database/interface) and then stuffing the data into mySQL on the remote machine with php scripting. had everything pretty much solved except the ftp!

cheers,
vuk.

 

[pookie62]

Yo vuk,
I have the FTP running, but no php scripting.. you seem to have solved this.
Would you share your scripts with me ??
Can PM me if you want..
Would appreciate it very much..
Thanks
Hans

 

[1DMF]

No Probs foto66, glad I could help, hope you get your project up and running real soon.

Regards,

1DMF

 

[foto66]

pookie62.

here is a VERY simplified description of the core steps:

1) use VBA and DAO/ADO to construct file containing executable SQL strings, for example (note--it's not this simple, because you must format date fields properly and look for apostrophes and other bad stuff in text fields):

dim db as database, wsJet as workspace
dim SQ as string
dim rs as recordset, i as integer, exec$

set wsJet = DBEngine.CreateWorkspace("", "admin", "", dbUseJet)
set db = wsJet.OpenDatabase("C:\myDatabase.mdb")

SQ = "SELECT * FROM myTable"

set rs=db.OpenRecordset(SQ, dbOpenSnapshot)
do until rs.eof
cols = "("
vals = "("
for i = 0 To rs.Fields.Count - 1
If cols = "(" Then
cols = cols & rs.Fields(i).name
Else
cols = cols & ", " & & rs.Fields(i).name
End If
If vals = "(" Then
vals = vals & "'" & rs.Fields(i).value & "'"
Else
vals = vals & ", '" & rs.Fields(i).value & "'"
End If
next i
exec$="INSERT INTO mytable " & cols & ") VALUES " & vals & ");"
rs.movenext
loop

etc.
will give you stuff like:

INSERT INTO mytable (referral, event, eventdate, lpvar, hospreq) VALUES ('1', '20010503', 'False', 'True');


2) upload file via ftp


3) PHP scripting:

//connect to db

$conn = mysql_connect('myhost', 'user', 'password') or die('Could not connect: ' . mysql_error());
mysql_select_db('mydatabase', $conn) or die('Could not select database.');


//open file with executable strings

$file = fopen ("myfile.txt", "r");


//loop through file and execute

while (!feof ($file)){
$Q = fgets ($file, 1024);
$ret=mysql_query($Q, $conn);
if ($ret=='1'){
$rec_proc=$rec_proc+1;
echo "record inserted <br>";
}else{
echo "insert failed <br>";
}

because of indexing and foreign key issues, i actually don't construct the full INSERT string straight off, but pass it in three parts (table, cols and values) then modify it at the php/server end--have not presented it this way here in order to make the essential strategy more clear for you.

p.s. you will also have to edit your php.ini file to enable the mySQL database scripting. otherwise, you need to use the ODBC equivalents, which are not as nice, IMO.

 

[flyover789]

This is one of the finest threads, I've read since a long time.
Thanks a lot and a star for 1DMF and also one for foto66 for the latest post.

Open question from my side to foto66:
How to automate the execution of the php script. Might just be obvious, but I'm not very experienced in php or cgi stuff.

Thanks again, and sorry in advance to the admins for pointing this thread in a non Access VBA direction ... but I think we are talking about data inetgration and interfacing here, which also implies the use of different technologies to accomplish one task.
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[foto66]

flyover789.

the php script is merely a file that sits on the web server and executes/runs when you point to it in your browser. to integrate this with VBA and launch automatically (once you have uploaded the data file), you run something like this from your windows app:

Application.FollowHyperlink "

http://www.myhost.com/myprocess.php"

(where myprocess.php is the file containing the script)

 

[flyover789]

foto66,

yeah I know at least what a php file is and does ...
but I couldn't see the obvious: follow hyperlink

Thanks so much again, this is absolutely fantastic.

Bye,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[pookie62]

Hi foto66,
Thanks for your constructive reply, but unfortunately I can't change the php.ini because the MySQL db is on a hosted site.
ODBC isn't a possible option either, which leaves me to the one option left: Export the data I want from MS db into a csv file.
Then upload this file with new data in it to the webserver
==
So far this is solved.
Now I would like to have a php script which I can access through the webbrowser and activate the php script.
This script needs to read the uploaded file, and update/insert the new data in the MySQL db.

What I've found so far on the net is not much help, I was hoping you had something I can start with. I not good enough to start form scratch with writing php scripts.. :-(

 

[foto66]

but unfortunately I can't change the php.ini because the MySQL db is on a hosted site

i really doubt that your hosting service would offer MySQL without having set this up properly already. it's like not having DAO or ADO with an access database.

Now I would like to have a php script which I can access through the webbrowser and activate the php script.
This script needs to read the uploaded file, and update/insert the new data in the MySQL db

that's precisely what my example does. i think you're going to have to practice a bit with all of this to get a good feel for what's going on.

 

[foto66]

before y'all take away my star, let me correct a mistake in what i posted above. the insert statement for MySQL should contain single-quotes around values only if they are text/string types. hence, my example, which has no such fields, should look like...

INSERT INTO mytable (referral, event, eventdate, lpvar, hospreq) VALUES (1, 20010503, False, True);


those are an integer, date and two boolean fields. btw, MySQL handles booleans as tinyint: it will turn True into 1 and False into 0.

 

[pookie62]

Hi foto66,
Thanks again,
I wouldn't dare taking your star away !

I will play around with your example and see where I'll get..
Cheers

 

[1DMF]

Hey it's ok foto66, if you lost a star, you could have one of mine, I seem to have gained a few on this thread, so I've got a spare one for you

It's good to see people finding real value from a thread and others adding their 2 pence worth, hopefully this thread will enable people to find a complete solution to data transfer and processing.

====================================
Another thought regarding firing off your script automatically if you can't set a cron job running.

If you have a website already, integrate it with that, when someone visits your site, behind the scene make it kick off the import scripts.

I use this principle alot and usualy have the call to the other code i want running inside my simple "Hit Counter" script, while I add one to the counter, the script runs other code to update tables and stuff used throughout my site.

Regards,
1DMF

 

[pookie62]

Hi 1DMF,
Good tip!
I'm thinking more in the direction of a button where the competion results are displayed when clicked, that genererates a little less activity than the counter you suggested.
I will use it that way though.. whenever I get my phpscript ready.. (not yet very far..)
Ciao

 

[1DMF]

Yep that'll works also, I do that same sort of thing for my Top 40 Charts I run.

so for you, when they click to view results you can run your code to import new results first before then displaying data.

Hope you get your script up and running soon, good luck