How to restore deleted records under access; how to restore deleted tables, forms, and other objects

Problem:

How to restore deleted records, how to restore deleted tables, forms, and other objects
1. I deleted some records using Delete from table. Now I find that the records are deleted by mistake. How can I restore them?
2. I manually deleted a table or deleted it using drop table. Now I found that it was deleted by mistake. How can I restore it?
3. I manually deleted a form. How can I restore it?
4. I deleted the record, but the database volume was not reduced. Can I retrieve the record?

Answer:

1. deleted records cannot be recovered. Access is not FoxPro. mdb is not in DBF format. There is no concept of logical deletion or physical deletion. Once deleted, it cannot be recovered.
2. It cannot be recovered, but you can check whether there is any "~ "Indicates the table starting with a symbol. Changing the table name may retrieve the expected table.
3. It cannot be recovered, but you can check whether there are any hidden objects in the system. Sometimes, when an object is deleted, the system does not directly delete it, but hides it after changing the object name.
4. The database size is indeed not small. After you compress and fix the database, the size will become smaller. That's because your data is not deleted in binary and is still stored in a certain sector of the disk. However, Microsoft does not provide reference materials for the MDB format binary Organization (nor does Microsoft provide it, other third-party companies do not have the right to decompile the MDB format ). So far, I have not seen any reference materials in mainland China. Therefore, the data you have deleted so far cannot be recovered. However, you can try to use the disk recovery software to find the data recovery method, but this method is not covered in this article.

Suggestion: when creating a database structure, you can add an isdel field to each table. When deleting a record, you do not use the delete from statement, but use the statement such as update table set isdel = true, then, the isdel = true record is not displayed on the interface.CopyCode The Code is as follows: theoretically, it is acceptable if it is not compressed. Try this code. Added to the access module
Restore the worksheet (not deleted)

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 &_
"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' bubutes 'flag is 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 of the name is '~ Tmpclp '...
If instr (1, qdef. Name ,"~ Tmpclp ") = 1 then
strobjectname =" "
strobjectname = inputbox (" A deleted query has been found. "& _
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
'Copy and won' t be needing to re-undelete it.
'(To break the condition "~ Tmpclp "in future ...)
Qdef. Name = "~ Tmpcscsi "& 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 any 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) 2005 Wayne Phillips (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) 2005 Wayne Phillips (http://www.everythingaccess.com)
'Written 18/04/2005
'We can't just remove the deleted flag on queries
'('Bubuckets' is not an 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) 2005 Wayne Phillips (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) 2005 Wayne Phillips (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) 2005 Wayne Phillips (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) 2005 Wayne Phillips (http://www.everythingaccess.com)
'Written 18/04/2005
'If an error occurs here, just ignore (user will 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
'Original table name... (Access 2000 + only-and doesn' t always exist ?!)

Strnamemap = currentdb. tabledefs (strrealtablename). properties ("namemap ")
Strnamemap = mid (strnamemap, 23) 'offset of the 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