Author note: This is part 3 of a series on DSN-less tables in Access.
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.