Last Updated 4 Dec 2020     (additional information related to queries used)                                                 Difficulty level : Moderate

Click any image to view a larger version

Fig 1:
Reference table tblSysObjectTypes

tblSysObjectTypes

Fig 2:
Deleted objects are renamed ~TMPCLP.
Compacting SHOULD remove these.
If not, the 'phantom' objects remain in the VBE

DeletedObjectVBE

Fig 3:
ALL form/report row source objects SHOULD have flag value = 3. If not, these will incorrectly appear in the query designer

MSysObjectsError

Fig 4:
Query designer window error

MSysQueryErrors

Fig 5:
Extinct objects may be left in MSysObjects
table after the object has been removed.
Compacting MAY delete these . . . but not always

MSysObjectsExtinct

Section Links:         ~TMPCLP Items         Wrongly flagged items         Extinct Items         Download


NOTE: This is a companion to my newer article: Recover Deleted Database Objects


The read only system table MSysObjects keeps a record of all items in a database so these can be used by forms/reports/module code etc
Each item is identified using a type value and a flag value     (see Fig 1)

Normally the MSysObjects table works perfectly but things can go wrong leaving incorrect data in the table e.g. if the database crashes for any reason after deleting an object.

If errors build up over time, I believe these may eventually make the database unusable.
In other words, don't ignore the problems as they won't go away!

NOTE:
It is important to remember that the MSysObjects table has been made deliberately difficult to edit.
This is to prevent users tampering without realising the potential consequences to their databases.

This article discusses 3 types of problem that can occur & a solution for each



1.   ~TMPCLP items                                                                                                   Return To Top

After a database object is deleted, Access keeps a copy in 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 as a 'phantom' in the VBE as something like 'Form_~TMPCLP29151'     (see Fig 2)

The object itself is of course no longer available or listed in the navigation pane.

The objects remain in the system table MSysObjects but (for safety reasons) this table cannot be directly edited

Running a compact/repair does NOT remove these 'phantom' items.

It is SOMETIMES possible to remove such objects using code like this:

          DoCmd.DeleteObject acForm, "~TMPCLP21541"

However, the 'standard' solution is to create a new database & import all items from the original database.
The ~TMPCLP items are of course not transferred.
Although this works, it is time consuming if you have a large database.

I used to think there was no way of directly deleting the 'phantom' object code from the VBE. However, after some experimentation, I came up with 2 successful approaches :

a)  Use a recordset to cycle through & delete the ~TMPCLP objects found.

      First create a query qryMSysObjectsTMPCLP:

 SELECT MSysObjects.Name, MSysObjects.Type
 FROM MSysObjects
 WHERE MSysObjects.Name Like '~TMPCLP*'
 ORDER BY MSysObjects.Name;


      Now use that query in the following procedure (stored in a standard module)

      CODE:

Sub DeleteTmpClpObjects()

'===============================================
'Purpose : remove any leftover ~TMPCLP objects from the database
'Author  : Colin Riddington - Mendip Data 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", _
               vbExclamation + vbOKCancel, "Remove " & N & " TMPCLP objects?") = vbCancel Then Exit Sub
   End If

   If MsgBox("You should backup the 'front end' database BEFORE deleting these objects" & vbNewLine & vbNewLine & _
       "Do a backup now? (RECOMMENDED)", vbExclamation + vbYesNo, "Copy the front end database?") = vbYes Then
           CopyCurrentDatabase
   End If

   strSQL = "SELECT qryMSysObjectsTMPCLP.* FROM qryMSysObjectsTMPCLP;"

   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     'macro
           DoCmd.DeleteObject acMacro, Rs("Name")

       Case -32761     'module
           DoCmd.DeleteObject acModule, Rs("Name")

       Case Else
                '???
           'look at what gets 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.Number & " in DeleteTmpClpObjects procedure :" & vbNewLine & _
       " - " & err.Description, vbExclamation, "Error"
   Resume Next

End Sub


      NOTE:
      The values in the select case statement refer to the object type code from the MSysObjects table

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

b)  There is an even easier method if you want to avoid code:
      Just replace the 'phantom' object with a real one! Amazingly this works!

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



