Link to home
Start Free TrialLog in
Avatar of steve lemmon
steve lemmon

asked on

Check if MS Azure is Running

I'm using MS Access on the front end for a point-of-sale and Azure SQL Server tables on the back end.  Yesterday South Central US Azure went down which locked up (disabled) my front end.  

Does anybody have VBA code that would check if Azure is responding?  Perhaps I could run a simple query to Azure (almost like a ping) and if there is no response then I could bypass the rest of my queries.  I'm not a programmer so please provide the explicit code.

Thanks!
Avatar of lcohan
lcohan
Flag of Canada image

I'm pretty sure you can monitor the Azure status here https://azure.microsoft.com/en-us/status/ however it does not look like the issue was resolved yet.
And even the status site were on and off.

The simple method is to open a table linked to the server.
  • If it opens => OK
  • If it times out with an error => No connection

Here is the full function we use. A pass-through query is used to check the connection:

Public Function IsSqlServer( _
    ByVal TestNewConnection As Boolean, _
    Optional ByVal Hostname As String, _
    Optional ByVal Database As String, _
    Optional ByVal Username As String, _
    Optional ByVal Password As String, _
    Optional ByRef ErrNumber As Long) _
    As Boolean

    Static CurrentConnectionString  As String
    
    Const VerificationQuery         As String = "VerifyConnection"
    
    Dim db                  As DAO.Database
    Dim qd                  As DAO.QueryDef
    Dim rs                  As DAO.Recordset
    
    Dim IsConnected         As Boolean
    Dim NewConnectionString As String
    Dim OldConnectionString As String
    Dim DoCheck             As Boolean
    
    Set db = CurrentDb
    Set qd = db.QueryDefs(VerificationQuery)

    If Hostname & Database & Username & Password = "" Then
        If TestNewConnection = False Then
            ' Verify current connection.
            DoCheck = True
        Else
            ' Don't check.
            ' A new connection cannot be checked with empty parameters.
        End If
    Else
        OldConnectionString = qd.Connect
        NewConnectionString = ConnectionString(Hostname, Database, Username, Password)
        If NewConnectionString <> OldConnectionString Then
            If TestNewConnection = False Then
                ' Fail. No check needed.
                ' Tables are currently connected to another database.
            ElseIf CurrentConnectionString <> "" Then
                ' A successful connection for this session is active.
                ' Check if only username and/or password has been changed.
                If Split(CurrentConnectionString, "UID=")(0) = Split(NewConnectionString, "UID=")(0) Then
                    ' Hostname and database have not changed.
                    If Split(CurrentConnectionString, "UID=")(1) = Split(NewConnectionString, "UID=")(1) Then
                        ' Username and password have not changed.
                        DoCheck = True
                    Else
                        ' The connection is open with other credentials, thus
                        ' these new credentials could never succeed.
                        ' No check.
                    End If
                Else
                    ' Hostname and database have changed.
                    ' Check a new connection.
                    qd.Connect = NewConnectionString
                    DoCheck = True
                End If
            Else
                ' Check a new connection.
                qd.Connect = NewConnectionString
                DoCheck = True
            End If
        Else
            ' Check the current connection.
            OldConnectionString = ""
            DoCheck = True
        End If
    End If
    
    On Error GoTo Err_IsSqlServer
  
    ' Perform check of a new connection or verify the current connection.
    If DoCheck = True Then
        Set rs = qd.OpenRecordset()
        ' Tried to connect ...
        If ErrNumber = 0 Then
            If Not (rs.EOF Or rs.BOF) Then
                ' Success.
                IsConnected = True
                ' At this point the connection is verified.
                ' Persist the successful connection string.
                CurrentConnectionString = NewConnectionString
            End If
            rs.Close
        End If
        
        If OldConnectionString <> "" Then
            ' Restore old connection parameters.
            qd.Connect = OldConnectionString
        End If
    End If
    
    Set rs = Nothing
    Set qd = Nothing
    Set db = Nothing
    
    IsSqlServer = IsConnected
    
Exit_IsSqlServer:
    Exit Function
    
Err_IsSqlServer:
    ' Return error.
    ErrNumber = Err.Number
    ErrorMox "Connection to database"
    ' Resume to be able to restore qd.Connect to OldConnectionString.
    Resume Next
    
End Function

Open in new window

Have you considered making the solution redundant so that this is not an issue?
Avatar of steve lemmon
steve lemmon

ASKER

I'm hoping Gustav Brock's code is the answer but it's above my head.  I gave it to a programmer friend of mine to test it but he hasn't had time.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.