Greetings,
In an Access 2007 application, I accidentally double-linked some table to the SQL BE DB, thus Access named the dupe links with a 1 appended to the dupe object. I deleted those.
While checking the MSysObjects table, I saw yet remaining all of those orphaned objects, even thought they were deleted from the Access UI \ Navigation Pane.
So I worked up the following code, stuck it in a new button on my admin/cleanup form.
The code logs its progress to the Immediate window.
Perhaps this helps someone else.
In an Access 2007 application, I accidentally double-linked some table to the SQL BE DB, thus Access named the dupe links with a 1 appended to the dupe object. I deleted those.
While checking the MSysObjects table, I saw yet remaining all of those orphaned objects, even thought they were deleted from the Access UI \ Navigation Pane.
So I worked up the following code, stuck it in a new button on my admin/cleanup form.
The code logs its progress to the Immediate window.
Code:
Private Sub btnDeleteADOXNonsense_Click()
On Error GoTo Err_btnDeleteADOXNonsense_Click
Dim adoCat As Object
Dim lngStep As Long
Dim lngMaxCount As Long
Dim strThisObjectName As String
'Define attachment to FE database
Set adoCat = CreateObject("ADOX.Catalog")
Set adoCat.ActiveConnection = CurrentProject.Connection
'Process "~" named Tables
lngMaxCount = adoCat.Tables.Count
If lngMaxCount > 0 Then
'It is necessary to subtract 1 from what is shown in the watches window
'The watches window shows numbers starting with 1
'The numbers actually start with 0
For lngStep = lngMaxCount - 1 To 0 Step -1
strThisObjectName = adoCat.Tables.Item(lngStep).Name
'Debug.Print "Considering adoCat.Tables: " & strThisObjectName
If InStr(1, strThisObjectName, "~", vbTextCompare) <> 0 Then
Debug.Print "Call adoCat.Tables.Delete(" & strThisObjectName & ")"
Call adoCat.Tables.Delete(strThisObjectName)
End If
Next lngStep
End If
'Process Procedures
lngMaxCount = adoCat.Procedures.Count
If lngMaxCount > 0 Then
'It is necessary to subtract 1 from what is shown in the watches window
'The watches window shows numbers starting with 1
'The numbers actually start with 0
For lngStep = lngMaxCount - 1 To 0 Step -1
strThisObjectName = adoCat.Procedures.Item(lngStep).Name
Debug.Print "Call adoCat.Procedures.Delete(" & strThisObjectName & ")"
Call adoCat.Procedures.Delete(strThisObjectName)
Next lngStep
End If
'Process Views
lngMaxCount = adoCat.Views.Count
If lngMaxCount > 0 Then
'It is necessary to subtract 1 from what is shown in the watches window
'The watches window shows numbers starting with 1
'The numbers actually start with 0
For lngStep = lngMaxCount - 1 To 0 Step -1
strThisObjectName = adoCat.Views.Item(lngStep).Name
Debug.Print "Call adoCat.Views.Delete(" & strThisObjectName & ")"
Call adoCat.Views.Delete(strThisObjectName)
Next lngStep
End If
Exit_btnDeleteADOXNonsense_Click:
'Clean up the connection to the database
Set adoCat = Nothing
Exit Sub
Err_btnDeleteADOXNonsense_Click:
Call errorhandler_MsgBox("Form: " & TypeName(Me) & ", Subroutine: btnDeleteADOXNonsense_Click()")
Resume Exit_btnDeleteADOXNonsense_Click
End Sub
Last edited:
