Connecting to a server

im using the exportXML command in Microsoft Access 2003 to export a .xml file to my website.

I have been experiementing with the exportXML command and have been able to export a table to a folder at my website. The problem is that before the table is exported i get a connect to website dialog box where you enter your server user name and password. Once submitted the .xml file is exported successfully.

I would like to write some code that hides the connect to website dialog box and fills in the information automatically so users of my database dont see the connection.

I have searched and search the net and are struggling to find example code to help.

Can anyone point me in the right direction please

Answer this question

Connecting to a server

ntalb

thanks for the info.

I'll copy this code into a class module and i'll have a read to try and understand it.

How would i initalise the code via a command button For instant if i have a command button that connects to my server, which part of the class module do i point to
DumbLuck61
Alot to read through, I know, but "Do it properly and only do it once" as the saying goes :-)
Wriju
I would write the file to my local hard drive and use the ftp My.Computer.Network.UploadFile to transfer the file to my website.
gon2024

Yeah dude, sorry. I thought you wanted VB.NET code to do this stuff... My bad...

If you DO end up using it, it's simply a matter of creating a new object based on your class

Dim F as new ClassFTP
MON205

After reading the code i assume i point to the Public Function ConnectToServe in order to create a connection.

Please confirm if im right
nardev
Also not that the My classes are a new feature in VB Express/2005
William Vaughn
The code is VB.net code

These forums are for VB.NET questions. Access uses a version of VB called Visual Basic for Applications. VBA is a very different product from VB.NET and there are some other locations where you will probably get a better response to your VBA questions.

If your trying to do this all in Access 2003 then you will need to write VBA code to achieve this. So this code will not work.

You may find more assistance in following which specifically deals with MS Office development.

VBA Forum

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1

Office Automation: office.developer.automation newsgroup

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.office.developer.automation&lang=en&cr=US

Or the Office Newgroups

http://www.microsoft.com/office/community/en-us/default.mspx d=1

Let us know if you are using VB.NET and if so which version or if you are trying to do this all in Access
Chris Walters
i have tried to compile this code and im getting loads of compile errors is this code suitable for Access2003
philipk

You could always just pass the logon credentials in the request string...

Example URLs:

ftp://username:password@ftpdomain.com
http://username:password@webdomain.com

The user credentials will be passed in the request URL. Yes, this looks unsecure, and it is if your not using SSL. But then again all username/password trasmissions are unsecure unless you use SSL.
BearShare

Hi Stujol, hopefully this code helps you out. It's a class module with all the FTP functionality you should need. And yes, I've been told MANY times that I have faaaar too much time on my hands :-)

Imports System.Runtime.InteropServices
Public Class FTP
Private Const FTP_TRANSFER_TYPE_UNKNOWN = &H0
Private Const FTP_TRANSFER_TYPE_BINARY = &H2
Private Const FTP_TRANSFER_TYPE_ASCII = &H1
Private Const INTERNET_OPEN_TYPE_DIRECT = 1
Private Const INTERNET_SERVICE_FTP = 1
Private Const INTERNET_FLAG_PASSIVE = &H8000000
Private Const FILE_ATTRIBUTE_READONLY = &H1
Private Const FILE_ATTRIBUTE_HIDDEN = &H2
Private Const FILE_ATTRIBUTE_SYSTEM = &H4
Private Const FILE_ATTRIBUTE_DIRECTORY = &H10
Private Const FILE_ATTRIBUTE_ARCHIVE = &H20
Private Const FILE_ATTRIBUTE_NORMAL = &H80
Private Const FILE_ATTRIBUTE_TEMPORARY = &H100
Private Const FILE_ATTRIBUTE_COMPRESSED = &H800
Private Const FILE_ATTRIBUTE_OFFLINE = &H1000

Private Declare Function InternetOpen Lib "wininet.dll" Alias _
   "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As _
   Int32, ByVal sProxyName As String, ByVal sProxyBypass As String _
   , ByVal lFlags As Int32) As Int32
