4

I have an Access DB, it's a local development copy, not production, and corrupted during (ironically) compact and repair, a large amount of data. Unfortunately I did a lot of work on it and I do have a backup plan but it failed me..

When I start it up it gives me useless error messages like:
'Id' is not an index in this table.
'ParentIdName' is not an index in this table.

I suppose these indexes are part of the hidden MSys* tables in the DB. I managed to read those and they're mostly empty while my older healthy backup has a lot of data in these tables.

First I tried to restore the healthy tables into the destroyed ones using VBA and an ADO connection (made a copy beforehand), I get a lot of errors saying I don't have write permissions on the tables.

Next attempt, still ongoing, is to recover the query definitions (90%+ of my changes) through the same ADO connection (tried ADOX and DAO too).

The most successful attempt was 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

This actually works! As long as the Query definition evaluation runs fine first. Which in my case cannot be true for most QD's since the tables are imported when required and then deleted. This means that the fields defined in the QD's cannot be found because the tables aren't there. Then Access presents me an error:

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.

Go home Access, you're drunk.

Next quest was to figure out how to not evaluate the SQL statement (I only need the SQL statement, not the result). I found that there is a property: Querydef.Prepare in this Access 2007 doc: http://msdn.microsoft.com/en-us/library/office/bb208511(v=office.12).aspx

According to the documentation (of Access 2007, I have 2010), I should set Querydef.Prepare = dbQUnprepare. But that does not work..

So long problem short..

  • Is anyone aware of a way to fix the corrupted DB?

OR..

  • Does anyone know how to retrieve my SQL statements from the query definitions without the need to evaluate them?

Thanks!

Chris
  • 668
  • 7
  • 18

2 Answers2

3

One option you could try would be

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

At least in some cases it dumps the SQL string at the beginning of the file (with some arbitrary breaks and escaped characters, just to keep things interesting). For example:

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
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks Gord, I'm trying this method as we speak to retrieve the other 10% of changes (VBA modules). I followed these instructions: http://stackoverflow.com/questions/187506/how-do-you-use-version-control-with-access-development But it doesn't work because it basically opens the database (just like a user would) and thus struggles with the same errors at startup, they're just not popping up. It says: `The expression you entered refers to an object that is closed or doesn't exist.` – Chris Feb 05 '14 at 10:51
  • I tried to dump the SQL with `CurrentDb.QueryDefs!QueryName.SQL` but even this triggers the error. `Application.SaveAsText` really does the trick. – Olivier Jacot-Descombes Aug 07 '17 at 13:03
0

You can use Access Fix from AccessFix.com, if you have a copy of the database, which just got corrupted, and you didn't try anything on it that may have changed the data. Like, if you tried to compact and repair, your objects are gone after that. I use it at work and it fixes dbs for me in most cases. I would also try http://allenbrowne.com/tips.html, can't quite remember, but he had a set of steps there that you can use to recover the entire database from text. I think he explained it in his tips and tricks book, with Dough Steele, also

Carlos Flores
  • 21
  • 3