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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.