Solved Checking network connection in split database setup (1 Viewer)

[joeyd11ph]

Good Day To All,

Below is a copy of a Sub to check whether connected or not to the network.
This is ok, only I need to hide the command window during execution or to work in the background only.


Private Sub btnEXIT_Click()

Dim stdOut, objScriptExec, strPingResults
Set objShell = CreateObject("WScript.Shell")
On Error Resume Next
Set objScriptExec = objShell.Exec("ping -n 2 -w 500 " & "192.168.100.7")
strPingResults = LCase(objScriptExec.stdOut.ReadAll)
Set stdOut = Nothing
Set objScriptExec = Nothing
If InStr(strPingResults, "reply from") Then
MsgBox "ConnectionOK"
Else
MsgBox "Connection NOK"
End If
End Sub

I'd tried inserting (,0)

" Set objScriptExec = objShell.Exec("ping -n 2 -w 500 " & "192.168.100.7", 0)"

yes, it's gone but always "Connection NOK".

Any help/tips will be highly appreciated.


As usual, advance thanks to all for the continued support to the novice like me.
More power guys...

Joey

 

[theDBguy]

Hi Joey. Not in front of a computer now; so just as a guess, try using Run instead of Exec. Hope it helps...

 

[joeyd11ph]

Thanks for prompt reply, theDBguy, I forgot to mention that I tried that but the same always "Connection NOK".

 

[theDBguy]

Thanks for prompt reply, theDBguy, I forgot to mention that I tried that but the same always "Connection NOK".

When I get a chance to get in front of a computer today, I'll try it out and let you know if I find anything useful.

 

[joeyd11ph]

Thanks a lot theDBguy...

 

[moke123]

Not sure this addresses your issue but here's a procedure I use

Option Compare Database
Option Explicit

'paste this code in at the top of your module (it will not work elsewhere)
Private Declare Function InternetGetConnectedState Lib "wininet.dll" (ByRef dwflags As Long, ByVal dwReserved As Long) As Long

Private Const INTERNET_CONNECTION_MODEM As Long = &H1
Private Const INTERNET_CONNECTION_LAN As Long = &H2
Private Const INTERNET_CONNECTION_PROXY As Long = &H4
Private Const INTERNET_CONNECTION_OFFLINE As Long = &H20

'paste this code in anywhere
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 Function

'your main function/calling function would look something like this
Private Sub btnInternetFunction_Click()
    If IsInternetConnected() = True Then
        MsgBox ("You are connected to the Internet")
        'code to execute Internet-required function here
    Else
        MsgBox ("You are not connected to the Internet or there is an issue with your Internet connection.")
    End If
End Sub

 

[zeroaccess]

I'm interested in this, too. When do you run this code? Only at startup, or on events? What if the connection goes down in the middle of use?

 

[joeyd11ph]

Thanks moke123 for your reply, My setup is P2P only, no internet connection involved, i just want check LAN connection not internet connection.

 

[HiTechCoach]

Just because a computer will respond to a ping. does not mean the shared resource is available.

What I do is open a text file in the folder with the back end. If it is successful, then the back end should be available. This could be a dummy file with any extension, an INI file, etc.

I actually use an INI file. It has a flag to tell the front end if the back end is offline.

 

[zeroaccess]

And when is this code run? I can think of several times you'd want to check for this.

 

[theDBguy]

Thanks a lot theDBguy...

Hi Joey. I've been experimenting, and the only thing I could find to help (sometimes) is to reduce the timeout down to 200 ms. Have you tried to find a good minimum timeout for your network? Just a thought... (I couldn't find a way, yet, to hide the shell window)

 

[isladogs]

Just a thought.
Why not check for the file size or date modified of a specified file on the network.
If a value is returned, you have a network connection.
If an error occurs, you don't

 

[The_Doc_Man]

