Problem:
How to recover records that have been deleted, how to recover deleted tables, forms, and so on
1, I deleted some records with delete from TABLE, now found mistakenly deleted, how to restore?
2, I manually delete or delete a table with drop table, now found to be mistakenly deleted, how to recover?
3, I manually deleted a form, how to restore?
4, I deleted the record, but the size of the database does not reduce, then whether it can retrieve records?
Reply:
1, deleted records are unrecoverable, ACCESS is not foxpro,mdb format is not DBF format, there is no logical deletion and physical deletion of the concept, once deleted can not be restored.
2, unable to recover, but you can see, there is no hidden with the "~" sign the beginning of the table, change the name of the table is likely to retrieve the table you need.
3, unable to recover, but you can see if there is no system hidden objects, sometimes the object is deleted when the system is not directly deleted, but change the object name and hide it.
4, the size of the database does not become smaller, you compress repair database after the volume will become smaller. That's because your data did not get deleted on the binary, but it was still stored in a sector of the disk, but Microsoft did not provide references to the MDB format binaries (Microsoft would not provide it, nor did other third-party companies have the right to decompile the MDB format directly). So far, I have not seen the relevant references in mainland China. So so far, the data you have deleted is unrecoverable. But you can try to use the disk recovery software to find a way to recover the data, but the method is not covered in this article.
Recommendation: When building a database structure, you can add an additional Isdel field to each table, delete the record without using delete from, and use the statement such as UPDATE table SET Isdel=true, and then do not display Isdel=true records on the interface.
Copy Code code as follows:
If not yet compressed theoretically can. Try this piece of code. Add to access Module
Restore the erased worksheet (not compressed)
Public Function fnundeleteobjects () as Boolean
On Error GoTo ErrorHandler:
Dim strObjectName as String
Dim Rstables as DAO. Recordset
Dim Dbsdatabase as DAO. Database
Dim TdeF as DAO. TableDef
Dim Qdef as DAO. QueryDef
Dim Intnumdeleteditemsfound as Integer
Set dbsdatabase = CurrentDb
For each tdef in Dbsdatabase.tabledefs
' This is actually used as a ' Deleted Flag '
If Tdef.attributes and Dbhiddenobject Then
strObjectName = Fngetdeletedtablenamebyprop (tdef.name)
strObjectName = InputBox ("A deleted TABLE has been found." & _
VbCrLf & VbCrLf & _
"To undelete this object, enter a new name:", _
"Access undelete Table", strObjectName)
If Len (strObjectName) > 0 Then
Fnundeletetable CurrentDb, Tdef.name, strObjectName
End If
Intnumdeleteditemsfound = Intnumdeleteditemsfound + 1
End If
Next TdeF
For each qdef in Dbsdatabase.querydefs
' Note ' Attributes ' flag are not exposed for QUERYDEF objects,
' We could look up the flag by using msysobjects but
' New queries don ' t get written to msysobjects until
' Access is closed. Therefore we ' ll just check the
' Start ' The name is ' ~TMPCLP ' ...
If InStr (1, Qdef.name, "~TMPCLP") = 1 Then
strObjectName = ""
strObjectName = InputBox ("A deleted QUERY has been found." & _
VbCrLf & VbCrLf & _
"To undelete this object, enter a new name:", _
"Access undelete Query", strObjectName)
If Len (strObjectName) > 0 Then
If fnundeletequery (CurrentDb, Qdef.name, strObjectName) Then
' We ' ll rename the deleted object since we ' ve made a
' Copy and won ' t is needing to re-undelete it.
' (to break the condition "~TMPCLP" in future ...)
Qdef.name = "~TMPCLQ" & right$ (Qdef.name, Len (qdef.name)-7)
End If
End If
Intnumdeleteditemsfound = Intnumdeleteditemsfound + 1
End If
Next Qdef
If intnumdeleteditemsfound = 0 Then
MsgBox "Unable to find no deleted tables/queries to undelete!"
End If
Set dbsdatabase = Nothing
Fnundeleteobjects = True
Exitfunction:
Exit Function
ErrorHandler:
MsgBox "Error occured in Fnundeleteobjects ()-" & _
Err.Description & "(& CStr (err.number) &)"
GoTo exitfunction
End Function
Private Function fnundeletetable (dbdatabase as DAO. Database, _
Strdeletedtablename as String, _
Strnewtablename as String)
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
Dim TdeF as DAO. TableDef
Set tdef = dbdatabase.tabledefs (strdeletedtablename)
' Remove the Deleted Flag ...
Tdef.attributes = Tdef.attributes and not Dbhiddenobject
' Rename the deleted object to the original or new name ...
Tdef.name = Strnewtablename
DbDatabase.TableDefs.Refresh
Application.refreshdatabasewindow
Set tdef = Nothing
End Function
Private Function fnundeletequery (dbdatabase as DAO. Database, _
Strdeletedqueryname as String, _
Strnewqueryname as String)
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
' We can ' t just remove the Deleted flag on queries
' (' Attributes ' is not a exposed property)
' So instead we create a new query with the SQL ...
' Note:can ' t use Docmd.copyobject as it copies the Dbhiddenobject attribute!
If fncopyquery (Dbdatabase, Strdeletedqueryname, Strnewqueryname) Then
Fnundeletequery = True
Application.refreshdatabasewindow
End If
End Function
Private Function fncopyquery (dbdatabase as DAO. Database, _
Strsourcename as String, _
Strdestinationname as String)
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
On Error GoTo ErrorHandler:
Dim Qdefold as DAO. QueryDef
Dim qdefnew as DAO. QueryDef
Dim Field as DAO. Field
Set qdefold = dbdatabase.querydefs (strsourcename)
Set qdefnew = Dbdatabase.createquerydef (Strdestinationname, Qdefold.sql)
' Copy Root Query Properties ...
Fncopylvproperties qdefnew, Qdefold.properties, qdefnew.properties
For each Field in Qdefold.fields
' Copy each fields individual properties ...
Fncopylvproperties Qdefnew.fields (Field.name), _
Field.properties, _
Qdefnew.fields (Field.name). Properties
Next Field
DbDatabase.QueryDefs.Refresh
Fncopyquery = True
Exitfunction:
Set qdefnew = Nothing
Set Qdefold = Nothing
Exit Function
ErrorHandler:
MsgBox "Error re-creating Query" & Strdestinationname & "':" & VbCrLf & _
Err.Description & "(& CStr (err.number) &)"
GoTo exitfunction
End Function
Private Function propexists (Props as DAO. Properties, strPropName as String) as Boolean
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
' If properties fail to be created, we ll just ignore the errors
On Error Resume Next
Dim Prop as DAO. Property
For each Prop in Props
If Prop.name = strPropName Then
Propexists = True
Exit Function ' short circuit
End If
Next Prop
Propexists = False
End Function
Private Sub fncopylvproperties (objobject as Object, Oldprops as DAO. Properties, Newprops as DAO. Properties)
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
' If properties fail to be created, we ll just ignore the errors
On Error Resume Next
Dim Prop as DAO. Property
Dim Newprop as DAO. Property
For each Prop in Oldprops
If not Propexists (Newprops, Prop.name) Then
If IsNumeric (Prop.value) Then
Newprops.append Objobject.createproperty (Prop.name, Prop.type, CLng (Prop.value))
Else
Newprops.append Objobject.createproperty (Prop.name, Prop.type, Prop.value)
End If
Else
With Newprops (Prop.name)
. Type = Prop.type
. Value = Prop.value
End With
End If
Next Prop
End Sub
Private Function Fngetdeletedtablenamebyprop (Strrealtablename As String) as String
' Module (c) Phillips Wayne (http://www.everythingaccess.com)
' Written 18/04/2005
' If An error occurs here, just ignore (user'll override the blank name)
On Error Resume Next
Dim I as Long
Dim Strnamemap as String
' Look up ' the Unicode translation Namemap property to try to guess the
' Original table name ... (Access 2000+ Only-and doesn ' t always exist?!)
Strnamemap = Currentdb.tabledefs (strrealtablename). Properties ("Namemap")
Strnamemap = Mid (strnamemap) ' Offset of ' table name ...
' Find the null terminator ...
i = 1
If Len (Strnamemap) > 0 Then
while (I < Len (Strnamemap)) and (ASC (Mid strnamemap, i)) <> 0)
i = i + 1
Wend
End If
Fngetdeletedtablenamebyprop = Left (Strnamemap, i-1)
End Function