Private Declare Function InternetConnect Lib "wininet.dll" Alias _
   "InternetConnectA" (ByVal hInternetSession As Int32, ByVal _
   sServerName As String, ByVal nServerPort As Integer, ByVal _
   sUserName As String, ByVal sPassword As String, ByVal lService _
   As Int32, ByVal lFlags As Int32, ByVal lContext As Int32) As Int32
Private Declare Function InternetCloseHandle Lib "wininet.dll" _
   (ByVal hInet As Int32) As Int32
Private Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
   Alias "InternetGetLastResponseInfoA" (ByRef lpdwError As Int32, _
   ByVal lpszBuffer As String, ByRef lpdwBufferLength As Int32) As Boolean
Private Declare Function FtpSetCurrentDirectory Lib "wininet.dll" _
   Alias "FtpSetCurrentDirectoryA" (ByVal hFtpSession As Int32, ByVal _
   lpszDirectory As String) As Boolean
Private Declare Function FtpGetCurrentDirectory Lib "wininet.dll" _
   Alias "FtpGetCurrentDirectoryA" (ByVal hFtpSession As Int32, ByVal _
   lpszCurrentDirectory As String, ByVal lpdwCurrentDirectory As Int32) _
   As Int32
Private Declare Function FtpCreateDirectory Lib "wininet.dll" Alias _
   "FtpCreateDirectoryA" (ByVal hFtpSession As Int32, ByVal _
   lpszDirectory As String) As Boolean
Private Declare Function FtpRemoveDirectory Lib "wininet.dll" Alias _
   "FtpRemoveDirectoryA" (ByVal hFtpSession As Int32, ByVal _
   lpszDirectory As String) As Boolean
Private Declare Function FtpDeleteFile Lib "wininet.dll" Alias _
   "FtpDeleteFileA" (ByVal hFtpSession As Int32, ByVal lpszFileName As _
   String) As Boolean
Private Declare Function FtpRenameFile Lib "wininet.dll" Alias _
   "FtpRenameFileA" (ByVal hFtpSession As Int32, ByVal lpszExisting As _
   String, ByVal lpszNew As String) As Boolean
Private Declare Function FtpPutFile Lib "wininet.dll" Alias _
   "FtpPutFileA" (ByVal hFtpSession As Int32, ByVal lpszLocalFile As _
   String, ByVal lpszRemoteFile As String, ByVal dwFlags As Int32, _
   ByVal dwContext As Int32) As Boolean
Private Declare Function FtpGetFile Lib "wininet.dll" Alias _
   "FtpGetFileA" (ByVal hConnect As Int32, ByVal lpszRemoteFile As _
   String, ByVal lpszNewFile As String, ByVal fFailIfExists As _
   Int32, ByVal dwFlagsAndAttributes As Int32, ByVal dwFlags As Int32, _
   ByRef dwContext As Int32) As Boolean
Private Declare Auto Function FtpFindFirstFile Lib "wininet.dll" Alias _
   "FtpFindFirstFileA" (ByVal hFtpSession As Int32, ByVal _
   lpszSearchFile As String, ByRef lpFindFileData As _
   WIN32_FIND_DATA, ByVal dwFlags As Int32, ByVal dwContent As Int32) _
   As Int32
Private Declare Auto Function InternetFindNextFile Lib "wininet.dll" _
   Alias "InternetFindNextFileA" (ByVal hFind As Int32, ByRef _
   lpvFindData As WIN32_FIND_DATA) As Int32
Private lngInternetSession As Int32
Private lngFTPSession As Int32
<MarshalAs(UnmanagedType.LPTStr)> Private strErr As String
Private mTransferType As FileTransferType = FileTransferType.ftpBinary
Public Event LastResponse(ByVal strResponse As String)
Const MAX_PATH = 260

Public Enum FileTransferType
   ftpUnknown = FTP_TRANSFER_TYPE_UNKNOWN
   ftpAscii = FTP_TRANSFER_TYPE_ASCII
   ftpBinary = FTP_TRANSFER_TYPE_BINARY
