Solved

MS Access VBA--Check if there's an internet connection.

Posted on 2016-11-17
12
42 Views
Last Modified: 2016-11-28
I made an Access 2013 program which uses MS Azure for the SQL backend.  

Before a report (or other functions) is executed the code was checking to make sure there was a connection to the internet by using InternetGetConnectedState(0&, 0&).  I thought this was a good solution but discovered that this code only works if you unplug the Ethernet cable from your computer or disable the wireless in the "Network Connections."  

In other words when the internet went down the above code would give a false positive that there was still internet connectivity because I was still connected to my router.  And with no internet access the report locks up because it can't communicate with Azure.

So I need VBA code to check in the background if the internet is available, maybe a simple ping to www.msn.com.  If "true" then I can let the report execute, if "false" then a msgbox would tell the user that the internet is down.

Thanks!!!
0
Comment
Question by:steve lemmon
  • 6
  • 4
  • 2
12 Comments
 
LVL 57
ID: 41891732
This:

http://www.freevbcode.com/ShowCode.asp?ID=199

 Has a ping function.   Although it's in VB6, it should drop right into VBA.

 Let me know if you have any issues with it.

Jim.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41891755
Hmm, just a link to a remote site with downloads as an answer - I've been bashed for that ...

/gustav
0
 
LVL 57
ID: 41891775
Just a note that I tried the ping function in that lib and it worked fine.   I only had to comment out one call to the App.Logevent:

Private Sub SocketsCleanup()
   
If WSACleanup() <> ERROR_SUCCESS Then
    'App.LogEvent "Socket error occurred in Cleanup.", vbLogEventTypeError
End If

End Sub

I know it's solid to because this is the same ping routine included with vbSendMail, which has been used for years.

But do note that using a ping is not always reliable as many sites don't accept ping requests, so make sure you pick a site that does allow for pings.

Jim.
0
 
LVL 57
ID: 41891779
@gustav,

<<Hmm, just a link to a remote site with downloads as an answer - I've been bashed for that ...>>

 Depends on the site and if you add anything to it.  In this case, it's not another Q&A site, and I indicated that I knew it had what they needed.

Jim.
0
 

Author Comment

by:steve lemmon
ID: 41891831
Jim, thanks but even when I comment out the above there are more than 400 lines left.  Can you just copy a minimum amount and paste them here.  I just need the code to see if there's something out there beyond the router.  Many thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41891834
That's what I was told. A link is not an answer.

/gustav
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 57
ID: 41891864
<<That's what I was told. A link is not an answer.>>

 That is not true.  In most cases it is not, but there are occasions where a straight forward question is asked, and the best way to answer it is with a link and some commentary.

 What's frowned upon is answering a question with "this might help" and a link or in other words, a shot in the dark.

 Send me a PM with the question where this happened if you can remember it and I'll have a look at it.  I can then follow-up with whoever.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41894979
Attached is a sample database.    It has a single module in it.   Import the module into your DB.

 Then where you need a ping:

 Dim  lngRet as long

 lngRet = Ping("www.somewebsiteorhost.com","",False)

 If lngRet <> 0 then
    msgbox "Can't reach www.somewebsiteorhost.com", vbExclamimation + vbOKOnly,""
End If

Jim.
TCPIP.accdb
0
 

Author Closing Comment

by:steve lemmon
ID: 41896610
Works like a charm!!!  It's very quick and accurate.  

Anyone who uses Access with a cloud backend such as Azure, SharePoint, or any other should use this solution.  

Many thanks to Jim Dettman.
0
 

Author Comment

by:steve lemmon
ID: 41899674
Upon using the accepted solution for a while I started getting "false negatives," meaning the code would tell me the internet is down when it wasn't.  I thought that maybe it had something to do with the ping response time so I did 2 things.

First I was pinging www.yahoo.com but found that www.msn.com responds about twice as fast.
Second I delayed the code a quarter second by inserting the indented code.  Since then I haven't had a problem.  If I have problems in the future I'll try increasing the pause time a little.

    Dim lngRet As Long
    lngRet = Ping("www.msn.com", "", False)
        Dim PauseTime, Start
        PauseTime = 0.25 ' Set duration in seconds
        Start = Timer ' Set start time.
        Do While Timer < Start + PauseTime
        DoEvents ' Yield to other processes.
        Loop
    If lngRet <> 0 Then 'no internet connection
    MsgBox "This function does not work when the internet is down."
    Exit Sub
    End If
0
 
LVL 57
ID: 41899707
Steve,

 The loop there is doing nothing.   You can take it out.  The variable lngRet is already set.   Doesn't matter how long you wait.

 <<Upon using the accepted solution for a while I started getting "false negatives," meaning the code would tell me the internet is down when it wasn't. >>

  Relying on a Ping is problematic.   Not all sites return a ping and some routers/firewalls might start blocking a port if it sees repeated requests.  There are also transient internet problems where you might not reach a particular site.  Your also relying on DNS and there could be issues with that.   So what I would do is this:

    lngRet = Ping("www.msn.com", "", False)
    If lngRet <> 0 Then
        lngRet = Ping("www.google.com", "", False)
        If lngRet <> 0 Then
          lngRet = Ping("www.someothermajorsite.com", "", False)
          If lngRet <> 0 Then
            MsgBox "This function does not work when the internet is down."
          End If
       End If
    End If

If after attempting to ping three sites that normally take ping requests, then something is wrong.    

Jim.

PS. and no need to PM me if you post a comment here....I get a notification.
0
 

Author Comment

by:steve lemmon
ID: 41900670
Your updated code is awesome.  I am very grateful and will be using it.

I embellished it slightly for testing purposes only.  Since CenturyLink is my ISP I decided to make it the primary ping.  (Don't put an IP in for the URLs below...it will give you a false positive.)

Dim lngRet As Long
  lngRet = Ping("www.centurylink.com", "", False)
    If lngRet <> 0 Then
     lngRet = Ping("www.google.com", "", False)
        If lngRet <> 0 Then
            lngRet = Ping("www.msn.com", "", False)
            If lngRet <> 0 Then
                MsgBox "Internet down."
            Else
                MsgBox "Internet on MSN.com"
            End If
        Else
            MsgBox "Internet on Google.com"
        End If
    Else
        MsgBox "Internet on CenturyLink.com"
    End If
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now