Automatically download spreadsheets, check updates from ftp site

[rdemyan via AccessMonster.com]

I currently have code that brings up a form where the user can see a list of
spreadsheets on my ftp site, select one and then download and import the
spreadsheet into a table.

All of this processing is initiated by the user.

I'm thinking about automating some or all of this. My questions is: how do I
best determine when there is an update to a table in my Access Application
that is available on my ftp site?

I guess I could just have a table in my app that holds the name of
spreadsheets that have been downloaded. Then when there is an update, the
new spreadsheet name would be greater than the old spreadsheet name and that
would trigger the processing to at least notify the user that updates are
available.

However, there are a number of tables that need to be updated. I' m not sure
how to keep the code from comparing the wrong spreadsheets. In other words,
instead of just comparing SpreadsheetTabl eA update with SpreadsheetTabl eA
previous download, it may get confused and now compare SpreadsheetTabl eA
update with SpreadsheetTabl eB previous download.

So, if this is the right way to solve my issue, I need some advice on a fool
proof naming convention to prevent this from happening. Perhaps an extra
field in the table that holds the previous spreadsheet names that indicates
which table the spreadsheet is applicable to would do the trick. I'm loathe
to start hardcoding names or indicators to help solve this problem.

On the other hand, I'm very open to other suggestions of how to accomplish
notifying the user that there are updates available on my ftp site for tables
within my application.

Thank you.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200604/1

[Logipro]

Hi,

Each time you put a new version of you spreadsheet, put a script files on
your FTP with a release version like 1,2,3...., and download this script and
compare this version with the local script and use my code to download if
necessary.
Best regards (sorry for my cheap english)

Robert Simard
Logipro
http://www.logicielappui.com/tips

'// Code Start
Option Compare Database
Option Explicit

Private Const MAX_PATH = 260
Private Const INTERNET_OPEN_T YPE_PRECONFIG = 0
Private Const INTERNET_OPEN_T YPE_DIRECT = 1
Private Const INTERNET_OPEN_T YPE_PROXY = 3
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const INTERNET_INVALI D_PORT_NUMBER = 0
Private Const INTERNET_SERVIC E_FTP = 1
Private Const INTERNET_SERVIC E_GOPHER = 2
Private Const INTERNET_SERVIC E_HTTP = 3
Private Const INTERNET_FLAG_P ASSIVE = &H8000000
Private Const INTERNET_FLAG_R ELOAD = &H80000000
Private Const INTERNET_FLAG_K EEP_CONNECTION = &H400000
Private Const INTERNET_FLAG_M ULTIPART = &H200000

Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Type WIN32_FIND_DATA
dwFileAttribute s As Long
ftCreationTime As FILETIME
ftLastAccessTim e As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

Global FTP_Server As String
Global FTP_User As String
Global FTP_PassW As String
Global glbSize As String
Global StopTransfert As Boolean

Const FTP_UAgent = "FTP Demo"
Dim hOpen As Long
Dim hConnection As Long
Dim Transfer As Long
Dim hFile As Long

Private Declare Function InternetOpen Lib "wininet.dl l" Alias
"InternetOp enA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal
sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As
Long
Private Declare Function InternetConnect Lib "wininet.dl l" Alias
"InternetConnec tA" (ByVal hInternetSessio n 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
Private Declare Function InternetGetLast ResponseInfo Lib "wininet.dl l" Alias
"InternetGetLas tResponseInfoA" (lpdwError As Long, ByVal lpszBuffer As
String, lpdwBufferLengt h As Long) As Long
Private Declare Function InternetCloseHa ndle Lib "wininet.dl l" (ByVal hInet
As Long) As Integer
Private Declare Function FtpOpenFile Lib "wininet.dl l" Alias "FtpOpenFil eA"
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long,
ByVal Flags As Long, ByVal Context As Long) As Long
Private Declare Function InternetReadFil e Lib "wininet.dl l" (ByVal hFile As
Long, ByVal sBuffer As String, ByVal lNumberOfBytesT oRead As Long,
lNumberOfBytesR ead As Long) As Integer
Private Declare Function InternetWriteFi le Lib "wininet.dl l" (ByVal hFile As
Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long,
dwNumberOfBytes Written As Long) As Integer
Private Declare Function FtpDeleteFile Lib "wininet.dl l" Alias
"FtpDeleteFileA " (ByVal hFtpSession As Long, ByVal lpszFileName As String)
As Long