End Enum

Public Enum ConnectionModes
   Active = 0
   Passive = INTERNET_FLAG_PASSIVE
End Enum

Private Structure FILETIME
   Private dwLowDateTime As Int32
   Private dwHighDateTime As Int32
End Structure

Private Structure WIN32_FIND_DATA
   Dim dwFileAttributes As Integer
   Dim ftCreationTime As FILETIME
   Dim ftLastAccessTime As FILETIME
   Dim ftLastWriteTime As FILETIME
   Dim nFileSizeHigh As Integer
   Dim nFileSizeLow As Integer
   Dim dwReserved0 As Integer
   Dim dwReserved1 As Integer

   <VBFixedString(MAX_PATH), System.Runtime.InteropServices.MarshalAs _
      (System.Runtime.InteropServices.UnmanagedType.ByValTStr, _
      SizeConst:=MAX_PATH)> Public cFileName As String
   <VBFixedString(14), System.Runtime.InteropServices.MarshalAs _
      (System.Runtime.InteropServices.UnmanagedType.ByValTStr, _
      SizeConst:=14)> Public cAlternate As String
End Structure

Public Property TransferType() As FileTransferType
   Get
      Return mTransferType
   End Get

   Set(ByVal Value As FileTransferType)
      mTransferType = Value
   End Set
End Property

Public Function ConnectToServer(ByVal Server As String, Optional ByVal _
   Port As Int32 = 21, Optional ByVal ConnectionMode As ConnectionModes _
   = ConnectionModes.Active, Optional ByVal Username As String = "", _
   Optional ByVal Password As String = "") As Boolean

   Dim blnRet As Boolean

   Try
      CloseConnection()
      If Username = "" Then Username = Anonymous@Anonymous.com
      lngInternetSession = InternetOpen("FTPPUT", _
      INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0)
      If lngInternetSession <> 0 Then
         lngFTPSession = InternetConnect(lngInternetSession, Server, _
         Port, Username, Password, INTERNET_SERVICE_FTP, _
         ConnectionMode, 0)
      If lngFTPSession <> 0 Then blnRet = True
   End If
   Return blnRet
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Sub CloseConnection()
   If lngFTPSession <> 0 Then InternetCloseHandle(lngFTPSession)
   If lngInternetSession <> 0 Then InternetCloseHandle(lngInternetSession)
End Sub

Public Function PutFile(ByVal LocalFilePath As String, ByVal _
   RemoteFilename As String) As Boolean
   Try
   Return FtpPutFile(lngFTPSession, LocalFilePath, RemoteFilename, _
   TransferType, 0)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function GetFile(ByVal LocalFilePath As String, ByVal _
   RemoteFilename As String) As Boolean
   Try
   If Right(LocalFilePath, 1) <> "\" Then LocalFilePath = _
      LocalFilePath & "\"
   Return FtpGetFile(lngFTPSession, RemoteFilename, LocalFilePath & _
      RemoteFilename, False, 0, TransferType, 0)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function DeleteFile(ByVal strFile As String) As Boolean
   Try
   Return FtpDeleteFile(lngFTPSession, strFile)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function RenameFile(ByVal oldFileName As String, ByVal _
   NewFileName As String) As Boolean
   Try
   Return FtpRenameFile(lngFTPSession, oldFileName, NewFileName)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function CreateDirectory(ByVal DirectoryName As String) As Boolean
   Try
   Return FtpCreateDirectory(lngFTPSession, DirectoryName & Chr(0))
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function RemoveDirectory(ByVal DirectoryName As String) As Boolean
   Try
   Return FtpRemoveDirectory(lngFTPSession, DirectoryName)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function GetFileList() As Object
   Dim pData As WIN32_FIND_DATA, hFind As Int32, lRet As Int32
   Dim ReturnFile() As String, cnt As Int32
   Dim Filename As String, blnFind As Boolean
   Try
      pData.cAlternate = New String(Chr(0), 14)
      pData.cFileName = New String(Chr(0), MAX_PATH)
      hFind = FtpFindFirstFile(lngFTPSession, "*.*", pData, 0, 0)
      If hFind = 0 Then Exit Function
      If pData.dwFileAttributes <> FILE_ATTRIBUTE_DIRECTORY Then
         Filename = Left(pData.cFileName.ToString, pData.cFileName.Length)
         If Filename <> "System Volume Information" And Filename <> _
            "RECYCLER" Then
            ReDim Preserve ReturnFile(cnt)
            ReturnFile(cnt) = Filename
            blnFind = True
            cnt += 1
         End If
      End If
   Do
      pData.cFileName = New String(Chr(0), MAX_PATH)
      lRet = InternetFindNextFile(hFind, pData)
      If lRet = 0 Then Exit Do
      If pData.dwFileAttributes <> FILE_ATTRIBUTE_DIRECTORY Then
         Filename = Left(pData.cFileName.ToString, pData.cFileName.Length)
         ReDim Preserve ReturnFile(cnt)
         ReturnFile(cnt) = Filename
         blnFind = True
         cnt = cnt + 1
      End If
   Loop

   InternetCloseHandle(hFind)
   If blnFind Then GetFileList = ReturnFile
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   End Try
End Function

