Code for compacting multiple times in one procedure (reworded)

[janerussel]

This is the second time I have posted this, maybe I didn't make myself clear, so I'll post again.<br><br>I have a long procedure.&nbsp;&nbsp;I import some large tables from an accounting database, then run a series of make table queries and insert the results into a new table and finally export those results for distribution.<br><br>I need to compact the database four times, even though I delete as many tables as I can along the way.<br><br>I have found two suggestions on compacting the database in code at this forum and none of them have worked for my needs.&nbsp;&nbsp;Below, I will explain why, and hopefully someone out there will have a suggestion that will work.<br><br>1.&nbsp;&nbsp;Use SendKeys - This works the first time it is called, but just skips over it everytime it is called thereafter.&nbsp;&nbsp;I wonder why?<br><br>Function compact_database()<br><br>&nbsp;&nbsp;&nbsp;&nbsp;SendKeys (&quot;%(W1)&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;SendKeys (&quot;%(TDC)&quot<br><br>End Function<br><br>2.&nbsp;&nbsp;This code doesn't work, keep getting run time error 70, message, permission denied. <br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'CompactDB<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Dir(&quot;d:\Access\Budget\Budgetbak.mdb&quot &lt;&gt; &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Kill &quot;d:\Access\Budget\Budgetbak.mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DBEngine.CompactDatabase <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;d:\Access\Budget\Budgetbak.mdb&quot;, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;d:\Access\Budget\Budgetbak.mdb&quot;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.Hourglass False<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;Process Complete&quot;<br><br>

 

[Chopper]

Unfortunately, I am under the impression that what you are setting out to do is not going to work, and here is why.<br><br>You cannot use DBEngine.CompactDatabase to compact an open database, nor can you compact (a closed) database to the same directory with the same name.&nbsp;&nbsp;Also, in your above code, you are trying to delete (kill) the database you have open, thus resulting in your error.&nbsp;&nbsp;If that part did not error out, you would still error out on the compacting part.<br><br>In addition, if you try to compact the database in the middle of a subprocedure, using the sendkeys, you will not be able to complete any of the lines of code that follow it.<br><br>I am curious as to why you desire to compact so many times.&nbsp;&nbsp;Are you that close to the 1 gigabyte database limit? (access 97 has a 1 gig size limit on a database, access 2000 has a 2 gig limit)&nbsp;&nbsp;Have you considered just compacting once when the entire procedure finishes?&nbsp;&nbsp;Realize, of course, that when you compact, it will be very hard, if not impossible, to pick up where you left off in the application. <p>-Chopper<br><a href=mailto: > </a><br><a href= > </a><br>

 

[harryrich]

The program I run using sendkeys works.&nbsp;&nbsp;Maybe you need to start with a few escapes to make sure any previously opened menus are closed up.<br><br>In my shop I have a job that takes a database past a gigabyte if I don't compact it, but its in a back end database.&nbsp;&nbsp;To deal with this I run a batch file which alternately runs MSACCESS on a front end database which runs starup code which operates on the back end depending on the CMD paramter.&nbsp;&nbsp;In between, it call MSACCES to compress the back end database.<br><br>It's some work, but you might try it if you can't make sendkeys fly.

 

[janerussel]

How do I code escapes to make sure any previously opened menus are closed up???? <br><br>