How do I determine if a specific table is linked on a fe db

[AppStaff]

can anyone provide me code to do a boolean check on whether a specific table is linked in my front end database? I would like to run this check in an autoexec macro but I'm struggling with the code.

 

[KenReay]

Hi

In function below, pass in the Db (ie Set Db = CurrentDb()and the tabldef of the tabel you want to test ie SET tdf = Db.TAbledefs("YourTable&quot.

Function bisTestOneAttachedTable(dbsTmp As Database, tdfTmp As TableDef) As Boolean

'****************************************************************
' Comments : Tests the links of an attached table in the named database
'
'****************************************************************

Dim varTmp As Variant

On Error GoTo err_bisTestOneAttachedTable

varTmp = tdfTmp.Fields(0).NAME

bisTestOneAttachedTable = True

exit_bisTestOneAttachedTable:
Exit Function

err_bisTestOneAttachedTable:
If Err = 3265 Then
bisTestOneAttachedTable = False
End If
Resume exit_bisTestOneAttachedTable

End Function Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[MichaelRed]

Ken Reay's soloution only checks that the table exists in the tdf collection, not that it is a LINKED table. The following snippet checks the table is actually linked.

Of Course (as always), it is lacking the various niceties of error checking, and thus should be carefully reviewed and tested prior to introduction to a production application.

Public Function basCkTblAttd(MyTbl As String) As Boolean

    'Michael Red    10/14/02    Check if Table is LINKED
    'Tek-Tips thread705-379978

    Dim rst As ADODB.Recordset
    Dim Cmd As ADODB.Command
    Dim Cnn As ADODB.Connection

    Dim strSql As String

    strSql = "SELECT MSysObjects.Connect, MSysObjects.ForeignName, MSysObjects.Name "
    strSql = strSql & "FROM MSysObjects "
    strSql = strSql & "WHERE ((Not (MSysObjects.Connect) Is Null) And "
    strSql = strSql & "((MSysObjects.Name) = " & Chr(34) & MyTbl & Chr(34) & "))"
    
    Set Cnn = CurrentProject.Connection    'Normally on initial open of Application
    Set Cmd = New ADODB.Command

    With Cmd
        Set .ActiveConnection = Cnn
        .CommandType = adCmdText
        .CommandText = strSql
    End With

    Set rst = New ADODB.Recordset
    rst.Open Cmd.Execute
    If (Not (rst.BOF = True And rst.EOF = True)) Then
        'NoRecords ==> Not a linked Table in this db
        basCkTblAttd = True
    ' Do whatever you want here .......
    End If

    Set rs = Nothing
    Set Cmd = Nothing
    Set adoCN = Nothing
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[KenReay]

Hi

You are quite right Michael, I (mistakenly) thought AppStaff was trying to detect broken links, not which tables were linked tables.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[AppStaff]

Thank you both for your responses