Problem with JRO CompactDatabase method

[jbradley]

Using the following code, copied almost exactly from the MSDN libraby, I'm trying to compact an Access 97 database after adding a bunch or records:

Sub JROCompactDatabase()

   Dim je As New JRO.JetEngine

   ' Make sure there isn't already a file with the
   ' name of the compacted database.
   If Dir(&quot;NewMyData.mdb&quot;) <> &quot;&quot; Then Kill &quot;NewMyData.mdb&quot;

   ' Compact the database
   je.CompactDatabase &quot;Data Source=MyData.mdb;&quot;, _
      &quot;Data Source=NewMyData.mdb;&quot;

   ' Delete the original database
   Kill &quot;MyData.mdb&quot;

   ' Rename the file back to the original name
   Name &quot;NewMyData.mdb&quot; As &quot;MyData.mdb&quot;

End Sub

It acts like it's working but if I try to open the database afterwards I get an Unrecognized database format 'MyData.mdb' error and the database is shot.

What am I missing in this?

 

[MarkSweetland]

Need to specify the provider like such:

je.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datafiles\myData.mdb", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\datafiles\NewMyData.mdb;Jet OLEDB:Engine Type=5"


Mark

 

[jbradley]

Thanks, Mark! That did the trick, although I had to use Type=4 with my Access 97 database.