MS Access: Internet connectivity check using VBA

Hello Experts,

I am trying to compile some simple VBA code to check internet connectivity of the PC before the execution of a subroutine that sends emails via remote SMTP server. I have found that Access freezes if it tries to send emails when not connected to the internet, hence the desire for a connectivity pre-check. The idea is that if there is no internet connection then Access does not attempt to send the emails. For testing purposes I am trying to develop the connectivity check code behind a command button click.
I have found a few articles that deal with checking internet connectivity in VBA, but nothing that I can get to work. I am trying to adapt the following code (from: https://bytes.com/topic/access/answers/944317-test-internet-connection-within-access), which -if I can only get it to work- would seem to be the simplest solution:

Private Sub NetworkConnection_Click()
Public Declare Function InternetGetConnectedState Lib "wininet.dll" ()
If InternetGetConnectedState(0&, 0&) Then
MsgBox "Connected to internet"
Else
MsgBox "Not Connected to internet"
End If
End Sub

When this is run, I get an error saying that "only comments can be written after End Sub, End Function or End Property". If anyone could help out on this I would be most grateful ! I am using MS Access 2013 / Windows 7 professional 64-bit.
Paul McCabeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try
'This must be at the beginning of your module
Private Declare Function InternetGetConnectedState Lib "wininet.dll" _
(ByRef dwflags As Long, ByVal dwReserved As Long) As Long

Private Sub NetworkConnection_Click()

 If InternetGetConnectedState(0&, 0&) Then
 MsgBox "Connected to internet"
 Else
 MsgBox "Not Connected to internet"
 End If
 End Sub

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperCommented:
Try this function ...

If IsInternetConnected() = False Then
    MsgBox "Cannot establish a connection to Internet.  Check your Internet connection status.", vbInformation, ""
    Exit Sub
End If

Function IsInternetConnected() As Boolean
    Dim L As Long
    Dim R As Long
    R = InternetGetConnectedState(L, 0&)
    If R = 0 Then
        IsInternetConnected = False
    Else
        If R <= 4 Then
            IsInternetConnected = True
        Else
            IsInternetConnected = False
        End If
    End If
End Function

ET
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< "only comments can be written after End Sub,>>

 You've got an un-paired end sub/function statement in the module.

 All your code needs to be between a Sub/function and end sub/function and they must be paired evenly.

Jim.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Eric ShermanAccountant/DeveloperCommented:
Also ... add this at the beginning of your module if you select to use my solution.

Public Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef dwflags As Long, ByVal dwReserved As Long) As Long

ET
Paul McCabeAuthor Commented:
Thank you all for your answers. I am not using a module, so in this case, where should the following statement be placed ?

Public Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef dwflags As Long, ByVal dwReserved As Long) As LongOption

I tried placing it under the "Compare Database" line at the top of the code, but unfortunately that didn't do the trick.
Rgonzo1971Commented:
the function definition must be inserted at the beginning of the Form module in your case ( it is a module as well)
Paul McCabeAuthor Commented:
Got it ! Works perfectly. Thanks a lot guys.
Eric ShermanAccountant/DeveloperCommented:
Thanks for the points ... Glad I could help.

ET
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.