Go to Top

Part 3: Destroy your linked tables on exit

Author note: This is part 3 of a series on DSN-less tables in Access.

You can review part one here. Part 2 here. Part 4 here.

MS Access Report Generator

In part three of this series I’m going to give you the code to delete all of your linked tables once the app closes. Deleting your links makes your app secure since both the user’s name and password are stored with the linked tables. It may not be much of an issue on a local area network, but if your app is using SQL Server in the cloud it can be a security issue.

Where to place your code?

At our shop we have a main form that launches on every app, so we place the following code on the form’s close event:


Notice the use of the label “StartAgain” when you delete a table it changes the tabledef collection and you need to start over in order to refresh it, otherwise the code will error out.

Next Week: Changing from Production to Beta database by just changing the Access file name.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

9 Responses to "Part 3: Destroy your linked tables on exit"

  • Zelda
    June 25, 2018 - 6:44 pm Reply

    Hi Juan

    I have a code that will quit the application with a set expiry date. I want to delete one of my linked tables when this condition is met. Can you help me with a script to insert into this code please
    Public Function Expired()

    If (Date > #6/26/2018#) Then
    msg = “GOGOSHEQ License has expired. Please contact your office manager”
    Style = vbOKCancel + vbCritical
    Title = “Database Expired”
    myErr = MsgBox(msg, Style, Title)
    If myEr = vbOK Then
    Exit Function
    End If
    If myErr = vbCancel Then
    Dim strpassword As String
    Dim Counter As Integer
    Counter = 0
    Do Until Counter = 1
    strpassword = InputBox(“Please enter password”, “Password Required”)
    If strpassword = “13MErcury31$” Then
    ‘open login screen
    Exit Function
    Counter = Counter + 1
    remaining = 1 = Counter
    MsgBox “Wrong password!” + vbCrLf & _
    “You have ” & remaining & ” attempt(s) left”, _
    vbOKOnly, “Password Info”
    End If
    Exit Function
    End If
    End If
    End Function

  • Nathan Beckstrand
    June 6, 2016 - 6:37 am Reply

    Hi, I was recently considering this technique but I was thinking like a devious user. I copied the accdb file while the application was running. I then closed the application. The original accdb had all of the linked tables removed. The copy, of course, did not.

  • Dick weber
    March 2, 2013 - 5:30 pm Reply


    I am using your technique from this series in my application and it works very well. Thanks for sharing.

    However, I have run into an apparent race condition on closing the database. When a user attempts to close the database by using the “x” for the application while one of the most used forms is open, an error occurs due to an undefined query which is based on one of the deleted tables. If I close that form first and then close the application, the error doesn’t occur.

    I have been searching for an event which I can intercept to make sure all other forms are closed before the hidden form is closed. Do you know of such an event?

    I would like to maintain the functionality of the traditional “x” for the application since all users are so used to using it.

    Again, thanks for your very helpful blog.


    • Juan Soto
      March 2, 2013 - 8:09 pm Reply


      Glad to hear from you! How is your practice doing?

      Place the code on the on close event of the first form that opens in your app. In our case we close the app if users close the form.

      Let me know if that works.

      • Dick Weber
        March 5, 2013 - 10:07 am Reply


        The code is in the startup form which is hidden.
        After some testing, I discovered that the startup form was the first one to close leaving an open form which referenced A table which no longer existed.

        I added code to close all other forms first before deleting the tables; that solved the problem.

        The practice is very busy which is good.

        Thanks for your help.


  • Malcolm
    July 17, 2012 - 6:49 pm Reply

    Hi Juan,

    For Each iterates through a collection in an unordered sequence and by deleting an object within that collection during iteration is the reason you encounter an error.

    I would suggest that you iterate through the collection in an ordered sequence in reverse order to delete the TableDef objects using:

    For index = dbs.TableDefs.Count – 1 To 0 Step -1
    Set tdf = dbs.TableDefs(index)
    If Left(tdf.Connect, 5) = “ODBC;” Then
    DoCmd.DeleteObject acTable, tdf.Name
    End If
    Next index

    Best regards,

    • Juan Soto
      July 17, 2012 - 6:55 pm Reply

      Great suggestion Malcolm! Thanks!


  • Nicolas
    April 23, 2012 - 1:42 pm Reply

    Thanks for sharing these useful ideas!
    I was just wondering, instead of using a label how about adding this line in the loop:

    Thanks and Regards

    • Juan Soto
      April 23, 2012 - 8:12 pm Reply

      Hi Nicolas,

      I have not tried it that way, so I can’t say it will work. Will do some testing and let you know.


Leave a Reply

Your email address will not be published. Required fields are marked *


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.