How to delete linked tables programmatically (1 Viewer)

[genesis]

I have this database and it has local and linked tables. what I want to do is to decipher thru vba what table types are " linked " and by that criteria, loop thru those objects and delete them.

 

[Galaxiom]

The Description property of the table holds the link information.
Parse that and delete as required.

 

[HiTechCoach]

I use the description proprety for other data, so I am not sure how that woudl work.


I would loop through the table collection and test the .Connect property.


Here is some code I used to get the BE path of a liked table:

'*************** Code Start **************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

    Set dbs = CurrentDb()
    On Error Resume Next
    stPath = dbs.TableDefs(strTable).Connect
    If stPath = "" Then
        fGetLinkPath = vbNullString
        'can change this to currentdb.name
    Else
        fGetLinkPath = right(stPath, Len(stPath) _
                        - (InStr(1, stPath, "DATABASE=") + 8))
    End If
    Set dbs = Nothing
End Function

Sub sListPath()
    Dim loTd As TableDef
    CurrentDb.TableDefs.Refresh
    For Each loTd In CurrentDb.TableDefs
        Debug.Print fGetLinkPath(loTd.Name)
    Next loTd
    Set loTd = Nothing
End Sub

'*************** Code End **************

 

[MarkK]

Check out the Connect and Database fields of the hidden MSysObjects system table. Maybe you need to open a recordset "... FROM MSysObjects WHERE Database IS NOT NULL" or something to that effect.
Cheers,

 

[genesis]

thanks every one for your assistance. I have modified your codes and come up with my own and here it is and I want to share this to you:

'-----------------------------------------

Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs

If Left(tdf.Name, 4) <> "MSys" _
And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then

CurrentDb.TableDefs.Delete tdf.Name
CurrentDb.TableDefs.Refresh

End If
Next tdf

'-------------------------------------------------------

I just have this problem, when I click the button to delete those linked tables, it is not directly updates / refreshes the table section in the navigation panel so that only the local tables remain. I have included refresh therein but no effect yet. Any idea?

 

[MarkK]

Sub DeleteLinkedTables()
   Dim rst As DAO.Recordset
   Dim dbs As DAO.Database
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset( _
      "SELECT ForeignName FROM MSysObjects WHERE Type = 6")
   With rst
      Do While Not .EOF
         dbs.TableDefs.Delete !ForeignName
         .MoveNext
      Loop
      .Close
   End With
End Sub

 

[genesis]

ok. I worked it out. ok tnx for assisting

 

[TranslationLost]

In lagbolt's code snippet, "ForeignName" should be replaced with Name.
"Name" contains the name of the table as referenced in the working database. "Foreign Name" is the name of the table linked to in the "other" database.
In my test data base I had a local table called "Animals" and a linked table "Animals1" [linked to "Animals" in a second database]. When I first ran the sub, it deleted the local table not the linked table.

 

[jdraw]

thanks every one for your assistance. I have modified your codes and come up with my own and here it is and I want to share this to you:

'-----------------------------------------

Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs

If Left(tdf.Name, 4) <> "MSys" _
And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then

CurrentDb.TableDefs.Delete tdf.Name
CurrentDb.TableDefs.Refresh

End If
Next tdf

'-------------------------------------------------------

I just have this problem, when I click the button to delete those linked tables, it is not directly updates / refreshes the table section in the navigation panel so that only the local tables remain. I have included refresh therein but no effect yet. Any idea?

To get the navigation pane to reflect the latest status, use the
RefreshDatabaseWindow directive.

The RefreshDatabaseWindow method updates the Database window after a database object has been created, deleted, or renamed.

Info can be found here
http://msdn.microsoft.com/en-us/library/aa221557(office.11).aspx

 

[editolis]

I Use this code:

Function RemoveLinkedTables()

On Error Resume Next

Dim db As Database
Dim tdf As TableDef
Dim intCount As Integer, intPos As Integer

Set db = DBEngine(0)(0)
intCount = db.TableDefs.Count - 1
For intPos = intCount To 0 Step -1
Set tdf = db.TableDefs(intPos)
If Len(tdf.Connect) > 0 Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next intPos

End Function