asked on

check if connected to the internet

I have the following code but i need to amend it. sometimes my broadband connection drops, or the pc has shut down and restarted.

I need to be able to check that the connection to the sharepoint site is open and if it is not then to send me an email.

is this possible?

Public Function Command()
   Dim sSql As String
   Dim rs As New ADODB.Recordset
   Dim sFilename As String
   Dim fhFile As Integer
   Dim sLine As String

   sFilename = "\\sharepoint.bt.com@SSL\DavWWWRoot\sites\Ops_Storage_Area\Shared Documents\textfile.txt"
   fhFile = FreeFile
   Open sFilename For Output As #fhFile

   sSql = "SELECT * FROM TblXMLData ORDER BY [sent] DESC"
   rs.Open sSql, CurrentProject.Connection, , adLockOptimistic, adCmdText
   Do While Not (rs.EOF)
      sLine = "<div>" & vbCrLf & "<div class=""message"">" & vbCrLf & "<br />" & _
rs![Sent] & "," & rs![Incident] & "," & rs![Subject] & vbCrLf & "</div>" & vbCrLf
      Print #fhFile, sLine
      rs.MoveNext
   Loop
   rs.Close
   Set rs = Nothing
   Close fhFile
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Dezzar82

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bryan Scott

ASKER

Dezzar82, thanks for this. is there any way it could send an email and then close the database?

Dezzar82

sure...

at what point do you want it to send en email?

Bryan Scott

ASKER

thanks, rather than the msg box, just in there and then the next step is to close it down?

Dezzar82

ok, but if your not connected to the network you email probably wont work either...

however. first thing is you will need a module to create and send an email (if you don't have one already). I use lotus notes here at my company so all my email code probably wont work for you.

Do a search for a decent bit of code to send emails.

I found a bit of code and have modified it a little to work as a simple function. I can't test it so give it a go and let me knnow if you need anymore assistance with it. Outlook would probably already need to be open for this to work.

Place this in a module:

Function SendMail(strTO As String, strSubject As String, strBody As String)
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = strTO
        .Subject = strSubject
        .Body = strBody
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

Open in new window

And instead of the message box in your code above place:

Else
      Call SendMail("you@mail.com","Application Error!","Application has closed due to connectivity error!")
      DoCmd.Quite 'if you want the entire application to shutdown or
      'DoCmd.Close acForm, Me.Form.Name  if you just want to close the current form
      Exit Function
End If

Open in new window

p912s

When working with remote computers over the internet I generally use a simple ping command in my code to either wait for or force a connection to restart.

As for sending email, I don't always have access to a local mail server so I use a small executable written in VB6 that sends a message using blat & stunnel thru gmail.com. Stunnel is required to make a secure connection to gmail.

Hope that helps.

Scot

Bryan Scott

ASKER

thanks for all you help this is great

Dezzar82

no worries Bryan! your welcome.

Cheers
DeZZar