Restore a Deleted Microsoft Access Table with VBA

This is code you hope you never have to use. The accidental deletion of a table is something that should never occur. However, if it does, hopefully you just shrug and reload it from a back-up copy. But if you don’t have a back-up, you’ll get that chilly feeling as you realize you just hosed hundreds of records that are irreplaceable.

The whole purpose of having tables of information in Microsoft Access is to be able to retrieve and work with that data whenever you can. As such, it's always a smart move to keep current backup copies of every database you work with. It only takes a minute to back up a database, and if you're unfortunate enough to experience data loss, it's a minute you'll wish you'd spent. This technique can help you recover from a mistake, but it won't help you recover a corrupted database!

When you highlight a table in your database window and hit delete, the table isn’t actually deleted although it appears to be. As long as you have not closed out of Access, you can recover the table by running the function shown below. So keep the database open, copy and paste the code shown below into a new module, and save it as “bas_undelete”. To run the code, press Ctrl+G, then type UnDeleteTable and let the code go to work. Nothing is guaranteed, but it just might recover that table of yours!

Note that the code below was originally written for Access 97. If you use it in a more recent version of Access, you'll have to enable a reference to DAO for it to work. However, you can likely find copies of this code for other Access versions ready to go through Microsoft’s online knowledgebase.

Function UnDeleteTable()
     Dim db As Database, strTablename As String
     Dim i As Integer, StrSqlString As String

     Set db = currentdb()

     'set up a loop to go through every table in the database
     For i = 0 To db.TableDefs.Count - 1

          'if the table name starts with ~tmp, then run SQL to recover it
          If Left(db.TableDefs(i).Name, 4) = "~tmp" Then
                strTablename = db.TableDefs(i).Name
                StrSqlString = "SELECT DISTINCTROW [" & strTablename & _
                  "].* INTO MyUndeletedTable FROM [" & strTablename & "];"
                DoCmd.SetWarnings False
                DoCmd.RunSQL StrSqlString
                DoCmd.SetWarnings True
                MsgBox "A table has been restored as MyUndeletedTable", _
                  vbOKOnly, "Restored"
                GoTo Exit_undo
          End If

     Next i

      MsgBox "No Recoverable Tables Found", vbOKOnly, "Not Found"

Exit_undo:
     Set db = Nothing
     Exit Function

Err_undo:
     MsgBox Err.Description
     Resume Exit_undo

End Function

David Badurina, President of Blue Moose Technology, LLC, is a relational database design expert. David's unique ability to easily explain virtually any technical concept has allowed him to work with companies such as AMD, Motorola, the American Heart Association, and countless small businesses. Learn more about database design right now at http://www.bluemoosetech.com.

Article Publishing and Re-Print Guidelines

The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
When re-published online, all links must be live hyperlinks.
These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!

You can easily link to this article!

If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:

On your page, the code will appear like this:

Restore a Deleted Microsoft Access Table with VBA