Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 05-04-2017, 05:52 PM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,834
Thanks: 107
Thanked 2,662 Times in 2,435 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
How to remove 'deleted database objects' from the MSysObjects table

EDIT 15/05/2017: Added additional information & an example database (with MSysObjects errors left in)

The 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 attached image - tblSysObjectTypes



Normally the MSysObjects table works very well but things can go wrong leaving incorrect data in the table e.g. if the database crashes for any reason
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
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 in zip file).

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

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

It is NOT possible to remove such objects using code like:
DoCmd.DeleteObject acForm, "Form_~TMPCLP21541"

Similarly, 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

Until recently, I thought 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. This does work!

Code:
Sub DeleteTmpClpObjects()

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

'===============================================

'NOTE: an alternative approach that also works is:
'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

'===============================================

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
            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.Number & " in DeleteTmpClpObjects procedure :" & vbNewLine & _
        " - " & err.Description, vbExclamation, "Error"

    Resume Next
    
End Sub
NOTE: As many of you will already know, 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 and 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!

Quote:
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
2. Wrongly 'flagged' items
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) and the object name will look similar to this:

~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' - I've no idea why.
As a result, these 'wrongly flagged items appeared in the query designer window (see screenshots in zip file)

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

Code:
Sub DeleteMSysErrorObjects()

'===============================================
'Purpose : remove any incorrectly flagged Msys objects 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
            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 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
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 screenshot in zip file)
A third routine was created to deal with these items - this was slightly trickier to manage - but the following does work

Code:
Sub DeleteMSysExtinctObjects()

'===============================================
'Purpose : remove any Msys objects that are no longer in the database
'Author  : Colin Riddington - MendipData 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
            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 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
-----------------------------------------------------------
Attached is a zip file containing an example database, MSysObjectEditor-WithErrorObjects.accdb including all 3 routines.
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 the 4 images - please copy these to the same folder as they are referenced in the database main form

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, it is STRONGLY recommended that you make a copy of the front-end database - just in case!
I have included a routine CopyCurrentDatabase to do this (or you can use your own version if you prefer - modify the code if so...)


I hope this helps some of you ....

Any comments, questions or issues, please send me a private or visitor message
Attached Images
File Type: png tblSysObjectTypes.PNG (59.3 KB, 2989 views)
Attached Files
File Type: zip MSysObjectsEditor.zip (324.6 KB, 370 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 08-13-2017 at 01:53 PM. Reason: Added additional info & an example database
isladogs is offline  
The Following 4 Users Say Thank You to isladogs For This Useful Post:
DBQueen (04-23-2018), MrHans (07-20-2018), Orthodox Dave (05-18-2017), Papashep (05-08-2017)
Old 05-05-2017, 02:50 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,335
Thanks: 0
Thanked 735 Times in 720 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: How to remove 'deleted object' code from the VBE

Compacting will remove them.
Ranman256 is offline  
Old 10-25-2017, 01:29 AM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,834
Thanks: 107
Thanked 2,662 Times in 2,435 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: How to remove 'deleted database objects' from the MSysObjects table

This reply by Ranman has only just appeared though it was written in May.

Quote:
Compacting will remove them.
I agree that should be the case.
However, the whole point of the thread was to provide ways of fixing MSysObjects when things go wrong

In the first post I wrote:
Quote:
Normally the MSysObjects table works very well but things can go wrong leaving incorrect data in the table e.g. if the database crashes for any reason

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline  
Old 03-21-2019, 05:04 AM   #4
Louverril
Newly Registered User
 
Join Date: Mar 2011
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Louverril is on a distinguished road
Re: How to remove 'deleted database objects' from the MSysObjects table

Thanks for this!

Louverril is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Deleted object code left in the VBE isladogs General 5 04-30-2017 11:00 AM
Object deleted from form but still there when selecting in VBA code aldeb Forms 3 10-14-2008 09:41 AM
How do you remove DB Object Screen? pepegot Forms 1 03-18-2006 12:28 PM
Report Deleted But VBE Class Object Remains karmahum General 6 12-11-2005 03:34 PM
Can't remove "#deleted" from a field - Access complains 'Record is Deleted' RSIboy Tables 2 08-20-2004 07:45 AM




All times are GMT -8. The time now is 10:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World