Office中国论坛/Access中国论坛

标题: [分享]恢复误删Table的数据 [打印本页]

作者: andymark 时间: 2006-5-18 23:13
标题: [分享]恢复误删Table的数据
自已辛苦输入的数据,不小心删除了,怎么办?

答案是肯定的,资料可以找回

前提是数据库没退出和压缩过。

[此贴子已经被作者于2006-5-18 16:19:02编辑过]

作者: fan0217 时间: 2006-5-18 23:21
我没有发现可以恢复数据啊!(没有压缩数据库),怎么回事呢??

作者: andymark 时间: 2006-5-18 23:23
你delete 一个TABLE看看

作者: fan0217 时间: 2006-5-18 23:37
我删除了一个表,但你没有替我找回来.

作者: WDLRCZT 时间: 2006-5-18 23:37
可以恢复，只是表的名称改了

作者: andymark 时间: 2006-5-18 23:40

以下是引用WDLRCZT在2006-5-18 15:37:00的发言：
可以恢复，只是表的名称改了

正确

[此贴子已经被作者于2006-5-18 15:57:39编辑过]

作者: fan0217 时间: 2006-5-18 23:46
我没有看源程序,只是测试了一下.

应该是还原成原来的表名或加上后缀(前缀),这样容易区别,如果有多张表被删除,你的示例就只能还原一张了.

作者: LucasLynn 时间: 2006-5-18 23:54
很有用的功能。加精奖励。

作者: sgrshh29 时间: 2006-5-19 00:15
好像还有点不太完满,请各位测试一下下面这种情况:同时删除二个以上的表,然后要求回复这些删除的表.

作者: andymark 时间: 2006-5-19 00:20

以下是引用sgrshh29在2006-5-18 16:15:00的发言：
好像还有点不太完满,请各位测试一下下面这种情况:同时删除二个以上的表,然后要求回复这些删除的表.

根据fan0217的建议重新修改了代码，可以一次全部找回

作者: fan0217 时间: 2006-5-19 02:48
我也来凑过热闹:

Function UndoTable() As Integer

'===============================================================================

'-函数名称:       UndoTable

'-功能描述:       恢复删除的数据库对象

'-输入参数说明:

'-返回参数说明:    Integer 恢复数据库的个数

'-使用语法示例:    MsgBox UndoTable()

'-参考:          Access中国论坛帖子

'-使用注意:

'-兼容性:          2000,XP,2003

'-作者:          fan0217@163.com

'-更新日期：       2006-03-17

'===============================================================================

'On Error GoTo Err_UndoTable

Dim conn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim strSQL As String

Dim strTemName As String

Dim i As Integer

Set conn = CurrentProject.Connection

strSQL = "SELECT * FROM  MSysObjects "

strSQL = strSQL & "WHERE(Left$([Name], 1) = '~')

And (Left$([Name], 4) <> 'Msys') And (MSysObjects.Type) = 1 "

rs.Open strSQL, conn, 1, 3

      Do While Not rs.EOF

         strTemName = Right(rs("Name"), Len(rs("Name")) - 4)

         strSQL =

"SELECT * INTO [Undo_" & strTemName & "] FROM [" &

rs("Name") & "];"

         'conn.Execute strSQL

         DoCmd.SetWarnings False

         DoCmd.RunSQL strSQL

         DoCmd.SetWarnings True

         i = i + 1

         rs.MoveNext

      Loop



UndoTable = i

rs.Close

Set rs = Nothing

Set conn = Nothing

Exit_UndoTable:

Exit Function

Err_UndoTable:

Set rs = Nothing

Set conn = Nothing

MsgBox Err.Description

Resume Exit_UndoTable

End Function

作者: wu8313 时间: 2006-5-19 04:06
很实用的东西，支持。

作者: 无忧 时间: 2006-5-19 16:48
精品，谢谢

作者: wangruhai 时间: 2006-5-19 18:53
11111

作者: wangruhai 时间: 2006-5-19 18:53
22222

作者: wangruhai 时间: 2006-5-19 18:54
33333下载个东西，要发三条垃圾信息。

作者: gxlishuxin 时间: 2006-5-20 05:13
我之前在某外文网站上摘录到的：

Undelete Tables and Queries
When you delete a table/query in Access, they don't actually get permanently deleted until Access is compacted using the Compact & Repair menu option.

Instead, Access flags the objects as 'deleted' - fortunately for us these flags can be reversed.  When the items get flagged as 'deleted' Access also renames them to ~TMPCLP##### (#=Number) or similar, so unfortunately the names of the items are lost.  In Jet 4 files (Access 2000+) table names are usually recovered (by using the Unicode NameMap translation property of the table).

The VBA code in this article shows how this can be achieved... (I've commented the code so you can follow it through if you want to see how its done).

Option Compare Database
Option Explicit

' VBA MODULE: Undelete tables and queries in Microsoft Access
' (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
' Written 18/04/2005
'
' REQUIREMENTS: VBA DAO Reference, Access 97/2000/2002(XP)/2003
'
' This module will allow you to undelete tables and queries
' after they have been deleted in Access/Jet.
'
' Please note that this will only work if you haven't run the
' 'Compact' or 'Compact And Repair' option from Access/DAO.
' If you have run the compact option, your tables/queries
' have been permananetly deleted.
'
' You may modify this code as you please,
' However you must leave the copyright notices in place.
' Thank you.
'
' USAGE: Just import this VBA module into your project
' and call FnUndeleteObjects()
'
' If any un-deletable objects are found, you will be prompted
' to choose names for the undeleted objects.
' Note: In Access 2000, table names are usually recovered too.

Public Function FnUndeleteObjects() As Boolean

'Module (c) 2005 Wayne Phillips (http://www.everythingaccess.com)
'Written 18/04/2005

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 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 & vbCrLf & _
                                 "To undelete this object, enter a new name:", _
                                 "Access Undelete Query", strObjectName)

         If Len(strObjectName) > 0 Then

