SendKeys/Compact inconsistencies

[AlanS]

I'm not sure if this is a Forms issue or a VBA issue, so I'm cross-posting - please pardon the duplication. I have a form with a command button named cmdExit. The form's module includes the two short Subs below.

Case 1: When I close the form using the "X" button at the upper right, Form_Close is executed and the database is automatically repaired and compacted. So far so good.

Case 2: When I click the cmdExit button, cmdExit_Click is executed and the application is terminated. The problem is that even though part of that termination is closing the form, the repair and compaction do not occur.

If I put a MsgBox function at the beginning of Form_Close, it is displayed in both Case 1 and Case 2, but the repair and compaction still only happen in Case 1. I've spent a day trying all sorts of work-arounds (such as copying the contents of Form_Close to the beginning of cmdExit_Click, calling Form_Close at the beginning of cmdExit_Click, changing the SendKeys [wait] argument from False to True, etc., etc.), but nothing works. I've also noticed that if I add code to the end of Form_Close, the existing code stops working in all cases. All this is especially vexing because (1) when I first deployed this application about two weeks ago, repair and compaction worked fine in both Case 1 and Case 2; and (2) this application's sole purpose is to repair and compact other databases, which it does admirably.

Any ideas on what's going on would be greatly appreciated.

Private Sub cmdExit_Click()
DoCmd.Quit 'exit application
End Sub

Private Sub Form_Close()
DoCmd.SetWarnings False 'turn off warning dialogs
SendKeys "%TDR", False 'repair current database
SendKeys "%TDC", False 'compact current database
End Sub

 

[joeyreyma]

i can see no error in your code since you also stated that it all worked fine previously. however, i would like to suggest changing the SENDKEYS line to:

Docmd.RunCommand acCmdRepairDatabase 'repair database
Docmd.RunCommand acCmdCompactDatabase 'compact database

this is more natural than using sendkeys to run a built-in function of Access, especially, accessing menus.

and one last thing about the cmdExit_Click() sub, run a form close line before quiting the program making sure you close the form properly and that all necessary codes run.

 

[KDg]

Hi,

is there a way of getting the run command to run on the current DB ( i'm using 97) ?

cheers

Drew

ps i came across this page which may be of some use http://home.houston.rr.com/jeffkohn/compactcurrent.htm


[This message has been edited by KDg (edited 08-07-2001).]

 

[Rich]

Not sure if this is the reason but I use SendKeys "%(TDC)", False.
HTH

 

[joeyreyma]

if you use SendKeys to access menu items then you will have problem if the menu bar/toolbar is hidden because the line will not trigger the action of the menu. it's better to use DoMenuItem, if that's the case. however, RunCommand already replaces DoMenuItem. i find the new method easy and straigtforward.

 

[AlanS]

Thanks to everyone who responded for your suggestions. Unfortunately, I haven't been able to make any of them work. Changing the SendKeys [wait] argument to True makes the "Compact From" dialog box appear, and I need this operation to proceed without user input. The same thing happens when one uses DoCmd.RunCommand acCmdCompactDatabase. I also haven't been able to make this work with static or global flag variables to control the flow. If anyone has a working example of an Access application that will compact itself and close itself, all in the same operation without any user input, I'd love to see how it's done.

 

[Peter D]

You can use the TSI SOON utility to close a database, compact it, and then open it again:
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Hope this helps,

--
Peter De Baets
Peter's Software - MS Access Tools - http://www.peterssoftware.com