How to test SQL Server connection and prevent Server failure message in MS Access FE? (1 Viewer)

[SORM2]

Hi Everyone,

I have a database application with MS Access FE and SQL Server BE through the linked tables. When I forget connecting internet and then open my Access then I wait long and took SQL Server connection failure. I would like to create my own messagebox instead of SQL Server connection failure message, when I open the Access and then get my Access closed itself automatically. I thought I may need to write the code in the opening event of my beginning form. But I could not set.

Could you help me about creating the proper code and event? Thanks.

 

[Minty]

You can check before trying to connect to any tables by using the following code., simply pass in the name of one of your linked tables;

Function IsODBCConnected(TableName As String) As Boolean
    
    If Not TableExists(TableName) Then Exit Function
    
    Dim rst As DAO.Recordset
    
    On Error Resume Next
    'Debug.Print Err.Number
    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
    IsODBCConnected = (Err.Number <> 3151)
    
End Function

 

[SORM2]

You can check before trying to connect to any tables by using the following code., simply pass in the name of one of your linked tables;

Function IsODBCConnected(TableName As String) As Boolean
  
    If Not TableExists(TableName) Then Exit Function
  
    Dim rst As DAO.Recordset
  
    On Error Resume Next
    'Debug.Print Err.Number
    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
    IsODBCConnected = (Err.Number <> 3151)
  
End Function

Hello Minty,

How should I apply this code? Should I write it in a module? Then where should I call the function "IsODBCConnected" ?

 

[isladogs]

For info, I use the following code at startup for a similar purpose:

Public Function CheckSQLServerConnection() As Boolean

'returns true if SQL Server is running and the listed database is available
'Otherwise false

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection

CheckSQLServerConnection = False

    Set cnn = New ADODB.Connection
   'modify the next line as appropriate
    cnn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;User ID=sa;Password=PASSWORD"
 
    If cnn.State = adStateOpen Then
        CheckSQLServerConnection = True
        cnn.Close
    End If

    'Debug.Print CheckSQLServerConnection
   
Exit_Handler:
    Exit Function
   
Err_Handler:
    'CheckSQLServerConnection = False
     MsgBox "Error " & Err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
            "  " & Err.Description & "   ", vbCritical, "SQL Server error"
   
    Resume Exit_Handler
   
End Function

Or to test if a specific SQL table link is OK, I use:

Public Function CheckTableLinks() As Boolean
' Check links to the datafile ; returns True if links are OK.
    Set db = CurrentDb

' Open linked table "YourTableName" to see if connection information is correct.

    On Error Resume Next
    Set MySet = db.OpenRecordset("YourTableName", dbOpenSnapshot)
    MySet.Close
    ' If there's no error, return True.
    If Err = 0 Then
        CheckTableLinks = True
    Else
        CheckTableLinks = False
    End If
    
End Function

