UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> DeleteLinkedTables    

DeleteLinkedTables

Sometimes, we want to delete/remove ALL the linked tabled tables in our database at once, without the need to click and remove them one at a time. Here is a bit of code that will do just that in short fashion.

Just save the following code into a module and call with: DeleteTableLinks

This will remove ALL linked tables and can be tweaked to be more selective.

CODE
' DeleteLinkedTables
' http://www.utteraccess.com/wiki/index.php/DeleteLinkedTables
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                  This code will remove ALL linked tables from the current db
' 1.0  2013-01-24                    
'

Public Function DeleteTableLinks() As Boolean
Dim dbCurrent   As Object     ' current database
Dim tblLinked   As Object     ' current table in collection
Dim strTblName  As String
         
10        On Error GoTo DeleteTableLinks_Err
20        DoCmd.Hourglass True
         ' loop through the tables
30        Set dbCurrent = CurrentDb()
40        For Each tblLinked In dbCurrent.TableDefs
             ' if it is a linked table, then delete
50             If tblLinked.Connect <> "" Then
60                strTblName = tblLinked.Name
70                DoCmd.DeleteObject acTable, strTblName
              End If
         Next
         
80        DeleteTableLinks = True
90        DoCmd.Hourglass False

100     MsgBox "All linked tables removed."
         
DeleteTableLinks_Exit:
   DoCmd.Hourglass False
   Exit Function
         
DeleteTableLinks_Err:
   MsgBox "Error: " & Err.Number & " - " & Err.Description & vbCrLf & vbCrLf & "in DeleteTableLinks procedure on line " & Erl & " of 'DeleteTableLinks'.", vbInformation + vbOKOnly, "Error !"
   DeleteTableLinks = False
   DoCmd.Hourglass False
   Resume DeleteTableLinks_Exit

End Function


Creative Commons License
DeleteLinkedTables by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 5,030 times.  This page was last modified 15:49, 24 January 2013 by Mark Davis.   Disclaimers