I think I have to agree with Boyd, theDBguy, and Colin. Do some function like a cross-network directory scan for a directory that can't really be empty. Either it works or it doesn't. Be sure to have error trapping enabled around the test because otherwise you will get an unhandled exception and Access will crash or take a debugger break on you.

 

[joeyd11ph]

Hi Joey. I've been experimenting, and the only thing I could find to help (sometimes) is to reduce the timeout down to 200 ms. Have you tried to find a good minimum timeout for your network? Just a thought... (I couldn't find a way, yet, to hide the shell window)

Hi theDBguy, All

I play with a timeout but no luck.

My apology to all by not divulging all the steps I have taken.

Have tried all the suggestions here before I post but all of them have pros and cons:

1. checking by file >>slow response (>1min) if FE PC is off already (maybe retrying several times)
2. checking by ping >> fast response but with the command window
3. change Exec to Run >> no luck
4. insert ",0" >> no luck

I chose 2 but I need to really hide the command window for a smooth professional look.

Please HELP...

Thanks

 

[zeroaccess]

Create a .bat file that runs your desired routine, create a shortcut to the .bat file, and set the properties of the shortcut to open minimized. Have Access launch the shortcut.

 

[isladogs]

Or as another alternative...
Have a small linked table that is stored in a network table e.g. In the backend.
At startup run a DCount on that table.
If a value is returned, your connection is OK. IF it errors, no connection is available...show a message if so.
That should be fast.

BTW, if it helps, for a similar reason I have a routine that checks if SQL Server is running on the network.
You may be able to adapt how I do that for your purposes

 

[Minty]

This is what I used to check that the local SQL server was online. It is ping based, so the caveats highlighted by others apply.

Function SystemOnline(ByVal ComputerName As String)
    ' This function returns True if the specified host could be pinged.
    ' HostName can be a computer name or IP address.
    ' The Win32_PingStatus class used in this function requires Windows XP or later.
    ' Standard housekeeping
    Dim colPingResults As Variant
    Dim oPingResult As Variant
    Dim strQuery As String
    ' Define the WMI query
    strQuery = "SELECT * FROM Win32_PingStatus WHERE Address = '" & ComputerName & "'"
    ' Run the WMI query
    Set colPingResults = GetObject("winmgmts://./root/cimv2").ExecQuery(strQuery)
    ' Translate the query results to either True or False
    For Each oPingResult In colPingResults
        If Not IsObject(oPingResult) Then
            SystemOnline = False
        ElseIf oPingResult.StatusCode = 0 Then
            SystemOnline = True
        Else
            SystemOnline = False
        End If
    Next
End Function

Takes about 4-5 seconds to respond - no command window.
Works on Windows 10

 

[joeyd11ph]

This is what I used to check that the local SQL server was online. It is ping based, so the caveats highlighted by others apply.

Function SystemOnline(ByVal ComputerName As String)
    ' This function returns True if the specified host could be pinged.
    ' HostName can be a computer name or IP address.
    ' The Win32_PingStatus class used in this function requires Windows XP or later.
    ' Standard housekeeping
    Dim colPingResults As Variant
    Dim oPingResult As Variant
    Dim strQuery As String
    ' Define the WMI query
    strQuery = "SELECT * FROM Win32_PingStatus WHERE Address = '" & ComputerName & "'"
    ' Run the WMI query
    Set colPingResults = GetObject("winmgmts://./root/cimv2").ExecQuery(strQuery)
    ' Translate the query results to either True or False
    For Each oPingResult In colPingResults
        If Not IsObject(oPingResult) Then
            SystemOnline = False
        ElseIf oPingResult.StatusCode = 0 Then
            SystemOnline = True
        Else
            SystemOnline = False
        End If
    Next
End Function

Takes about 4-5 seconds to respond - no command window.
Works on Windows 10

S O L V E D !!!

Thank You Very Much Minty
and to all who shared their ideas.

I did the right thing by consulting you guys.....

More power to my ACCESS home.