delete all linked tables

[Jacob Mathai]

Is there code available to delete all linked tables when I close the Main form, by looping thru the list of tables? (I could use docmd.deleteobject command.)
I only want to delete 'linked' tables programatically.

 

[LaBelette]

This code will delete all the tables, not only the linked ones.
The only thing you need to do is add a line to verify if it is a linked table, but I don't know how.
One thing I can think of is adding a prefix (like "link") at the beginning of you linked tables name and checking if left(tblTable.name,4) = "link".

Public Sub DeleteLinkedTables()
    ' Loop through all tables
    Dim tblTable As AccessObject
    For Each tblTable In Application.CurrentData.AllTables
        ' Verify if it is a linked table
        ' ***I don't know how !!!***        

        ' Verify is the table is loaded
        If Not tblTable.IsLoaded Then
            ' Delete the table
            DoCmd.DeleteObject acTable, tblTable.Name
        End If
        
    Next
End Sub

 

[Newman]

Haven't tested it but maybe this could help complete LaBelette's code:

Set DB = DBEngine.Workspaces(0).Databases(0)
While i <= DB.TableDefs.Count - 1
If Left(Trim(DB.TableDefs(i).SourceTableName), Len([I]CurrentDatabase[/I])) <> [I]CurrentDatabase[/I] then

CurrentDatabase should be replaced by the name of the current database
I know that it worked for me for linked Oracle tables, but I am not sure it will works as well with Access or other tables.

 

[mresann]

Using DAO database object, the CONNECT method determines if the table is local or linked. If it is linked, CONNECT string is actually a filepath. If the table is local, there is no string.

Sub subRemoveLinks()

PROC_DECLARATIONS:
   Dim db           As DAO.Database
   Dim tdfTableDef  As DAO.TableDef
   Dim lngCounter   As Long
   Dim lngPosition  As Long
   
PROC_START:
   On Error GoTo PROC_ERROR
   Set db = CurrentDb
   lngCounter = db.TableDefs.Count - 1
   
PROC_MAIN:
   For lngPosition = lngCounter To 0 Step -1
      Set tdfTableDef = db.TableDefs(lngPosition)
      If Len(tdfTableDef.Connect) > 0 Then
         Debug.Print tdfTableDef.Name
         DoCmd.DeleteObject acTable, tdfTableDef.Name
         Application.RefreshDatabaseWindow
      End If
   Next
   
PROC_EXIT:
   Exit Sub

PROC_ERROR:
   MsgBox "Error " & Err.Number & " (" & _
           Err.Description & ")" & vbCrLf & vbCrLf & _
           "Procedure: subRemoveLinks" & vbCrLf & _
           "Module: Module2"
   GoTo PROC_EXIT

End Sub

 

[ghudson]

This SQL will find all linked tables...

SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=6))
ORDER BY MSysObjects.Name;

 

[Jacob Mathai]

Thanks for your response. I will try them soon.

 

[Pat Hartman]

ghudson,
There are TWO codes for linked tables:

SELECT [MSysObjects].[Name]
FROM MSysObjects
WHERE Left([Name],1) <> "~" AND (MSysObjects.Type = 6 OR MSysObjects.Type = 4)
ORDER BY MSysObjects.Name;

Type 4 = ODBC linked tables
Type 6 = Access linked tables

 

[Jacob Mathai]

Thanks to everyone who helped. It worked.

 

[ghudson]

Jacob,

I am curious... Can you post what the code you got to work? Thanks!

Thanks for the update Pat!

 

[Jacob Mathai]

DoCmd.RunSQL " SELECT [MSysObjects].[Name] into temp1 " _
& " FROM MSysObjects " _
& " WHERE Left([Name], 1) <> '~' And (MSysObjects.Type = 6 Or MSysObjects.Type = 4) " _
& " ORDER BY MSysObjects.Name"


This is the code from Pat. I put the data into a table temp1.