First Published 24 June 2022 Last Updated 4 July 2022 Difficulty level : Moderate
Section Links: Introduction Object Type And Flags Object Recovery Recovery Methods Summary Of Recovery Methods Managing Corruption
NOTE: This is a companion to my earlier article: Remove Deleted Objects from the MSysObjects table
Introduction Return To Top
OK . . . we’ve all done it. Without thinking, you deleted an object that you still need . . .
Access does not have a recycle bin so are yourdeleted objects lost forever?
Or is it possible to recover them? It depends . . .
Tables / queries can normally be recovered provided the database is still open & not compacted
When you try to delete a table or query, a warning is shown providing the setting below is ticked in the Client Settings section of Access Options.
This is the DEFAULT option.
If the option is unticked, the object is deleted without warning However, using the default option (ticked), a warning similar to this is shown:
If you click Yes to delete, the tables / queries are saved as ~TMPCLP (temporary clipboard) objects
The deleted objects are assigned with 4097 added to the existing Flags value so the deleted objects become deep hidden
a) 4096 indicates the object is deleted ; 1 indicates it is deep hidden.
b) Deep hidden objects cannot be made visible in the navigation pane.
~TMPCLP objects are also created for all types of deleted linked tables (Access / Excel / text / ODBC).
These can also be recovered but it is normally easier to just relink the table(s) to your database.
Similarly deleted macros can normally be recovered whilst the database is still open & not compacted
The deleted macros are renamed as ~TMPCLP objects so they are again not visible in the navigation pane but the Flags value remains 0.
However, forms / reports / modules are normally deleted permanently (after a clear warning)
However, occasionally these also can be recovered where a ~TMPCLP object was created e.g. this can sometimes happen during a program crash
Any ~TMPCLP objects that contain code will be listed in the Visual Basic Editor (VBE) and the code remains visible and fully functional!
A list of deleted objects can be obtained by querying the read only MSysObjects system table.
SELECT DISTINCT MSysObjects.Name, MSysObjects.Type, MSysObjects.Flags, tblSysObjectTypes.Object
FROM MSysObjects INNER JOIN tblSysObjectTypes ON MSysObjects.Type = tblSysObjectTypes.Type
WHERE (((MSysObjects.Name) Like '~TMPCLP*'))
ORDER BY tblSysObjectTypes.Object;
A lookup table tblSysObjectTypes has been used to determine the object type and category from the Type and Flags fields in the MSysObjects table
Some further explanation may be helpful here:
Object Type and Flags Return To Top
These two fields are used to identify each type of object in an Access database
The Type field identifies the type of object:
The Flags field provides additional information about the object and its status. The value depends on whether the object is visible / hidden or deleted:
For example, these are the Flags values for local tables with standard & complex datatypes
Similarly the query type is also identified by its Flags value:
All Flags values are based on the hexadecimal (hex) number system (base 16) using 0 to 9 then A to F.
Hex(1) = 1 , Hex(8) = 8, Hex(10) = A, Hex(15) = F
Hex(16)= 10, Hex(24) = 18, Hex(26) = 1A, Hex(31) = 1F
Hex(256)= 100, Hex(264) = 108, Hex(266) = 10A, Hex(271) = 10F
Hex(4096) = 1000
Hex(262144) = 40000
In summary, the table below shows some of the possible Flags values for tables & select queries together with their hex values and meaning.
Flags values are combined to manage all the attributes of an object.
A thorough understanding of what the various Type and Flags values mean is very important for recovering objects successfully.
The Type and Flags fields cannot be used to distinguish standard / class modules.
However, these can be identified as standard or class modules using the Module.Type property.
Object Recovery Return To Top
In order to recover deleted objects, do one or more of the following recovery methods whilst your database is still open.
Do NOT close or compact your database as that will delete the ~TMPCLP tables & queries i.e. all ~TMPCLP objects with non-zero Flags values
This shows the results of the same query after compacting the database.
All deleted tables, queries and macros have been removed permanently
If you can’t do a recovery immediately, first make a backup then consider breaking one of the cardinal rules for Access . . .
Forcibly close your db using Task Manager – the ~TMPCLP objects should still exist when re-opened.
However, this risks corrupting your database – hence the need to backup first.
Or, perhaps a safer option, just recover the deleted objects from the backup you just created.
You should now be ready to start attempting a recovery of your deleted objects
1. Restore from Backup Return To Top
This is often the simplest method where regular backups have been done
However, any changes made since the last backup will be lost
Of course, you do backup regularly . . . don’t you?
OK, so you don’t have a recent backup … what else can you do?
2. Use Recovery Software Return To Top
These are very widely advertised and usually expensive commercial apps
Many of these only restore tables / queries e.g. SysTools / DataNumen
A few also restore other database objects e.g. AccessFIX, Stellar
NOTE : tables with complex fields such as attachments and multivalued fields are usually NOT fully recovered
To accompany this article, I intend to write a detailed review of several widely advertised recovery apps in the near future
You should be aware that all recovery software use the same methods that you can do yourself as described below
In general, my advice is to avoid spending your money unnecessarily
3. DoCmd.Rename Return To Top
e.g. DoCmd.Rename (NewName, ObjectType, OldName)
e.g. DoCmd.Rename "tblRecovered1", acTable, "~TMPCLP372154"
DoCmd.Rename "frmRecovered1", acForm, "~TMPCLP29151"
a) Tables / queries – this doesn’t remove the 4097 flag - so these objects remain deep hidden
b) Forms / Reports / Macros / Modules – all recovered successfully
c) Can’t use this method on ACCDE files as objects can’t be renamed using code
4. DoCmd.CopyObject Return To Top
Syntax: DoCmdCopyObject (DestinationDatabase, NewName, SourceObjectType, SourceObjectName)
Leave the first argument blank for objects copied in the current db
As method 3. above but TMPCLP object remains – need to delete this manually using DoCmd.DeleteObject
e.g. DoCmd.CopyObject , "tblRecovered1", acTable, "~TMPCLP66311"
DoCmd.DeleteObject acTable, "~TMPCLP66311"
5. DoCmd.TransferText Return To Top
This can be used to export database objects to external text files then import them back again as a new object
Syntax: DoCmd.TransferText (TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
e.g. DoCmd.TransferText acExportDelim, , "~TMPCLP66311", CurrentProject.Path & "\tblTemp.txt", True
DoCmd.TransferText acImportDelim, , "tblRecovered2", CurrentProject.Path & "\tblTemp.txt", True
This method also works for tables, but with major limitations:
a) primary key field index removed
b) autonumber changed to number
c) text field size may change
d) complex fields returned as standard fields
e) column history lost
This approach is NOT recommended due to issues above
6. Create New Table Return To Top
Create a new table using a make table query - SELECT . . . INTO
e.g. db.Execute "SELECT * INTO tblRecovered3” & " FROM “~TMPCLP532164”;", dbFailOnError
This method works for tables with standard datatypes BUT the new table has no primary key
However it fails for all tables with complex datatypes with error 3838: Multi-valued fields are not allowed in SELECT INTO statements.
7. Rename & remove deleted flag Return To Top
e.g. DoCmd.Rename "tblRecovered1", acTable, "~TMPCLP372154"
CurrentDb.TableDefs("tblRecovered1").Attributes = 0 NOTE: This line resets the Flags value to zero so the renamed table is visible.
This works for all standard tables and for complex tables with attachment & column history fields.
However, the Flags value is reset to 0 for all tables whether standard or complex
Also, all multivalued (MVF) field data will be lost.
This is because the MVF data source from a table/query or value list becomes detached when deep hidden i.e. when deleted
This is yet another very good reason for avoiding the use of multivalued fields. See my article: Multivalued Fields . . . and why you really shouldn't use them!
However, there is a solution, albeit rather obscure, which I discovered by chance when researching information for my article: A Complex Deep Hidden Attachment Mystery
The unexpected (and undocumented) solution is to temporarily add an attachment field during the restore process.
This single step does all the following things:
a) restores the links to the deep hidden attached system tables
b) recovers the MVF data
c) restores the 262144 flags value used for complex tables.
The added attachment field is no longer required and can safely be removed again. TOTAL MAGIC!
'code used to restore MVF data and Flags =262144 for all tables with complex fields
Dim fld As DAO.Field
'rename & reflag
DoCmd.Rename "tblRecovered3", acTable, "~TMPCLP351143"
CurrentDb.TableDefs("tblRecovered3").Attributes = 0
'now add attachment field to restore MVF data & flags value
Set fld = CurrentDb().TableDefs("tblRecovered3").CreateField("NewField", 101&)
Set fld = Nothing
'remove attachment field again as no longer needed
By using this additional code, the method now works for all tables!
Unfortunately, this approach cannot be used with queries as it can’t remove deleted flag
This method also cannot be used with tables in ACCDE files as we can’t rename ACCDE objects using code
8. Copy Object & remove deleted flag Return To Top
This approach combines parts of methods 4 and 7.
e.g. DoCmd.CopyObject , "tblRecovered1", acTable, "~TMPCLP643601"
CurrentDb.TableDefs("tblRecovered1").Attributes = 0
DoCmd.DeleteObject acTable, "~TMPCLP643601"
This method works in ACCDB files but again fails on the CopyObject line in ACCDE files with error 7874 - cannot find deleted object
Therefore it has no advantages compared to method 7
9. Rebuild Queries – from MSysQueries Return To Top
Access stores the details of all queries in a read only system table MSysQueries. This includes the details of all recently deleted ~TMPCLP queries and can therefore be used to reconstruct the queries . . . at least in principle!
For more details, see my article: How Access Stores Queries
For example, the select query below uses two system tables MSysObjects & MSysQueries to give the details of all deleted queries
SELECT MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Expression, MSysQueries.Flag, MSysQueries.Name1, MSysQueries.Name2
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysObjects.Name) Like "~TMPCLP*") AND ((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3));
I deleted the query then looked at how the query was constructed:
With a bit of effort, it would be possible to reconstruct that query SQL
As another example, this is a fairly straightforward crosstab query
TRANSFORM First(tblSysObjectTypes.Type) AS FirstOfType
SELECT tblSysObjectTypes.Object, tblSysObjectTypes.Flags
GROUP BY tblSysObjectTypes.Object, tblSysObjectTypes.Flags
These are the results after I deleted that query
In theory, it would be possible to retrieve any query no matter how complex by reverse engineering it using MSysQueries
However it would be VERY hard work to do so, especially for more complicated queries.
Luckily . . . much easier methods exist
10. Re-create Queries Return To Top
For more details, see the article by Wayne Phillips at: Undelete Tables and Queries in Access
This works but some of the code is fairly complex – once again, easier methods exist
Wayne's code also includes a clever method to optionally recover the original table name from the name autocorrect data where this in use.
I have chosen NOT to include this in my code as many developers disable the name autocorrect feature.
Also, it may be better to restore objects using a generic name such as tblRecovered1 to ensure the developer examines each object in turn after recovery.
11. SaveAsText / LoadFromText Return To Top
This is an undocumented method of saving database objects externally and then retrieving the information again
This method is often used for version control and for object backups
Syntax: Application.SaveAsText(ObjectType As AcObjectType, ObjectName As String, FileName As String)
Application.LoadFromText(ObjectType As AcObjectType, ObjectName As String, FileName As String)
e.g. Application.SaveAsText acQuery, "~TMPCLP258081", CurrentProject.Path & “\Query1.txt”
Application.LoadFromText acQuery, "qryRecovered1", CurrentProject.Path & “\Query1.txt”
This works for all database objects except Tables
All queries including those with complex datatypes are recovered successfully
After recovering your database objects, check each in turn and rename those you want to keep with appropriate names.
Any objects you don't need can be safely deleted again (do another backup first).
This time, DO compact or close the database to remove the objects permanently.
Summary of Recovery Methods Return To Top
The following methods are recommended for each object type:
In the next week or so, I intend to upload a YouTube video and an example database demonstrating these methods.
The screenshots below are taken from the recovery form provided with the example database:
a) Database with 20 deleted objects that can be recovered. The object types and categories are shown for each deleted object
b) 1 deleted query selected and recovered as qryRecovered1
c) Recovered query qryRecovered1 shown in navigation pane. Form is updated
d) All remaining deleted objects selected for recovery
e) All deleted objects recovered
f) All deleted objects are now shown in navigation pane and ready for inspection
Of course, if you make regular backups and use version control you will just need to recover from a backup.
None of the other methods will ever be required.
a) Table indexes ARE recovered when using method 7: Rename & remove deleted flag
b) Table relationships CANNOT be recovered. That is because it is necessary to remove a relationship BEFORE deleting a table
I intend to write a detailed article on this topic in the future. In the meantime, look into the following suggestions at the links below
12. Recovering from Corruption Return To Top
a) Recovering from corruption
b) Database Repair Service for Microsoft® Access
c) Use Recovery Software – see point 2 above / similar comments may apply
13. Preventing Corruption Return To Top
a) Preventing corruption of installed databases
b) Top 10 ways to prevent Access database corruption
I hope you have found this article both informative and useful. Please do check back for additional information to follow
I would appreciate feedback on this article, including details of any errors or omissions.
Colin Riddington Mendip Data Systems Last Updated 4 July 2022
Return to Access Articles Page
Return to Top