check connection to linked table (Access 2003)


I have an access 2003 database.
I have several linked tables to linking to tables in other databases.
These are in different locations on our network.

Occasionally I have had problems.
Sometimes relating to Network, and sometimes when the other databases have been inadvertently moved.

What I'd like to do Is just before I do something critical is to check that everything is still Ok.
I've searched the knowledge base and there was a good solution whereby I check the tabledefs.
So I thought I's check 1 table for each of my external databases a(as follows)

If Nz(CurrentDb.TableDefs("tbl_FSL_Archive").Connect, "") <> "" Or _
   Nz(CurrentDb.TableDefs("Version1").Connect, "") <> "" Then
 MsgBox "not linked table"
End If

However, this doesn't seem to work. I have moved one of the DB's away and it still says its linked OK.

Does anyone have any other ideas.
Ideally I'd like to capture both the network issue, and the missing DB.
Eric HarrisDeveloperAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your code is just checking if the table is linked or not.  It's not actually doing something with it.

You need to open the table (a recordset) to actually test if the link is good.  Take a look at the attached code for ideas.  This is code I use at startup to check a table in each of the BE (I assume if one is good, they are all good).

Function IsJETTableAttached(strDatabaseName As String, strTableName As String) As Integer

        Dim curDB As DAO.Database
        Dim curTableDef As DAO.TableDef
        Dim rstTest As DAO.Recordset
        Dim intRet As Integer

        '  Open attached table to see if connection information is correct.
        '  Execute refresh if attachments are broken otherwise exit.

10      IsJETTableAttached = False

20      On Error Resume Next

30      Err = 0

40      Set curDB = DBEngine.Workspaces(0).Databases(0)
50      Set rstTest = curDB.OpenRecordset(strTableName)
60      If Err = 0 Then
70        intRet = True
80      Else
90        intRet = RefreshJETTables(strDatabaseName)
100     End If

110     rstTest.Close
120     Set rstTest = Nothing

130     Set curDB = Nothing

140     If intRet = False Then
150       IsJETTableAttached = False
160       Call ApplicationExit
170     End If

End Function

Open in new window

Eric HarrisDeveloperAuthor Commented:
Thanks Jim,

I'll give that a try.
For the Database name is it just the name or the full path of the DB
Eric HarrisDeveloperAuthor Commented:
Forget that Jim.
As I'm not doing the refresh. I'm just checking the link itself
works an absolute treat
Eric HarrisDeveloperAuthor Commented:
Wicked response and brilliant solution
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.