Deleted object code left in the VBE

[isladogs]

Hi

After a database object is deleted, Access keeps a copy on memory until the database is closed.
However, if the database crashes for some reason before that has been done, the code for the object remains in the VBE as something like 'Form_~TMPCLP29151 (see screenshot).
The object itself is of course no longer available or listed in the nav pane.

There seems to be no way of deleting the 'phantom' object code from the VBE

Until now, I have got rid of these by periodically creating a new database & copying all items across.
The ~TMPCLP items are of course not transferred.

Just wondering if anyone has a better solution

 

[Ranman256]

normally, when you compact the db, they vanish.
did you do this?

 

[isladogs]

Yes of course...!

Compacting NEVER clears these'legacy items
Nor does decompiling.

 

[JHB]

..
Until now, I have got rid of these by periodically creating a new database & copying all items across.
The ~TMPCLP items are of course not transferred.

Just wondering if anyone has a better solution

When I've had the problem, I've done the same as you.

 

[isladogs]

Thanks
Hopefully someone else has found a quicker way!

 

[isladogs]

Hooray - I've solved the problem which has been irritating me on & off for years
After doing Google searches, there are plenty of others who feel the same!

As it may be of use to others, I'm posting the 2 solutions I found here

As I wrote in post #1, doing a compact/repair does NOT remove these 'phantom' items.

The 'standard' solution is to create a new database & copying all items across. The ~TMPCLP items are of course not transferred.

Although this works, it is time consuming if you have a large database

After some experimentation & internet searches, I tried 3 approaches

1. Firstly I tried to delete the items with code like this:

Public Sub DeleteTMPCLPItems()

DoCmd.DeleteObject acForm, "Form_~TMPCLP21541"
DoCmd.DeleteObject acForm, "Form_~TMPCLP519071"
DoCmd.DeleteObject acReport, "Report_~TMPCLP595641"

End Sub

This fails with error 7874
If error handling is added to manage this, nothing is deleted.
Don't bother

In fact, none of the other solutions I found online worked for me

2. I then wrote this routine to cycle through all database objects & delete the ~TMPCLP objects found. It worked!!

Sub DeleteTmpClpObjects()

'===============================================
'Purpose : remove any leftover TMPCLP objects from the database
'Author  : Colin Riddington - MendipData Systems
'Date    : 30/04/2017
'===============================================

Dim Rs As DAO.Recordset
Dim strSQL As String
Dim N As Integer
Dim Q As Integer

On Error GoTo Err_Handler

'check for '~TMPCLP' objects
    N = DCount("*", "MSysObjects", "MSysObjects.Name Like '~TMPCLP*'")
    
    If N = 0 Then
        MsgBox "There are no 'leftover' database objects named '~TMPCLP*" & _
            vbNewLine & vbNewLine & _
            "This routine will now close", vbInformation, "No TMPCLP objects"
        Exit Sub
    Else
        If MsgBox("There are " & N & " 'leftover' database objects named '~TMPCLP*" & _
            vbNewLine & vbNewLine & _
            "Click OK to remove these objects from the database", _
                vbOKCancel, "Remove " & N & " TMPCLP objects?") = vbCancel Then Exit Sub
    End If
    
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects" & _
    " WHERE MSysObjects.Name Like '~TMPCLP*' ORDER BY MSysObjects.Name;"

Set Rs = CurrentDb.OpenRecordset(strSQL)

    Do Until Rs.EOF
        Select Case Rs("Type")
        Case 1, 4, 6 'tables
            DoCmd.DeleteObject acTable, Rs("Name")
        Case 5 'queries
            DoCmd.DeleteObject acQuery, Rs("Name")
        Case -32768 'form
            DoCmd.DeleteObject acForm, Rs("Name")
        Case -32764 'report
            DoCmd.DeleteObject acReport, Rs("Name")
        Case -32766
            DoCmd.DeleteObject acMacro, Rs("Name")
        Case -32761
            DoCmd.DeleteObject acModule, Rs("Name")
        
        Case Else
            '???
            'look at what was printed in the immediate window
            'and include the type in the select case
            Debug.Print Rs("Type"), Rs("Name")
        
        End Select

   
    Rs.MoveNext
    Loop
    Rs.Close
    
Set Rs = Nothing

'check again for '~TMPCLP' objects
    Q = DCount("*", "MSysObjects", "MSysObjects.Name Like '~TMPCLP*'")
    
'end message
    If Q = N Then 'none removed
        MsgBox "None of the " & N & " 'leftover' database objects named '~TMPCLP*'" & _
            " could be removed from the database", vbCritical, "TMPCLP objects were not deleted"
    ElseIf Q = 0 Then 'all removed
        MsgBox "All " & N & " 'leftover' database objects named '~TMPCLP*'" & _
            " have been removed from the database", vbInformation, "TMPCLP objects successfully deleted"
    Else: 'partly deleted
        MsgBox N - Q & " 'leftover' database objects named '~TMPCLP*'" & _
            " have been removed from the database" & vbNewLine & vbNewLine & _
            "However, " & Q & " '~TMPCLP' objects were not removed", vbExclamation, "TMPCLP objects partly deleted"
    End If

Exit_Handler:
    Exit Sub
    
Err_Handler:
    'MsgBox "Error " & Err & " : " & Err.Description
    Resume Next
    
End Sub

This worked perfectly & was very fast.
In a large db of about 5000 objects, 3 TMPCLP objects were found and removed in less than 2 seconds
Surprisingly, only one of these were shown in the VBE window

3. There is an even easier method if you want to avoid code:
Just replace the 'phantom' object with a real one!

Create a new form/report with the same name.
Save it and replace the existing form when prompted
Close the new form.
It is automatically deleted along with the TMPCLP item