Public Function FTPConnect(FSer ver As String, _
FUser As String, _
FPass As String) As Boolean
'// Connection au FTP
FTPConnect = True
FTP_Server = FServer
FTP_User = FUser
FTP_PassW = FPass

DoCmd.Hourglass True

hOpen = InternetOpen(FT P_UAgent, INTERNET_OPEN_T YPE_DIRECT, vbNullString,
vbNullString, 0)

If hOpen <> 0 Then
hConnection = InternetConnect (hOpen, FTP_Server, _
INTERNET_INVALI D_PORT_NUMBER, _
FTP_User, _
FTP_PassW, _
INTERNET_SERVIC E_FTP,
INTERNET_FLAG_P ASSIVE, 0)
If hConnection = 0 Then
MsgBox "Impossible de se connecter au serveur FTP avec le nom
d'utilisateur et le mot de passe spécifiés.", vbExclamation, "Erreur de
connection"
FTPDisconnect
FTPConnect = False
End If
Else
MsgBox "Impossible d'ouvrir votre connection Internet.",
vbExclamation, "Erreur de connection"
FTPConnect = False
End If

DoCmd.Hourglass False
End Function

Public Function DownloadFTPFile s(strFile As String, strNewFile As String,
lngFileSize As Long) As Boolean
'// Télécharge un fichier sur un site FTP
Dim hFile As Long
Dim sBuffer As String
Dim sReadBuffer As String * 4096 'par tranche de 4k
Dim lNumberOfBytesR ead As Long
Dim bDoLoop As Boolean
Dim Sum As Long

hFile = FtpOpenFile(hCo nnection, Trim(strFile), GENERIC_READ, Transfer,
0)
Open strNewFile For Binary Access Write As #2

bDoLoop = True
While bDoLoop
If StopTransfert = True Then GoTo StopGetFiles
sReadBuffer = vbNullChar
bDoLoop = InternetReadFil e(hFile, sReadBuffer, Len(sReadBuffer ),
lNumberOfBytesR ead)
sBuffer = sBuffer & Left$(sReadBuff er, lNumberOfBytesR ead)
If Not CBool(lNumberOf BytesRead) Then bDoLoop = False
Sum = Sum + lNumberOfBytesR ead
'UpdateProgress Meter "Téléchargement ", Sum, Val(lngFileSize ),
"Téléchargement ", Val(Sum)
Wend

Put #2, , sBuffer

StopGetFiles:
Close #2
InternetCloseHa ndle (hFile)

End Function

Public Function UploadFTPFiles( lFile, ShortFile, FileSize As Long)
'// Envois un fichier sur un FTP
'//Exemple :SendFTPFiles "c:\bob.exe ", "bob.exe"

Dim Data(99) As Byte ' array of 100 elements 0 to 99
Dim Written As Long
Dim Size As Long
Dim Sum As Long
Dim j As Long

Sum = 0
j = 0

hFile = FtpOpenFile(hCo nnection, ShortFile, GENERIC_WRITE, Transfer, 0)
If hFile = 0 Then Exit Function

Open lFile For Binary Access Read As #1
Size = LOF(1)
glbSize = Size

For j = 1 To Size \ 100
If StopTransfert = True Then GoTo StopSendFTPFile s
Get #1, , Data
If (InternetWriteF ile(hFile, Data(0), 100, Written) = 0) Then Exit
Function

DoEvents
Sum = Sum + 100
'UpdatePB Sum, FileSize
Next j

Get #1, , Data

If (InternetWriteF ile(hFile, Data(0), Size Mod 100, Written) = 0) Then Exit
Function

Sum = Sum + (Size Mod 100)
'UpdatePB Sum, FileSize (this is my forms progressbar)

Close #1
InternetCloseHa ndle (hFile)
Exit Function
StopSendFTPFile s:
Close #1
InternetCloseHa ndle (hFile)
hFile = FtpDeleteFile(h Connection, ShortFile)
glbSize = 0
End Function

Private Function GetStatus() As String
'// Retourne le status de le connection
Dim Buffer$, L&, Inf&, AA$

InternetGetLast ResponseInfo Inf, vbNullString, L

