Chapter 16: Working with External Data
607
Function TestLink(sTablename As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iStartODBC As Integer
Dim iEndODBC As Integer
Dim sDataSrc As String
Dim iODBCLen As Integer
Dim sMessage As String
Dim iReturn As Integer
On Error GoTo HandleError
Set db = CurrentDb
‘Open a recordset to force an error:
Set rs = db.OpenRecordset(sTablename)
‘If the link is valid, exit the function:
TestLink = True
ExitHere:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Function
HandleError:
‘If the link is bad, determine what the problem
‘is, let the user know, and exit the function:
Select Case Err
Case 3078 ‘Table doesn’t exist:
sMessage = “Table ‘“ & sTablename
& “‘ does not exist in this database”
Case 3151 ‘Bad link
‘Extract the name of the ODBC DSN
‘to use in your custom error message:
iStartODBC = InStr(Error, “to ‘“) + 4
iEndODBC = InStr(Error, “‘ failed”)
iODBCLen = iEndODBC - iStartODBC
sDataSrc = Mid$(Error, iStartODBC, iODBCLen)
sMessage = “Table ‘“ & sTablename
& “‘ is linked to ODBC datasource ‘“
& sDataSrc
& “‘, which is not available at this time.”
Case Else
sMessage = Err.Description
End Select
iReturn = MsgBox(sMessage, vbOKOnly)
‘Return failure:
TestLink = False
Resume ExitHere
End Function