2.   Wrongly 'flagged' items                                                                                   Return To Top

      All form & report items containing a row source are assigned their own record in the MSysObjects
      table. These are identified as TEMP queries (type = 5 ; flag = 3).

      The object name will look similar to these examples:
          ~sq_c#frmMessageLog~sq_ccboPhone
          ~sq_cfrmMessageLog~sq_ccboPhone
          ~sq_cfrmSendEmail~sq_cLstContactEmail
          ~sq_ffrmPStats3
          ~sq_rrptPRecordTeacherCrosstab

      NOTE: ~sq_f = form ; ~sq_c = form control ; ~sq_r = report ; ~sq_d = report control

      Access ignores these 'TEMP queries' in the query designer window

      However, recently one of my databases, wrongly re-flagged some items as 'normal queries'.
      As a result, these 'wrongly flagged' items appeared in the query designer window   (see Fig 3 & 4)

      I created another routine, similar to the first one, to remove these items

      For this, another query qryMSysObjectsERROR is needed:

 SELECT MSysObjects.Name, MSysObjects.Flags, tblSysObjectTypes.Type, tblSysObjectTypes.SubType,
        tblSysObjectTypes.Object
 FROM MSysObjects INNER JOIN tblSysObjectTypes ON (MSysObjects.Flags = tblSysObjectTypes.Flags) AND
        (MSysObjects.Type = tblSysObjectTypes.Type)
 WHERE (((MSysObjects.Name) Like "~sq_*") AND ((MSysObjects.Flags)<>3))
 ORDER BY MSysObjects.Name;



      Now use that query in the following procedure (stored in a standard module)

      CODE:

Sub DeleteMSysErrorObjects()

'===============================================
'Purpose : remove any incorrectly flagged MSysObjects from the database
'Author  : Colin Riddington - MendipData Systems
'Date    : 12/05/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 incorrectly flagged objects
   N = DCount("*", "qryMSysObjectsERROR")

   If N = 0 Then
       MsgBox "There are no 'incorrectly flagged' database objects" & _
           vbNewLine & vbNewLine & _
           "This routine will now close", vbInformation, "No incorrectly flagged objects"
       Exit Sub
   Else
       If MsgBox("There are " & N & " 'incorrectly flagged' database objects" & _
           vbNewLine & vbNewLine & _
           "Click OK to remove these objects from the database", _
               vbExclamation + vbOKCancel, "Remove " & N & " incorrectly flagged objects?") = vbCancel Then Exit Sub
   End If

    If MsgBox("You should backup the 'front end' database BEFORE deleting these objects" & vbNewLine & vbNewLine & _
       "Do a backup now? (RECOMMENDED)", vbExclamation + vbYesNo, "Copy the front end database?") = vbYes Then
           CopyCurrentDatabase
   End If

   strSQL = "SELECT qryMSysObjectsERROR.* FROM qryMSysObjectsERROR;"

   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     'macro
           DoCmd.DeleteObject acMacro, Rs("Name")

       Case -32761     'module
           DoCmd.DeleteObject acModule, Rs("Name")

       Case Else
           '???
           'look at what gets 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 incorrectly flagged objects
   Q = DCount("*", "qryMSysObjectsERROR")

   'end message
   If Q = N Then 'none removed
       MsgBox "None of the " & N & " 'incorrectly flagged' database objects'" & _
           " could be removed from the database", vbCritical, "Incorrectly flagged objects were not deleted"
   ElseIf Q = 0 Then 'all removed
       MsgBox "All " & N & " 'incorrectly flagged' database objects" & _
           " have been removed from the database", vbInformation, "Incorrectly flagged objects successfully deleted"
   Else 'partly deleted
       MsgBox N - Q & " 'Incorrectly flagged' database objects" & _
           " have been removed from the database" & vbNewLine & vbNewLine & _
           "However, " & Q & " 'incorrectly flagged' objects were not removed", vbExclamation, "Incorrectly flagged objects partly deleted"
   End If

Exit_Handler:
   Exit Sub

Err_Handler:
   MsgBox "Error " & err.Number & " in DeleteMSysErrorObjects procedure :" & vbNewLine & _
       " - " & err.Description, vbExclamation, "Error"

   Resume Next

End Sub




3.   'Extinct' items                                                                                                     Return To Top

      I also found a number of items which were still in the MSysObjects table though the objects had
      already been deleted.
      I identified these by checking for 'parent' objects no longer in the database   (see Fig 5)

      A third routine was created to deal with these items.
      This was slightly trickier to manage - but the following does work!

      First create a query qryMSysObjectsTEMP:


 SELECT MSysObjects.Name,  IIf(InStr(Mid([Name],6),"~")>0, Mid([Name],6,InStr(Mid([Name],6),"~")-1),
       Mid([Name],6)) AS ParentObjectName, MSysObjects.Flags, tblSysObjectTypes.Type, tblSysObjectTypes.SubType,
       tblSysObjectTypes.Object
 FROM MSysObjects INNER JOIN tblSysObjectTypes ON (MSysObjects.Flags = tblSysObjectTypes.Flags) AND
       (MSysObjects.Type = tblSysObjectTypes.Type)
 WHERE (((MSysObjects.Name) Like "~sq_*") AND ((MSysObjects.Flags)=3))
 ORDER BY MSysObjects.Name;


      Next create another query qryMSysObjectsEXTINCT:

 SELECT qryMSysObjectsTEMP.Name, qryMSysObjectsTEMP.ParentObjectName AS EXTINCTParentObjectName,  
       qryMSysObjectsTEMP.Flags, qryMSysObjectsTEMP.Type, qryMSysObjectsTEMP.SubType,
       qryMSysObjectsTEMP.Object
 FROM qryMSysObjectsTEMP LEFT JOIN MSysObjects ON qryMSysObjectsTEMP.ParentObjectName =
       MSysObjects.Name
 WHERE (((MSysObjects.Name) Is Null));


      Now use those queries in the following procedure (stored in a standard module)

      CODE:

