You could simply “try” and open the table(s) in question. However, if your connection is gone, then you find some VERY nasty effects. First you get a huge delay (bad). And if you trigger an ODBC error, in most cases you have to exit the application.
To avoid the above?
It turns out there is a different pathway to get access to check if you have use of the server. It not only eliminates the dreaded and all evil ODBC error, but ALSO errors out MUCH faster to boot! You find the “error out” is VERY fast!
The way this works is you use a queryDef to check if you have a connection. This causes access to use a “different” path and test then opening a reocrdset.
So this approach avoids the long delay, and also ODBC errors.
You can use this routine for the test:
Function TestLogon(strCon As String) As Boolean
On Error GoTo TestError
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strCon
qdf.ReturnsRecords = False
'Any VALID SQL statement that runs on server will work below.
qdf.SQL = "SELECT 1"
qdf.Execute
TestLogon = True
Exit Function
TestError:
TestLogon = False
Exit Function
End Function
Now, say in our code, we had a linked table. So, we can use/grab the connection of that linked table, and test like this:
Sub MyCoolUpdate()
Dim strCon As String
Dim rstHotels As DAO.Recordset
strCon = CurrentDb.TableDefs("dbo_tblHotels").Connect
If TestLogon(strCon) = True Then
' it is safe to open the linked table,
'eg:
Set rstHotels = CurrentDb.OpenRecordset("dbo_tblHotels", dbOpenDynaset, dbSeeChanges)
' we are conneced - do your stuff
' walk the dog - do updates
rstHotels.Close
End If
End Sub
So I much suggest you don't try and touch a linked table to the server until such time you test using the above trick.
