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.

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:

Private Sub Form_Close()
   'delete any tables where connection property has ODBC in it
   Dim tdf As DAO.TableDef
StartAgain:
 For Each tdf In CurrentDb.TableDefs
    If InStr(1, tdf.Connect, "ODBC") > 0 Then
       DoCmd.DeleteObject acTable, tdf.Name
       GoTo StartAgain
    End If
 Next tdf
 Set tdf = Nothing
End Sub

 

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 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.

Posted in Access Help, VBA
7 comments on “Part 3: Destroy your linked tables on exit
  1. Dick weber says:

    Juan:

    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.

    Dick

    • Juan Soto says:

      Dick,

      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 says:

        Juan:

        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.

        Dick

  2. Malcolm says:

    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,
    Malcolm

  3. Nicolas says:

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

Free email subscription

Enter your email address:


Facebook

Twitter

Blog Archives

ITImpact
%d bloggers like this: