Check if ODBC linked table is "reachable" (1 Viewer)

[Tango]

I need to check (through VBA) if a linked table is online or not. The linked table is attached to a SQL server using an ODBC connection string. If the server is offline or unreachable is what I am using to trigger other code.


Anyone have ideas? I have been googling all morning with little luck.

I did find http://www.vbaexpress.com/forum/showthread.php?t=17777 which looks like what I need but I cant get it to work. I just get a
"Run time error ODBC driver manager data source name not found and no default driver specified"

Private Sub Form_Load()
 
Dim curtbl As String
Dim con
curtbl = CurrentDb.TableDefs("help table").Connect
Set con = CreateObject("ADODB.Connection")
con.Open curtbl
con.Close
Set con = Nothing
  
con_Exit:
    Exit Sub
    
con_error:
    MsgBox con
    'Run linkDB()
    Resume
End Sub

 

[Mr. B]

Check this link. It should help:
http://www.fmsinc.com/free/newtips/access/accesstip3.asp

 

[Tango]

I found that one too and frankly it confused the hell out of me.

 

[Rx_]

Saw that you are connecting to SQL Server.
http://www.access-programmers.co.uk/forums/showthread.php?t=228143&highlight=dsnless
http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=dsnless
Sparkbyte added to this too.

My example is not the best code, but should get you started.
Basically, a local table list all of the tables to be linked. I use a couple of columns with checkboxes that can determine versions of linking (e.g. Developer, User, userLite...)
In my case, over 100 tables are linked.

Delete all connections, then re-connect.
Build a connection string then apply it in a function.
While mine are connecting, a pass/faill is indicated.

Granted, I am not a big fan of ADO. I use the SQL Server Native Client 10.0 The data types conversions have treated me well. The SQL Server Native Client is something to download from MS. DAO works really well. I am old and have been using DAO since the first version. you should let me know about your ADO experience.

Link to link for Native Client
http://www.access-programmers.co.uk/forums/showthread.php?t=224187&highlight=dsnless

 

[Tango]

Both of those appear to go WAY beyond what I need to do. I don't need to re-link them or do anything to them. I just need to find out of the linked table is online. I have no preference between DAO or ADO.

Essentially this is a check that runs when the front end is opened to see if the SQL server is where it is supposed to be.

 

[Rx_]

Leave it to me to make something simple too complex. I am really good at it too!

So maybe you put your code in a Function and call the function from the same place.

Have the function return a boolean (i.e. pass fail)
Instead of a message box in the error, set the function return value to false. Actually, I set these kinds of functions to False at the beginning. Run your query in the function, if it returns a value then set the function to True. Otherwise, it is already at false. (assume false until proven it connects).
If you need help with that function, just let us know.

I use functions all the time. If during the form open, it returns a false, - go to some other path to let the user know. If it passes, go on with the program.

Just to add unneeded complexity... LOL
This is an excellent time to create a local Log table and only record the connection faiures. Just the standard time stamp, error message, error number...
this way you might get some clues of why it failed.

 

[Rx_]

See if this might work

Create a TLogUsage table with autocounter and the field names listed below (UseDate, StrFormName, CallingProc, UserName, ..)
If your opening form has a connection - it will populate your table with who and where.

Call this with
dim PassFailOpen as boolean
PassFailOpen = LogUsage("Splash_Screen", "Form_Open")
If it passes, you have a nice log table of all the users, time stamp and form opened.
If it fails, you get a False returned

in the form open use

if LogUsage("Splash_Screen", "Form_Open") then
' its all good and recorded
else
' message box to user then close down gracefully
' you could modify the code below to write error to a local table
end if

Function LogUsage(ByVal strFormName As String, _
    strCallingProc As String, Optional ControlName) As Boolean
10       On Error GoTo Err_LogUsage
          Dim rst As DAO.Recordset  ' The tLogUsage table
          LogUsage = False  ' false until we prove it passed
20            Set rst = CurrentDb.OpenRecordset("tLogUsage", 2, dbAppendOnly + dbSeeChanges)
30            rst.AddNew
40                rst![UseDate] = Now()
50                rst![strFormName] = strFormName
60                rst![CallingProc] = strCallingProc
70                rst![UserName] = Environ("username")
80                If Not IsMissing(ControlName) Then
90                    rst![ControlName] = Left(ControlName, 75)
100               End If
110           rst.Update
120           rst.Close
130           LogUsage = True ' hey we connected and logged event
Exit_LogUsage_GIS:
140       Set rst = Nothing
150       Exit Function
Err_LogUsage:
          ' just resume next and the usage is not logged
  'MsgBox Err.Number & Err.Description ' OPTIONAL
160       Err.Clear
170       Resume Exit_LogUsage_GIS
End Function

 

[Tango]

Jackpot, I cracked it and without adding or editing tables!

curtbl = CurrentDb.TableDefs("help table").Connect
 
  On Error GoTo con_error
 
  Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   'Set the provider property to the OLE DB Provider for ODBC.
   cnn.Provider = "MSDASQL"
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      curtbl
   cnn.Open
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "Server online!"
   Else
      MsgBox "Sorry. The server is not responding."
   End If
   ' Close the connection.
   cnn.Close
 
con_Exit:
    Exit Sub
 
con_error:
  Dim resp
  resp = MsgBox("Server Connection Failed!" & vbCrLf & vbCrLf & "Would you like to change the name of your base server?", vbYesNo)
If resp = vbYes Then
    Run linkDB()
    Else
    Application.Quit
End If

 

[Rx_]

way to go! Hopefully, you are expanding your programming horizons!

 

[GanzPopp]

If you want to keep it simple, just try to open the table recordset:

'Check if an ODBC connected table is reachable
Function IsODBCConnected(TableName As String) As Boolean
    If Not TableExists(TableName, True) Then Exit Function
    
    Dim rst As DAO.Recordset
    
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset(TableName)
    IsODBCConnected = (Err.Number <> 3151)
End Function

Maybe not the most likeable and complete solution, but works for simple situations.