Compacting Access2000 Databases using JRO. 2

[Phooey]

I have an Access2000 database developed using ADO and I'm trying to compact the database using code.&nbsp;&nbsp;The database has been split and it's the Back end which I'm trying to compact on exit.&nbsp;&nbsp;So far every piece of code I've used has resulted in: <font color=red>You currently have the database open</font>.(Longer error message but you get the idea).<br><br>No forms remain open and as far as I know, there is nothing using the back end database.<br><br>I've succeeded in this using a DAO database in Access97, but sadly the same code won't work in this instance, even with the relevant references.<br><br>Here is the code that I have usedit fails during compact)<br><font color=blue>Function CompactDB()<br><br>Dim jetengine As JRO.jetengine<br>Dim fs As Object<br>Dim strSourceConnect As String<br>Dim strDestConnect As String<br>Dim strSourceDB As String<br>Dim strDestDB As String<br><br>'Build connection strings for SourceConnection and<br>'DestConnection arguments.<br>strSourceDB = &quot;'W:\Sep_Reps\SEPData.mdb'&quot;<br>strDestDB = &quot;'W:\Sep_Reps\SEPOld.mdb'&quot;<br><br>strSourceConnect = &quot;Data Source=&quot; & strSourceDB<br>strDestConnect = &quot;Data Source=&quot; & strDestDB & &quot;;&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Jet OLEDB:Encrypt Database=False&quot;<br><br>Set jetengine = New JRO.jetengine<br>Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot<br><br>'Compact the database specified by the strSourceDB<br>'to the name and path specified by strDestDB<br>If fs.FileExists(&quot;W:\Sep_Reps\SEPOld.mdb&quot Then<br>&nbsp;&nbsp;&nbsp;&nbsp;fs.DeleteFile (&quot;W:\Sep_Reps\SEPOld.mdb&quot<br>End If<br>jetengine.CompactDatabase strSourceConnect, strDestConnect<br><br>fs.copyfile &quot;W:\Sep_Reps\SEPOld.mdb&quot;, &quot;W:\Sep_Reps\SEPData.mdb&quot;, True<br><br>Set jetengine = Nothing<br>Set fs = Nothing<br><br>End Function<br></font><br><br>Any help would be greatly appreciated.

 

[DougP]

Your error says &quot;it's open&quot;.<br>Are you trying to compact the Backend &quot;_BE&quot; in the &quot;front end&quot;?<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Phooey]

Okay, I've sussed it now..!!&nbsp;&nbsp;Whereas Access97 would compact after a <font color=red>Docmd.close</font> command (so that no forms are open), Access2000 seems to be a little tighter and I finally succeeded after moving the code to the <font color=blue>On Unload</font> event of the last form to close.<br><br>Thanks anyway.

 

[Timc]

This was interesting if anyone is interested this is the code I use for compacting a database<br>Public Function CompactDatabase(strDatabaseName As String) As Boolean<br><br>&nbsp;&nbsp;&nbsp;&nbsp;On Error GoTo 0<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strPath As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strPath1 As String<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = vbHourglass<br><br>&nbsp;&nbsp;&nbsp;&nbsp;'Save Paths for Database<br>&nbsp;&nbsp;&nbsp;&nbsp;strPath = &quot;c:\inetpub\

http://wwwroot\chiroasp\&quot;

& strDatabaseName<br>&nbsp;&nbsp;&nbsp;&nbsp;strPath1 = &quot;c:\inetpub\

http://wwwroot\chiroasp\&quot;

& &quot;BackupOf&quot; & strDatabaseName<br>&nbsp;&nbsp;&nbsp;&nbsp;'Repair Database<br>&nbsp;&nbsp;'&nbsp;&nbsp;DBEngine.RepairDatabase strPath<br>&nbsp;&nbsp;&nbsp;&nbsp;'Get Size of File Before Compacting<br>&nbsp;&nbsp;&nbsp;&nbsp;strPathSize = GetFileSize(strPath)<br>&nbsp;&nbsp;&nbsp;&nbsp;'Kill the file if it exists<br>&nbsp;&nbsp;&nbsp;&nbsp;If Dir(strPath1) &lt;&gt; &quot;&quot; Then Kill strPath1<br>&nbsp;&nbsp;&nbsp;&nbsp;'Compact Database to New Name<br>&nbsp;&nbsp;&nbsp;&nbsp;DBEngine.CompactDatabase strPath, strPath1<br>&nbsp;&nbsp;&nbsp;&nbsp;''Kill the file if it exists<br>&nbsp;&nbsp;&nbsp;&nbsp;If Dir(strPath) &lt;&gt; &quot;&quot; Then Kill strPath<br>&nbsp;&nbsp;&nbsp;&nbsp;'Compact back to original Name<br>&nbsp;&nbsp;&nbsp;&nbsp;DBEngine.CompactDatabase strPath1, strPath<br>&nbsp;&nbsp;&nbsp;&nbsp;'Kill the file, no need to save it<br>&nbsp;&nbsp;&nbsp;&nbsp;If Dir(strPath1) &lt;&gt; &quot;&quot; Then Kill strPath1<br>&nbsp;&nbsp;&nbsp;&nbsp;'Get Size of File After Compacting<br>&nbsp;&nbsp;&nbsp;&nbsp;strPathSize2 = GetFileSize(strPath)<br>&nbsp;&nbsp;&nbsp;&nbsp;CompactDatabase = True<br>&nbsp;&nbsp;&nbsp;&nbsp;'Display the Summary<br>&nbsp;&nbsp;'&nbsp;&nbsp;MsgBox UCase(strDatabaseName) & &quot; compacted successfully.&quot; _<br>&nbsp;&nbsp;'&nbsp;&nbsp;& vbNewLine & vbNewLine & &quot;Size before compacting:&quot; & vbTab & strPathSize _<br>&nbsp;&nbsp;'&nbsp;&nbsp;& vbNewLine & &quot;Size after compacting:&quot; & vbTab & strPathSize2, vbInformation, &quot;Compact Successful&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Err_CompactDatabase:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case err<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox err & &quot;: &quot; & Error, vbCritical, &quot;CompactDatabase Error&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;End Select<br><br>Screen.MousePointer = vbNormal<br><br>End Function<br><br>

 

[Quehay]

Thanks to both of you guys for the code examples for compacting.&nbsp;&nbsp;How is the programmatic version different (in terms of results & best use)from just setting the &quot;Compact on close&quot; option?&nbsp;&nbsp;

 

[Phooey]

If using a split database, the compact on close option will only compact the front end.&nbsp;&nbsp;In most cases the front end will not contain any tables and so the size of the front end should not change, or any change in size will be minimal.