title: Corrupted Access 2010 database - recover query definitions
body: <p>I have an Access DB, it's a local development copy, not production, and corrupted during (ironically) compact and <em>repair</em>, 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..</p>
  
  <p>When I start it up it gives me useless error messages like:<br>
   <code>'Id' is not an index in this table.</code><br>
   <code>'ParentIdName' is not an index in this table.</code></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Next attempt, still ongoing, is to recover the query definitions (90%+ of my changes) through the same ADO connection (tried ADOX and DAO too). </p>
  
  <p>The most successful attempt was ADO:</p>
  
  <pre><code>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) &lt;&gt; "~" 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
  </code></pre>
  
  <p>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:</p>
  
  <p><code>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.</code> </p>
  
  <p>Go home Access, you're drunk.</p>
  
  <p>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: <code>Querydef.Prepare</code> in this Access 2007 doc: <a href="http://msdn.microsoft.com/en-us/library/office/bb208511(v=office.12).aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/office/bb208511(v=office.12).aspx</a></p>
  
  <p>According to the documentation (of Access 2007, I have 2010), I should set 
  <code>Querydef.Prepare = dbQUnprepare</code>. But that does not work..</p>
  
  <p>So long problem short..  </p>
  
  <ul>
  <li>Is anyone aware of a way to fix the corrupted DB?  </li>
  </ul>
  
  <p>OR..  </p>
  
  <ul>
  <li>Does anyone know how to retrieve my SQL statements from the query definitions without the need to evaluate them?</li>
  </ul>
  
  <p>Thanks!</p>