If Inf Then
Buffer = String(L + 1, 0)
InternetGetLast ResponseInfo Inf, Buffer, L
AA = Inf & " " & Buffer
Else
AA = " Ok"
End If

GetStatus = AA
End Function

Public Sub FTPDisconnect()
InternetCloseHa ndle hConnection
InternetCloseHa ndle hOpen
GetStatus
hConnection = 0
hOpen = 0
End Sub

Public Sub DelFTPFiles(fFi les As String)
FtpDeleteFile hConnection, fFiles
End Sub
'\\ Code End

[rdemyan via AccessMonster.com]

Merci beucoup, Robert.

I'm going to compare your code with what I have. I may need to post again,
if I need some help in translating the french error messages to english.

P.S. your English is fine!

Logipro wrote:

Hi,

Each time you put a new version of you spreadsheet, put a script files on
your FTP with a release version like 1,2,3...., and download this script and
compare this version with the local script and use my code to download if
necessary.

Best regards (sorry for my cheap english)

Robert Simard
Logipro
http://www.logicielappui.com/tips

'// Code Start
Option Compare Database
Option Explicit

Private Const MAX_PATH = 260
Private Const INTERNET_OPEN_T YPE_PRECONFIG = 0
Private Const INTERNET_OPEN_T YPE_DIRECT = 1
Private Const INTERNET_OPEN_T YPE_PROXY = 3
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const INTERNET_INVALI D_PORT_NUMBER = 0
Private Const INTERNET_SERVIC E_FTP = 1
Private Const INTERNET_SERVIC E_GOPHER = 2
Private Const INTERNET_SERVIC E_HTTP = 3
Private Const INTERNET_FLAG_P ASSIVE = &H8000000
Private Const INTERNET_FLAG_R ELOAD = &H80000000
Private Const INTERNET_FLAG_K EEP_CONNECTION = &H400000
Private Const INTERNET_FLAG_M ULTIPART = &H200000

Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Type WIN32_FIND_DATA
dwFileAttribute s As Long
ftCreationTime As FILETIME
ftLastAccessTim e As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

Global FTP_Server As String
Global FTP_User As String
Global FTP_PassW As String
Global glbSize As String
Global StopTransfert As Boolean

Const FTP_UAgent = "FTP Demo"
Dim hOpen As Long
Dim hConnection As Long
Dim Transfer As Long
Dim hFile As Long

