ms-access - 损坏的 Access 2010 数据库 - 恢复查询定义

Question

我有一个 Access DB，它是一个本地开发副本，而不是生产副本，并且在(具有讽刺意味的)压缩和修复期间损坏了大量数据。不幸的是，我在这方面做了很多工作，而且我确实有一个备用计划，但它失败了。

当我启动它时，它给了我无用的错误消息，例如:
'Id' is not an index in this table. 'ParentIdName' is not an index in this table.
我想这些索引是数据库中隐藏的 MSys* 表的一部分。我设法阅读了这些内容，它们大部分都是空的，而我的旧版健康备份在这些表中有大量数据。

首先，我尝试使用 VBA 和 ADO 连接(事先制作了一个副本)将健康的表恢复到被破坏的表中，我收到很多错误，说我没有表的写权限。

下一个仍在进行中的尝试是通过相同的 ADO 连接(也尝试过 ADOX 和 DAO)恢复查询定义(我更改的 90% 以上)。

最成功的尝试是 ADO:

Sub DebugPrintQueryDefsADO()

    Dim dmgDB As DAO.Database
    Dim dmgQD As DAO.QueryDef

    Set dmgDB = DBEngine.OpenDatabase("C:\Database.accdb", , True)

    For Each dmgQD In dmgDB.QueryDefs

        If Left(dmgQD.Name, 1) <> "~" Then ' ~ Query defs seem to be the form views'
            Debug.Print "---------------------------------"
            Debug.Print dmgQD.Name
            Debug.Print "---------------------------------"
            Debug.Print dmgQD.SQL
        End If

    Next dmgQD

    Set dmgQD = Nothing
    Set dmgDB = Nothing    

End Sub

这确实有效!只要查询定义评估首先运行良好。在我的情况下，对于大多数 QD 来说这不是真的，因为表是在需要时导入然后删除的。这意味着无法找到 QD 中定义的字段，因为表不存在。然后 Access 给我一个错误:
3258: The SQL Statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.
回家 Access ，你喝醉了。

下一个任务是弄清楚如何不评估 SQL 语句(我只需要 SQL 语句，而不是结果)。我发现有一个属性: Querydef.Prepare在此 Access 2007 文档中: http://msdn.microsoft.com/en-us/library/office/bb208511(v=office.12).aspx

根据文档(Access 2007，我有 2010)，我应该设置 Querydef.Prepare = dbQUnprepare .但这不起作用..

这么长的问题这么短..

有没有人知道修复损坏的数据库的方法？

或者..

有谁知道如何从查询定义中检索我的 SQL 语句而无需评估它们？

谢谢!

Answer 1

您可以尝试的一种选择是

Application.SaveAsText acQuery, "QueryName", "C:\path\QueryName.txt"

至少在某些情况下，它会在文件的开头转储 SQL 字符串(有一些任意的中断和转义字符，只是为了让事情变得有趣)。例如:

dbMemo "SQL" ="SELECT YEAR(Assigned.[Date]) AS Yr, MONTH(Assigned.[Date]) AS Mo, SUM(Assigned.["
    "Hours Worked]) AS Hrs\015\012FROM Assigned INNER JOIN Projects ON Projects.[Proj"
    "ect Name] = Assigned.[Project Name]\015\012WHERE Projects.Billable\015\012GROUP "
    "BY YEAR(Assigned.[Date]), MONTH(Assigned.[Date]);\015\012"
dbMemo "Connect" =""
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbBoolean "OrderByOn" ="0"
dbByte "Orientation" ="0"
dbByte "DefaultView" ="2"
dbBinary "GUID" = Begin
    0x1304ebdf78bef2459211d478954077cd
End
dbBoolean "FilterOnLoad" ="0"
dbBoolean "OrderByOnLoad" ="-1"
dbLongBinary "DOL" = Begin
    0x0acc0e5500000000534cc617867b4d43b98002a1b002c8eb00000000c732603a ,
    0xa958e4400000000000000000410073007300690067006e006500640000000000 ,
    0x00006b84dfe37aec2248a1ccfe3e157361df0000000040b3affda358e4400000 ,
    0x000000000000500072006f006a006500630074007300000000000000d5b092f5 ,
    0x1c13394884636d668e066e66070000001304ebdf78bef2459211d478954077cd ,
    0x59007200000000000000efa363bc29cbfc478a958e7b935d03da070000001304 ,
    0xebdf78bef2459211d478954077cd4d006f00000000000000b1174c1abe1d3d4c ,
    0xb9a1a7e5915a2e47070000001304ebdf78bef2459211d478954077cd48007200 ,
    0x7300000000000000b2e4aa9aab3a9e479c47b2772754fce107000000534cc617 ,
    0x867b4d43b98002a1b002c8eb4400610074006500000000000000404c3a80ef29 ,
    0xa4498e153e354319e84c07000000534cc617867b4d43b98002a1b002c8eb4800 ,
    0x6f00750072007300200057006f0072006b0065006400000000000000a3a95acb ,
    0xe7ec994bab8df870ce5b3d98070000006b84dfe37aec2248a1ccfe3e157361df ,
    0x500072006f006a0065006300740020004e0061006d006500000000000000f5cb ,
    0xefb11bcf5e4da4b2806eed09a70207000000534cc617867b4d43b98002a1b002 ,
    0xc8eb500072006f006a0065006300740020004e0061006d006500000000000000 ,
    0x453445bf7e67b54ead2c9317ca29d906070000006b84dfe37aec2248a1ccfe3e ,
    0x157361df420069006c006c00610062006c006500000000000000000000000000 ,
    0x000000000000000000000c000000050000000000000000000000000000000000
End
dbByte "PublishToWeb" ="1"
Begin
    Begin
        dbText "Name" ="Yr"
        dbLong "AggregateType" ="-1"
        dbBinary "GUID" = Begin
            0xd5b092f51c13394884636d668e066e66
        End
    End
    Begin
        dbText "Name" ="Mo"
        dbLong "AggregateType" ="-1"
        dbBinary "GUID" = Begin
            0xefa363bc29cbfc478a958e7b935d03da
        End
    End
    Begin
        dbText "Name" ="Hrs"
        dbLong "AggregateType" ="-1"
        dbBinary "GUID" = Begin
            0xb1174c1abe1d3d4cb9a1a7e5915a2e47
        End
    End
End