Check to see if SQL Server Exists

[mikebrewer]

I have an application that I have automatically checking to see if tables are valid and connected, etc. If the app finds that the tables aren't connected or aren't working, it automatically tries to re-connect those tables in the background without the user's knowledge. The time I'm having trouble is if the SQL Server doesn't exists (the back-end tables are on a SQL Server). I would like to check to see if the SQL Server exists before I try to re-connect tables so that no SQL Errors pop up but I'm not sure how to figure that out with VBA. Does anyone have any idea??

 

[Rx_]

It Depends (the standard answer for a contractor type).
You could ping the Server, but a ping could work and the actual SQL Server could be down.
telnet xxxx.xxxx.com 1433 ' 1433 is the default port plus a firewall might block a ping.
http://www.access-programmers.co.uk/forums/showthread.php?t=215649
However, the post here indicates it isn't something to pursue.


Another function is the SQL DMO Application

Public Function TestSQLServer(strSQLServerName As String) As Boolean
Dim oApp As SQLDMO.Application
Dim objNames As NameList
Dim intCount As Integer
    Set oApp = New SQLDMO.Application
    Set objNames = oApp.ListAvailableSQLServers
    TestSQLServer = False
    If objNames.Count > 0 Then
        For intCount = 1 To objNames.Count
            If objNames.Item(intCount) = strSQLServerName Then
                TestSQLServer = True
            End If
        Next
    End If
End Function

You will have to download Microsoft SQLDMO and then set a Tools- Reference to SQLDMO. PROBLEM - Microsoft announced SQL 2008 is the last version that SQLSMO will work on. I wouldn't sugges using this either.

After reading about 12 internet post on this, this is the only way to go.
Create a typical Relink for one single table and count the records.
But, add the Timeout to the connection string.That is the approach I would go with.

Here is one of the better solutions you might be interested in.
http://www.access-programmers.co.uk/forums/showthread.php?t=217499

 

[mikebrewer]

The issue I'm having is that I don't want the users to see the pop up box about SQL Server not existing or access being denied. And if they travel out of signal area (they are connecting back through 4g tablets) and the program loses connection, it will try to reconnect, timeout and pop up the error. I figured I could get it to check to see if the server was there and available first and if so, reconnect the tables. So I have moved on to trying this:

Dim cnn As New ADODB.Connection
On Error GoTo ErrConn

Set cnn = New ADODB.Connection
cnn.ConnectionString = "Server=server;Database=db;UID=sa;Pwd=password"
cnn.Open

If cnn.State = adStateOpen Then
MsgBox ("YES")
Else
MsgBox ("NO!")
End If


Exit Sub

ErrConn:
Select Case Err.Number
Case -2147217843
MsgBox "Connection failed due to unknown user name 'HI'"
Case -2147467259
MsgBox "Sql Server does not exist or access denied"
Case Else
MsgBox Err.Number & " " & Err.Description
End Select


However... i still get that is does not exists or access is denied so I know something is wrong with my connection string as I know the server is up and running and usernames and passwords are correct.

 

[mikebrewer]

Figured it out... i was forgetting the driver. looks like I can establish the connection that way and check to see if it will connect. if it connects, I can connect tables.

thanks!