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.
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
Application.Quit
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
Else
Counter = Counter + 1
remaining = 1 = Counter
MsgBox “Wrong password!” + vbCrLf & _
“You have ” & remaining & ” attempt(s) left”, _
vbOKOnly, “Password Info”
End If
Loop
Application.Quit
Exit Function
End If
End If
End Function
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.
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
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.
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
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
Great suggestion Malcolm! Thanks!
Juan
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.RefreshThanks and Regards
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.
Thanks!
Juan