Sub DeleteMSysExtinctObjects()

'===============================================
'Purpose : remove any MSysObjects that are no longer in the database
'Author  : Colin Riddington - Mendip Data Systems
'Date      : 13/05/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 MSys objects that are 'extinct' (no longer in the database)
   N = DCount("*", "qryMSysObjectsEXTINCT")

   If N = 0 Then
       MsgBox "There are no 'extinct' database objects" & _
           vbNewLine & vbNewLine & _
           "This routine will now close", vbInformation, "No extinct objects"
       Exit Sub
   Else
       If MsgBox("There are " & N & " 'extinct' database objects" & _
           vbNewLine & vbNewLine & _
           "Click OK to remove these objects from the database", _
               vbExclamation + vbOKCancel, "Remove " & N & " extinct objects?") = vbCancel Then Exit Sub
   End If

   If MsgBox("You should backup the 'front end' database BEFORE deleting these objects" & vbNewLine & vbNewLine & _
       "Do a backup now? (RECOMMENDED)", vbExclamation + vbYesNo, "Copy the front end database?") = vbYes Then
           CopyCurrentDatabase
   End If

   strSQL = "SELECT qryMSysObjectsEXTINCT.* FROM qryMSysObjectsEXTINCT;"

   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     'macro
           DoCmd.DeleteObject acMacro, Rs("Name")

       Case -32761     'module
           DoCmd.DeleteObject acModule, Rs("Name")

       Case Else
           '???
           'look at what gets 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 MSys objects that are 'extinct' (no longer in the database)
   Q = DCount("*", "qryMSysObjectsEXTINCT")

   'end message
   If Q = N Then     'none removed
       MsgBox "None of the " & N & " 'extinct' database objects'" & _
           " could be removed from the database", vbCritical, "Extinct database objects were not deleted"
   ElseIf Q = 0 Then     'all removed
       MsgBox "All " & N & " 'incorrectly flagged' database objects" & _
           " have been removed from the database", vbInformation, "Extinct database objects successfully deleted"
   Else     'partly deleted
       MsgBox N - Q & " 'extinct' database objects" & _
           " have been removed from the database" & vbNewLine & vbNewLine & _
           "However, " & Q & " 'extinct' database objects were not removed", _
               vbExclamation, "Extinct database objects partly deleted"
   End If

Exit_Handler:
   Exit Sub

Err_Handler:
 '  If Err <> 5 And Err <> 3071 And Err <> 2498 Then
       MsgBox "Error " & err.Number & " in DeleteMSysEXTINCTObjects procedure :" & vbNewLine & _
           " - " & err.Description, vbExclamation, "Error"
 '  End If
   Resume Next

End Sub




4.   Download                                                                                                           Return To Top

      Click to download an example application which includes all 3 routines:

            MSysObjectEditor-WithErrorObjects          Approx 1.1 MB   (zipped)

      I've deliberately left several of each type of error in the db so you can test out the routines for yourself.

      There are 30 items in the MSysObjects table of which 17 are errors to be removed.
      After running all 3 routines, you will be left with just 13 valid items

      I've also included 4 images
      Please copy these to the same folder as they are referenced in the main form of this application

      If you wish to use the routines in your own databases, copy the following items:
      a)   table tblSysObjectTypes
      b)   all 5 queries starting with qryMSys ...
      c)   module modDatabaseObjects
      d)   module modBackupDB - only needed if you want to use my backup routine CopyCurrentDatabase

      NOTE:
      BEFORE running any of these routines in your own applications, it is STRONGLY recommended that you
      make a copy of the front-end database - just in case!

      I have included a backup routine CopyCurrentDatabase to do this (or you can use your own backup
      code if you prefer - modify the code if so...)



Colin Riddington               Mendip Data Systems                 Last Updated 4 Dec 2020



Return to Access Articles Page




Return to Top