Note: This sample code uses Microsoft data Access objects. For this code to work correctly, you must refer to the Microsoft DAO 3.6 Object Library. You can do this by clicking the VisualBasic Editor in the Tools menu, referencing and making sure that the Microsoft DAO 3.6 Object Library check box is selected.
1. Open the database in MicrosoftAccess.
2. In the Database window, click the next object, module, and then click New.
3. Type or paste the following code, which you only create in the module:
Copy Code code as follows:
Function recoverdeletedtable ()
On Error GoTo Exithere
' *declarations*
Dim DB as DAO. Database
Dim strTableName as String
Dim strSQL as String
Dim intcount as Integer
Dim blnrestored as Boolean
' *init*
Set db = CurrentDb ()
' *procedure*
For intcount = 0 to DB. Tabledefs.count-1
strTableName = db. TableDefs (intcount). Name
If Left (strtablename, 4) = "~tmp" Then
strSQL = "Select Distinctrow [" & strTableName & "].* into" & Mid (strTableName, 5) & "from [" & STRT Ablename & "];"
Docmd.setwarnings False
Docmd.runsql strSQL
MsgBox "A deleted table has been restored, using the name '" & Mid (strTableName, 5) & "", vbOKOnly, "restored"
blnrestored = True
End If
Next intcount
If blnrestored = False Then
MsgBox "No recoverable tables found", vbOKOnly
End If
' *exit/error*
Exithere:
Docmd.setwarnings True
Set db = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Description
Resume Exithere
End Function
4. On the Debug menu, click the Compile database name database name.
5. Save As Recovertable module. To test this function, first create two tables, add rows, and delete the two tables.
6. In the Immediate window, type the following line, and then press ENTER:
Recoverdeletedtable