Private Declare Function InternetOpen Lib "wininet.dl l" Alias
"InternetOpenA " (ByVal sAgent As String, ByVal lAccessType As Long, ByVal
sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As
Long
Private Declare Function InternetConnect Lib "wininet.dl l" Alias
"InternetConne ctA" (ByVal hInternetSessio n 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
Private Declare Function InternetGetLast ResponseInfo Lib "wininet.dl l" Alias
"InternetGetLa stResponseInfoA " (lpdwError As Long, ByVal lpszBuffer As
String, lpdwBufferLengt h As Long) As Long
Private Declare Function InternetCloseHa ndle Lib "wininet.dl l" (ByVal hInet
As Long) As Integer
Private Declare Function FtpOpenFile Lib "wininet.dl l" Alias "FtpOpenFil eA"
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long,
ByVal Flags As Long, ByVal Context As Long) As Long
Private Declare Function InternetReadFil e Lib "wininet.dl l" (ByVal hFile As
Long, ByVal sBuffer As String, ByVal lNumberOfBytesT oRead As Long,
lNumberOfBytes Read As Long) As Integer
Private Declare Function InternetWriteFi le Lib "wininet.dl l" (ByVal hFile As
Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long,
dwNumberOfByte sWritten As Long) As Integer
Private Declare Function FtpDeleteFile Lib "wininet.dl l" Alias
"FtpDeleteFile A" (ByVal hFtpSession As Long, ByVal lpszFileName As String)
As Long

Public Function FTPConnect(FSer ver As String, _
FUser As String, _
FPass As String) As Boolean
'// Connection au FTP
FTPConnect = True
FTP_Server = FServer
FTP_User = FUser
FTP_PassW = FPass

DoCmd.Hourglas s True

hOpen = InternetOpen(FT P_UAgent, INTERNET_OPEN_T YPE_DIRECT, vbNullString,
vbNullString , 0)

If hOpen <> 0 Then
hConnection = InternetConnect (hOpen, FTP_Server, _
INTERNET_INVALI D_PORT_NUMBER, _
FTP_User, _
FTP_PassW, _
INTERNET_SERVIC E_FTP,
INTERNET_FLAG_ PASSIVE, 0)

If hConnection = 0 Then
MsgBox "Impossible de se connecter au serveur FTP avec le nom
d'utilisateu r et le mot de passe spécifiés.", vbExclamation, "Erreur de
connection"
FTPDisconnect
FTPConnect = False
End If
Else
MsgBox "Impossible d'ouvrir votre connection Internet.",
vbExclamatio n, "Erreur de connection"
FTPConnect = False
End If

DoCmd.Hourglass False
End Function

Public Function DownloadFTPFile s(strFile As String, strNewFile As String,
lngFileSize As Long) As Boolean
'// Télécharge un fichier sur un site FTP
Dim hFile As Long
Dim sBuffer As String
Dim sReadBuffer As String * 4096 'par tranche de 4k
Dim lNumberOfBytesR ead As Long
Dim bDoLoop As Boolean
Dim Sum As Long

hFile = FtpOpenFile(hCo nnection, Trim(strFile), GENERIC_READ, Transfer,
0)
Open strNewFile For Binary Access Write As #2

bDoLoop = True
While bDoLoop
If StopTransfert = True Then GoTo StopGetFiles
sReadBuffer = vbNullChar
bDoLoop = InternetReadFil e(hFile, sReadBuffer, Len(sReadBuffer ),
lNumberOfBytes Read)
sBuffer = sBuffer & Left$(sReadBuff er, lNumberOfBytesR ead)
If Not CBool(lNumberOf BytesRead) Then bDoLoop = False
Sum = Sum + lNumberOfBytesR ead
'UpdateProgress Meter "Téléchargement ", Sum, Val(lngFileSize ),
"Téléchargemen t", Val(Sum)
Wend

Put #2, , sBuffer

StopGetFiles :
Close #2
InternetCloseHa ndle (hFile)

End Function

Public Function UploadFTPFiles( lFile, ShortFile, FileSize As Long)
'// Envois un fichier sur un FTP
'//Exemple :SendFTPFiles "c:\bob.exe ", "bob.exe"

Dim Data(99) As Byte ' array of 100 elements 0 to 99
Dim Written As Long
Dim Size As Long
Dim Sum As Long
Dim j As Long

Sum = 0
j = 0

hFile = FtpOpenFile(hCo nnection, ShortFile, GENERIC_WRITE, Transfer, 0)
If hFile = 0 Then Exit Function

Open lFile For Binary Access Read As #1
Size = LOF(1)
glbSize = Size

For j = 1 To Size \ 100
If StopTransfert = True Then GoTo StopSendFTPFile s
Get #1, , Data
If (InternetWriteF ile(hFile, Data(0), 100, Written) = 0) Then Exit
Function

DoEvents
Sum = Sum + 100
'UpdatePB Sum, FileSize
Next j

Get #1, , Data

If (InternetWriteF ile(hFile, Data(0), Size Mod 100, Written) = 0) Then Exit
Function

Sum = Sum + (Size Mod 100)
'UpdatePB Sum, FileSize (this is my forms progressbar)

Close #1
InternetCloseH andle (hFile)
Exit Function

StopSendFTPFil es:
Close #1
InternetCloseHa ndle (hFile)
hFile = FtpDeleteFile(h Connection, ShortFile)
glbSize = 0
End Function

Private Function GetStatus() As String
'// Retourne le status de le connection
Dim Buffer$, L&, Inf&, AA$

InternetGetLas tResponseInfo Inf, vbNullString, L

If Inf Then
Buffer = String(L + 1, 0)
InternetGetLast ResponseInfo Inf, Buffer, L
AA = Inf & " " & Buffer
Else
AA = " Ok"
End If

GetStatus = AA
End Function

Public Sub FTPDisconnect()
InternetCloseHa ndle hConnection
InternetCloseHa ndle hOpen
GetStatus
hConnection = 0
hOpen = 0
End Sub

Public Sub DelFTPFiles(fFi les As String)
FtpDeleteFile hConnection, fFiles
End Sub
'\\ Code End

--
Message posted via http://www.accessmonster.com