MS Access – VBA – Delete all Linked Tables

Ever needed to delete the linked tables out of your database. Sadly Access does not allow one to make multiple selection of Access object to perform batch actions on, such as delete. So if you have quite a few tables to delete it can be frustrating and a waste of time. This is why I create the very simply procedure found below.

'---------------------------------------------------------------------------------------
' Procedure : DeleteAttachedTbls
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Deletes all the linked tables from the active database.  It only removes
'             the links and does not actually delete the tables from the back-end
'             database.
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-06-10              Initial Release
' 2         2020-04-09              Code change based on Ben Sacherich's comment
'---------------------------------------------------------------------------------------
Function DeleteAttachedTbls()
On Error GoTo Error_Handler
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    DoCmd.SetWarnings False
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
            'DoCmd.DeleteObject acTable, tdf.Name
            db.TableDefs.Delete tdf.Name 'Per Ben Sacherich's recommendation
        End If
    Next

Error_Handler_Exit:
    DoCmd.SetWarnings True
    Set tdf = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: DeleteAttachedTbls" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

9 responses on “MS Access – VBA – Delete all Linked Tables”

  1. Roger Kerrison

    Thanks very much for this code. There is an obvious error as it it presented on your website: ‘Exit Sub’ should be ‘Exit Function’. With this change, it worked a treat.

    Very annoying the way the link tables now works – I will not use it again as it is too much trouble to change it.

    Thanks again for publishing this. The only change I would make is a message at the end to say the code worked as advertised.

    1. admin Post author

      Thank you for the correction. It was adapted from a sub routine that I quickly converted over to a function for this website to help others, but obviously missed one detail in the conversion. Thank you for pointing it out so I could correct it and avoid others having the issue.

  2. Tom

    Well done. Very useful. You’ve saved me and no doubt many others many hours of work to build something like this. Thanks for taking the time to share.

  3. Bernie Simmonds

    Very useful, saved me heaps of time. I found I had to replace & with just & as the compiler errored in Access 2016. Well done, thanks for sharing

  4. Gina Maylone

    Thank you so much for this! I was so tired of unattaching and reattaching every time I did an update! Happy Holidays to you!

  5. Ben Sacherich

    I’m working offline and have linked tables in a back-end that point to Access tables on the client network. When I try to delete the linked tables using the Access UI it can take over 30 seconds for each table. It’s annoying. I was looking for a routine like yours that would remove all the linked tables programmatically. This is a great find with one caveat; It suffers from serious lag if the linked table is pointing to somewhere that is currently inaccessible.

    What worked for me was to replace the line: DoCmd.DeleteObject acTable, tdf.Name
    with this: db.TableDefs.Delete tdf.Name

    Now it deletes all linked tables super fast.

    Note to author: In your code, please replace “&” with “&” in your error handler.

  6. Tim

    This does not work for me.
    I find I have to run the routine 4 or 5 times or more to delete all the linked tables.
    It seems to exit prematurely without going through all the tables in the collection.

    1. Daniel Pineault Post author

      I suspect because relationships. You may need to identify and delete relationships first before you can delete the table(s). If you search, I have code on the site for that.