Public Function GetDirectoryList() As Object
   Dim pData As WIN32_FIND_DATA, hFind As Int32, lRet As Int32
   Dim ReturnFile() As String, cnt As Int32
   Dim Filename As String
   Try
   ReDim Preserve ReturnFile(cnt)
   ReturnFile(cnt) = ".."
   cnt += 1
   pData.cAlternate = New String(Chr(0), 14)
   pData.cFileName = New String(Chr(0), MAX_PATH)
   hFind = FtpFindFirstFile(lngFTPSession, "*.*", pData, 0, 0)
   If hFind = 0 Then
      Return ReturnFile
      Exit Function
   End If
   If pData.dwFileAttributes = FILE_ATTRIBUTE_DIRECTORY Then
      Filename = Left(pData.cFileName.ToString, pData.cFileName.Length)
      If Filename <> "System Volume Information" And Filename <> _
      "RECYCLER" And Filename <> ".." Then
         ReDim Preserve ReturnFile(cnt)
         ReturnFile(cnt) = Filename
         cnt += 1
      End If
   End If
   Do
      pData.cFileName = New String(Chr(0), MAX_PATH)
      lRet = InternetFindNextFile(hFind, pData)
      If lRet = 0 Then Exit Do
      If pData.dwFileAttributes = FILE_ATTRIBUTE_DIRECTORY Then
         Filename = Left(pData.cFileName.ToString, pData.cFileName.Length)
         If Filename <> "System Volume Information" And Filename <> _
         "RECYCLER" And Filename <> ".." Then
            ReDim Preserve ReturnFile(cnt)
            ReturnFile(cnt) = Filename
            cnt = cnt + 1
         End If
     End If
   Loop
   InternetCloseHandle(hFind)
   Return ReturnFile
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   End Try
End Function

Public Function SetWorkingDir(ByVal WorkingDir As String) As Boolean
   Try
   Return FtpSetCurrentDirectory(lngFTPSession, WorkingDir)
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   Finally
      GetLastInternetResponse()
   End Try
End Function

Public Function GetLastInternetResponse() As String
   Dim lErr As Int32
   Dim l As Int32
   Try
   InternetGetLastResponseInfo(lErr, strErr, l)
   strErr = New String(Chr(0), l + 1)
   InternetGetLastResponseInfo(lErr, strErr, l)
   RaiseEvent LastResponse(strErr.ToString)
   Return strErr.ToString
   Catch ex As Exception
   Throw New Exception(ex.Message, ex)
   End Try
End Function

Protected Overrides Sub Finalize()
   CloseConnection()
   MyBase.Finalize()
End Sub

End Class
Connecting to a server

Answer this question