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!
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!
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.
Here is the full function we use. A pass-through query is used to check the connection:
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
Have you considered making the solution redundant so that this is not an issue?
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 TRIALMembers 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.