How to check if the table already linked ? (1 Viewer)

[dhlao]

I've a form button, when click it'll first check if the target backend tables already linked. If not, it'll link to those tables.
I made a function to do this task

Function ChkLinkTbl(tableName As String) As Boolean
   Dim tdf As DAO.TableDef
   For Each tdf In CurrentDb.TableDefs
      If tdf.Name = tableName Then
         ChkLinkTbl = True
      Else
         ChkLinkTbl = False
         Set tdf = Nothing
         Exit Function
      End If
   Next
   Set tdf = Nothing
End Function

This function works if there's no linked table. But it always return False even linked tables exist.
Then I found that the "tdf.Name" is not returning the real table name. It return something like "~TMPCLP52921". That's why the function never return True.
This make the VBA code try to link the table again. And cause error 3012 - Object 'table-name' already exists

 

[Cronk]

I'd suggest that rather than looping through all tables, just have your code try to open a recordset based on the table you are checking is linked, and catch the error.

I'd also suggest that rather than having this check in a click event on a form requires user intervention, to put the code in a function called by Autoexec. This way the links are checked and fixed if necessary as the database is opened.

 

[dhlao]

Hello cronk
I think I've made a mistake. You're right, I shouldn't loop through all tables. But is there a way to loop through all linked tables only ?

 

[Galaxiom]

There are system tables whose names start with MSys. Tables starting with the tilde are deleted. Deleted tables are purged in a Compact and Repair.

A linked table will have a value in its Connect Property.

 

[dhlao]

Hello Galaxiom, I made some modification to my code

Function ChkLinkTbl(linkTbl As String) As Boolean
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Len(tdf.Connect) > 0 Then
            If linkTbl = tdf.Name Then
                ChkLinkTbl = True
                Set tdf = Nothing
                Exit Function
            End If
        End If
    Next
    ChkLinkTbl = False
End Function

It works, but not efficiency. Because every "linkTbl" need to loop through all the tables in the currentdb to find a match. Is there a faster way ?

 

[Cronk]

If you don't want to take up my suggestion in #2, here's another one.

Dim tdf as tabledef

on error resume next
set tdf = currentdb.tabledefs(linkTbl)
if err <> 0 then msgbox linkTbl.name & "is not linked"

 

[dhlao]

Hi,Cronk. It's better that no need to loop through all tables.
Actually, my main Sub look like this

Private Sub Command1_Click()
   Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset("target_table_tbl")
   Do Until rst.EOF
      Set tdf = CurrentDb.CreateTableDef(rst!table_name)
      If [B]ChkLinkTbl[/B](rst!table_name) = True Then
         CurrentDb.TableDefs(rst!table_name).RefreshLink
      Else
         tdf.Connect = ";DATABASE=" & Me.txtConnection
         tdf.SourceTableName = rst!table_name
         CurrentDb.TableDefs.Append tdf
      End If
      rst.MoveNext
   Loop
End Sub

The table "target_table_tbl" has only 1 field - "table_name" which store the name of those tables that I need to check.
If function ChkLinkTbl return True, then just refresh link. Otherwise, link it.

Because my Access allow user to choose the backend accdb they want. I want them to click the button to trigger this event. Not at the Form_Load.

Would you mind to give me an example code of how to catch the error when checking the target table is linked or not ?

 

[Galaxiom]

One of the system tables also contains the Connect information so you could simply check its values.

Either way it isn't a big load to process.

 

[gemma-the-husky]

a linked table will have a connect string

linkedtable = tabledefs("sometable").connect<>""

for an access table, the connect string includes the path to the linked database, along with some other stuff

note that you will get a trappable run time error if there is no table with name "sometable"

----
because of this, I actually store a local table containing the tables that should be linked for my database - and then I verify that they are all linked when opening the database

 

[Galaxiom]

This query will return the names of linked tables along with their location.

SELECT Name, Database, ForeignName FROM MSysObjects WHERE Database Is Not Null