作者: 宿命的风 时间: 2006-5-20 07:45
这个问题很早我也发过，没人注意，不过楼主的代码简洁了不少．．．谢谢分享！

相关连接:

http://www.office-cn.net/forum.php?mod=viewthread&tid=28585&replyID=&skin=1

http://www.accfans.net/dispbbs.asp?BoardID=22&ID=8370&replyID=52437&skin=1

寻欢也写了个工具，不过据说在２００３下测试失败．

作者: tmtony 时间: 2006-5-20 10:09
我看过,最早还是国外的文章.但不同的方法有不同的精妙,但对理解系统表及Access的原理都有帮助.

作者: huanghai 时间: 2006-5-30 16:43
andymark版主发现新大陆

作者: asbad 时间: 2006-6-16 18:35
我回我回我回回回

作者: asbad 时间: 2006-6-16 18:35
我再回我再回我再回回回

作者: asbad 时间: 2006-6-16 18:36
就是为了看一眼,这么没脸没皮的回,丢人啊

作者: qlm 时间: 2006-6-16 20:49
谢谢

作者: QIUYIMIN 时间: 2006-7-26 18:58
Dear fan0217:

我用该函数尝试恢复被删除的表，发现删除表以后如果退出ACCESS后，再进ACCESS就不能恢复了，请问有什么办法吗？谢谢！

作者: fan0217 时间: 2006-7-26 22:02

以下是引用QIUYIMIN在2006-7-26 10:58:00的发言：

Dear fan0217:

我用该函数尝试恢复被删除的表，发现删除表以后如果退出ACCESS后，再进ACCESS就不能恢复了，请问有什么办法吗？谢谢！

前提是数据库没退出和压缩过。

作者: sunney 时间: 2006-7-26 22:49
好

作者: QIUYIMIN 时间: 2006-7-27 08:15

以下是引用fan0217在2006-7-26 14:02:00的发言：

前提是数据库没退出和压缩过。

谢谢!

作者: ElfMouz 时间: 2006-7-27 20:06
感谢分享

作者: zyz218 时间: 2006-7-27 21:21
db.TableDefs(i).Name

这个表在那？？？

作者: ak47m16 时间: 2006-9-16 06:40
标题: 我也遇到着问题
我也遇到着问题我也遇到着问题

作者: nfwjyp 时间: 2006-10-31 21:09
我也遇到这个问题了，能解决吗？

作者: 老眼夹 时间: 2006-11-2 03:42
能否将代码注释一下,以便新学者能好理解!

Function UndoTable()

  Dim db As DAO.Database, strTablename As String
  Dim i As Integer, StrSqlString As String
  Dim strTemName As String
  Set db = CurrentDb()

  For i = 0 To db.TableDefs.Count - 1

If Left(db.TableDefs(i).Name, 4) = "~tmp" Then
   strTablename = db.TableDefs(i).Name
   strTemName = Right(strTablename, Len(strTablename) - 4)
   StrSqlString = "SELECT DISTINCTROW [" & strTablename & _
      "].* INTO [" & strTemName & "] FROM [" & strTablename & "];"
   DoCmd.SetWarnings False
   DoCmd.RunSQL StrSqlString
   DoCmd.SetWarnings True
   MsgBox strTemName & " Table 已恢复"
End If
  Next i

Exit_Undo:
   Set db = Nothing
   Exit Function
Err_Undo:
   MsgBox Err.Description
   Resume Exit_Undo

End Function

作者: illusion 时间: 2006-11-8 07:58
真的很惨

作者: 天下有我师 时间: 2006-12-29 18:52
这是什么原因，版主帮看，在线等

Option Compare Database

Private Sub Command0_Click()
On Error GoTo err:
Select Case Me.Frame1.Value
Case 1 '表被删除
      If FnUndeleteObjects() = False Then
         Call FnUndeleteObjects
      End If
Case 2 '表被改名
      If IsNull(Me.oldname) = True Then
         MsgBox "必须输入原来的表名!(未被更改过的)", vbCritical, "错误"
         Me.oldname.SetFocus
      Else
         Dim str_old As String
         Dim str_del As String
         str_old = Me.oldname
         str_del = "~TMPCLP" & str_old
         DoCmd.Rename str_old, acTable, str_del
      End If
End Select
Exit Sub
err:
MsgBox err.Description & "您可能没有正确选择表丢失的原因"
Resume Next
End Sub

Private Sub Frame1_AfterUpdate() '
Select Case Me.Frame1.Value
Case 1
      Me.oldname.Visible = False
Case 2
      Me.oldname.Visible = True
End Select
End Sub

作者: panblackbean 时间: 2008-1-27 14:32
关闭了不就恢复了以上方法使用欠佳只有对深刻认识到这个问题的人有帮助

谁有accessfix软件有注册码的共享一下破解版的

作者: ABCaccess 时间: 2008-6-1 16:38
谢谢你与大众分享

作者: liaoliao 时间: 2008-7-15 11:32
有用[:45] [:45]

作者: itbill 时间: 2009-8-17 17:23
www.office-cn.net

作者: hxczyh 时间: 2009-9-16 09:49
kkkkkkkkkkkkkkkkkkk

作者: chaojianan 时间: 2009-10-19 21:14
谢谢分享

作者: liaoqiang234 时间: 2010-1-2 21:30

作者: 付谦 时间: 2010-2-4 09:06
学习

作者: Y9X 时间: 2013-7-23 17:59
我找了很久了

作者: shixm_1 时间: 2017-5-2 00:05
下载，学习了

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)