CODEWITHSUNDEEP
sql-servervbaexcelvpn
Tom
Tom

Reputation: 9878

VBA to get Computers VPN State

I'm trying to build into my code a way of seeing if the computer can reach the SQL Server that it accesses.

The issue is that I can't just get the network state as to reach the SQL Server the computer needs to be on the VPN (through Cisco)

I've seen the Cisco API reference in VBA but have no idea how to use this, and also not 100% sure this would be a fail safe method of testing

I've had two ideas of doing this:

  1. Ping the SQL Server (my preferred) - I've tried using this code but it just comes back with "passed" whether the server is connected or not

My function:

Sub test()
' SQL Server address instead of xxxxxxx
If Ping("xxxxxxx") Then
    MsgBox "Passed", vbOKOnly
Else
    MsgBox "Failed", vbOKOnly
End If
End Sub
Public Function Ping(ByVal ComputerIP As String) As Boolean
    'You can use also name of computer
    ' Return TRUE, if pin was successful
   Dim oPingResult As Variant
    For Each oPingResult In GetObject("winmgmts://./root/cimv2").ExecQuery _
        ("SELECT * FROM Win32_PingStatus WHERE Address = '" & ComputerName & "'")
        If IsObject(oPingResult) Then
            If oPingResult.StatusCode = 0 Then
                Ping = True
                'Debug.Print "ResponseTime", oPingResult.ResponseTime 'You can also return ping time
               Exit Function
            End If
        End If
    Next
End Function
  1. My other idea was if the computer is connected to the VPN the Ethernet LAN adapter changes to have a connection-specific DNS suffix (however this I think is a virtual adapter so I can't just test the name as it might not be on the same adapter in repeated tests)

Therefore my second idea was to scan through all of the connections retrieving their Connection-specific dns suffix and test for the correct name however I'm not sure how to extract this information.

Upvotes: 2

Views: 2372

Answers (1)

Tom
Tom

Reputation: 9878

Have fixed the code above - Just realised I was calling the wrong variable to ping.

Should be:

Sub test() ' SQL Server address instead of xxxxxxx If Ping("xxxxxxx") Then MsgBox "Passed", vbOKOnly Else MsgBox "Failed", vbOKOnly End If End Sub Public Function Ping(ByVal ComputerIP As String) As Boolean 'You can use also name of computer ' Return TRUE, if pin was successful Dim oPingResult As Variant For Each oPingResult In GetObject("winmgmts://./root/cimv2").ExecQuery _ ("SELECT * FROM Win32_PingStatus WHERE Address = '" & ComputerIP & "'") If IsObject(oPingResult) Then If oPingResult.StatusCode = 0 Then Ping = True 'Debug.Print "ResponseTime", oPingResult.ResponseTime 'You can also return ping time Exit Function End If End If Next End Function

Works now

Upvotes: 1

Related Questions