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.