Either of these should be placed in a standard module (as should Minty's code).
Use whichever you prefer

 

[SORM2]

For info, I use the following code at startup for a similar purpose:

Public Function CheckSQLServerConnection() As Boolean

'returns true if SQL Server is running and the listed database is available
'Otherwise false

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection

CheckSQLServerConnection = False

    Set cnn = New ADODB.Connection
   'modify the next line as appropriate
    cnn.Open "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;User ID=sa;Password=PASSWORD"

    If cnn.State = adStateOpen Then
        CheckSQLServerConnection = True
        cnn.Close
    End If

    'Debug.Print CheckSQLServerConnection
 
Exit_Handler:
    Exit Function
 
Err_Handler:
    'CheckSQLServerConnection = False
     MsgBox "Error " & Err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
            "  " & Err.Description & "   ", vbCritical, "SQL Server error"
 
    Resume Exit_Handler
 
End Function

Or to test if a specific SQL table link is OK, I use:

Public Function CheckTableLinks() As Boolean
' Check links to the datafile ; returns True if links are OK.
    Set db = CurrentDb

' Open linked table "YourTableName" to see if connection information is correct.

    On Error Resume Next
    Set MySet = db.OpenRecordset("YourTableName", dbOpenSnapshot)
    MySet.Close
    ' If there's no error, return True.
    If Err = 0 Then
        CheckTableLinks = True
    Else
        CheckTableLinks = False
    End If
  
End Function

Either of these should be placed in a standard module (as should Minty's code).
Use whichever you prefer

Hi isladogs,
Thank you very much. However I still could not get this function worked. I wrote your second code as well as Minty's code in a module and then call this function in the opening event of my beginning form. Nothing changed. that code did not affect anything. I will be glad if you can give me a bit more clarification.

 

[Minty]

Please post up the code you are using in your form opening.
What does "nothing has changed" mean?

 

[SORM2]

Please post up the code you are using in your form opening.
What does "nothing has changed" mean?

it is the same as you sent me. I wrote the code you send, exactly like below to a module. One of my tablename = tblcustomer

Function IsODBCConnected(tblcustomer As String) As Boolean
If Not TableExists(tblcustomer) Then Exit Function
Dim rst As DAO.Recordset
On Error Resume Next
Set rst = CurrentDb.OpenRecordset(tblcustomer, dbOpenSnapshot)
IsODBCConnected = (Err.Number <> 3151)
End Function



After I wrote it to module and call in the beginning form, I disconnect the internet and opened the application. It is still giving standard SQL Server connection failure message as before. That is what I meant. I wanted to create my customized message to user when there is no internet connection. and close the app.

 

[Minty]

So you have changed the function a bit and broken it!

You should call it as originally written by passing a table name into it.

If IsODBCConnected("tblCustomer") Then ....

REPEAT : Please post up the code you are using in your form opening.

 

[isladogs]

OK I'm slightly confused as to what you really want.
Your post #5 suggested you used my CheckSQLServerConnection function in your startup form.
Can you confirm before I respond further.

Also if you want to check whether the user is connected to the Internet, I have separate code for that purpose.
Let me know if that's what you actually want

 

[SORM2]

So you have changed the function a bit and broken it!

You should call it as originally written by passing a table name into it.

If IsODBCConnected("tblCustomer") Then ....

REPEAT : Please post up the code you are using in your form opening.

Hello Minty, I posted my code in my 7. message already. Sorry if I dont understand.
In the open form I called the function of that code like below, I wrote in 7. message.

Private Sub Form_Open(Cancel As Integer)
Call IsODBCConnected
End Sub

 

[SORM2]

OK I'm slightly confused as to what you really want.
Your post #5 suggested you used my CheckSQLServerConnection function in your startup form.
Can you confirm before I respond further.

Also if you want to check whether the user is connected to the Internet, I have separate code for that purpose.
Let me know if that's what you actually want

I used the function you wrote which is "CheckTableLinks" in a module and then put it to opening event of my form.
What I want is simply to give a my own pop up message to users in case no connection.

 

[isladogs]

Each of my functions from post #5 give a boolean output ...as does Minty's from post #2
In each case, if the function output is true, all is well so proceed as normal
If false the connection is broken, so you need to alert the user and, if appropriate, close the app.
For example:

 'code to prevent issues relinking SQL tables if SQL server isn't running
        If CheckSQLServerConnection = False Then
             MsgBox "SQL Server is NOT available" & vbCrLf & _
            "Unable to relink tables" & vbCrLf & _
            "This application will now close", vbInformation, "SQL server not available"
            Application.Quit
        End If

If you just want to check the Internet connection, that can also be done with different code

 

[Jbooker]

If the main form that calls this also has databound controls that query SQL Server then you're not going to prevent the SQL delay. You need to call this Form_Load and cancel the form load or call it from a non-databound form such as a menu.

 

[Minty]

In all instances where we use this, the initial loading form is only bound to a local table.
This allows local user validation (if required), then a check of Interent availability (if we are using Azure), then finally BE availability.

Once the external BE data source is established, the local lookup tables are updated from the server versions, then finally other linked tables are connected.

You could easily do the user check last if you don't have a login screen or just stop things dead in their tracks if the BE isn't available, but we have some apps that allow an external IP address update for the Azure firewall to be processed through an external API for home and mobile users, so we need to validate the user before that can happen. It's all a bit chicken and egg...

 

[isladogs]

If the main form that calls this also has databound controls that query SQL Server then you're not going to prevent the SQL delay. You need to call this Form_Load and cancel the form load or call it from a non-databound form such as a menu.

The Form_Load event cannot be cancelled. Perhaps you meant Form_Open which you can cancel

 

[Jbooker]

The Form_Load event cannot be cancelled. Perhaps you meant Form_Open which you can cancel

Correct. So like:

Private Sub Form_Open(Cancel As Integer)
        If IsODBCConnected("tblCustomer") = False Then
             MsgBox "SQL Server is NOT available" & vbCrLf & _
            "Unable to relink tables" & vbCrLf & _
            "Cancelling Form Open", vbInformation, "SQL server not available"
            Cancel = True
        End If
End Sub

or

Private Sub Form_Open(Cancel As Integer)
        If CheckSQLServerConnection = False Then
             MsgBox "SQL Server is NOT available" & vbCrLf & _
            "Unable to relink tables" & vbCrLf & _
            "Cancelling Form Open", vbInformation, "SQL server not available"
            Cancel = True
        End If
End Sub

 

[isladogs]

Yes you could do that. However, I usually prefer to run such checks at startup using an autoexec macro.
Either way, checking whether SQL Server is connected is instantaneous when a connection exists. However, there is a delay of 10 seconds or more if no connection is detected