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

NOTE:
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

NOTE:
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;

NOTE:
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:



NOTE:
All Flags values are based on the hexadecimal (hex) number system (base 16) using 0 to 9 then A to F.
For example:

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.

NOTE:
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.
See https://docs.microsoft.com/en-us/office/vba/api/access.module.type





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



Recovery Methods

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"

NOTE:
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!

EXAMPLE CODE:

'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"
DoEvents
CurrentDb.TableDefs("tblRecovered3").Attributes = 0

'now add attachment field to restore MVF data & flags value
Set fld = CurrentDb().TableDefs("tblRecovered3").CreateField("NewField", 101&)
CurrentDb().TableDefs("tblRecovered3").Fields.Append fld
CurrentDb().TableDefs.Refresh

Set fld = Nothing

'remove attachment field again as no longer needed
CurrentDb().TableDefs("tblRecovered3").Fields.Delete "NewField"

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
FROM tblSysObjectTypes
GROUP BY tblSysObjectTypes.Object, tblSysObjectTypes.Flags
PIVOT tblSysObjectTypes.SubType;

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

NOTE:
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:



NOTE:
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.

Other points:
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





Managing Corruption

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