Link to home
Create AccountLog in
Avatar of Bryan Scott
Bryan ScottFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of Dezzar82
Dezzar82
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Bryan Scott

ASKER

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

at what point do you want it to send en email?
thanks, rather than the msg box, just in there and then the next step is to close it down?
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



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

thanks for all you help this is great
no worries Bryan!  your welcome.

Cheers
DeZZar