COMPACTING DATABASE FROM MAIN MENU

[Angelique]

I got some cool code to allow the user to compact the database from a command button on the Main Menu. It worked fine until recently.

I have included the code which looks at a table to see what the last compact date was, and if over 30 days proceeds to compact the database by closing it, compacting and re-opening it. It did allow the user to manually run it by skipping the checking of the Compact table date!

Sub CheckCompactDate()

Dim dbs As Database
Dim rst As Recordset

' Get default Workspace.

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tlkCompactDate&quot

With rst

' Loop through recordset
Do Until .EOF
If Date - !Compactdate > 30 Then
.Edit ' open the file for editing
!Compactdate = Date
.Update 'update the record
'call the compact db function
Call Compactdb
Else
'do nothing
End If


.MoveNext 'go to the next record
Loop


.Close ' close recordset
End With


End Sub

Function Compactdb()
On Error GoTo Compactdb_Err

SendKeys "%(TDC)", False


Compactdb_Exit:
Exit Function

Compactdb_Err:
MsgBox Error$
Resume Compactdb_Exit

End Function

All I get now is a beep from the computer, the date inserted into the table and nothing else.

I should mention that since writing the code, I removed the Menu and Toolbar from the form and maximized through a docmd.maximise. Any connections?

Any advice is better than none!


Angelique

 

[Lightning]

Angelique

The Sendkeys statement does exactly what the name implies - it sends the keystrokes listed to the computer. It does EXACTLY the same thing as if you had physically pressed the keys on the keyboard. So, the Sendkeys statement will not work if the menu item does not exist. Since you have removed the menu bar, the Sendkeys command cannot run.

You have a number of options.

1. Add the menu bar back into the form;

2. Add the Compact database menu item to a custom menu
and add this to the form;

3. Re-write the code so that it does not use the
SendKeys statement.

There may be (and probably are) other solutions that I can't think of.

HTH

Lightning

 

[Angelique]

Thanks Lightning - I will reinstate the menu but can I do it just before running the code? I suspect so! Will give it a go!

You guys are so quick in responding - again Thanks.


Angelique