Echo On- Echo Off

[LeonS]

Hello! This is my first thread!! The question is (I believe) simple. How do I get the Eco On/ Off to work in Access 2007. I have it working in one instance. But I am trying to add Echo Off in a Sendkeys action. I am using Sendkeys to carry out a Compact and Repair. I tend to do this from time to time to help keep the db in order.

[burrina]

You can try this and see if it works for you. for 2007.

Sendkeys "%(FMC)", False

Here is code for doing what you described.

O

ption Compare Database
Option Explicit

'------------------------------------------------------------------------------
'
' This module will compact an external database OR the current database.
' If the current database is being compacted, it will close the database,
' create a batch file, and reopen.
'
' The inspiration behind this method comes from Bob Larson and the way his
' "Front End Update" tool works.
'
' Required References:
' 1. Microsoft Scripting Runtime
'
' To compact a database add the following to a button or other control:
'
' Call CompactRepair(sSourceFile)
' where 'sSourceFile' is the full path to the
' database you want to compact.
'
' I highly recommend creating a backup prior to running ANY compact/repair
' operation, regardless of which various method you are using.
'
' In the startup form's Form_Load event or your startup routine be sure to
' add the following:
' Application.SetOption "Auto Compact", False
' This way the current DB won't compact every time it closes.
'
' I think I have trapped the error for the Back End being in use. If there
' are other errors that need to be trapped, please let me know so I can
' add them to my code.
'
'------------------------------------------------------------------------------

Dim FSO As FileSystemObject

Dim sDestFile As String
Dim sExt As String
Dim sFileName As String
Dim sPrompt As String
Dim sSourcePath As String
Dim sTitle As String

Function CompactRepair(ByVal sSourceFile As String)
On Error GoTo Error_Handler

If sSourceFile <> Application.CurrentDb.Name Then

'Compact the back end
Set FSO = New FileSystemObject
sFileName = FSO.GetBaseName(sSourceFile)
sExt = "." & FSO.GetExtensionName(sSourceFile)
sSourcePath = FSO.GetParentFolderName(sSourceFile) & "\"

'Delete the previous Temp file if it exists.
If Dir(sSourcePath & sFileName & "_Temp" & sExt) <> "" Then
Kill sSourcePath & sFileName & "_Temp" & sExt
End If

'Compact the Back-End database to a temp file.
DBEngine.CompactDatabase sSourceFile, sSourcePath & sFileName & "_Temp" & sExt

'Delete the previous backup file if it exists.
If Dir(sSourcePath & sFileName & ".bak") <> "" Then
Kill sSourcePath & sFileName & ".bak"
End If

'Rename the current database as backup and rename the temp file to
'the original file name.
Name sSourceFile As sSourcePath & sFileName & ".bak"
Name sSourcePath & sFileName & "_Temp" & sExt As sSourceFile
Kill sSourcePath & sFileName & ".bak"
Set FSO = Nothing

Else

'Compact the front end
Application.SetOption "Auto Compact", True

'Sets the file name of the batch file to create
Dim BatchFile As String
BatchFile = CurrentProject.Path & "\Compact.cmd"

' creates the Batch file
' Change the ping value to allow for speed.
' 60000 = 60 secs, 30000 = 30secs etc
' I recommend 60 seconds to prevent any overlapping should the compact take time.
' Large databases may require more time and smaller databases may require less
' time. Just be sure you are not trying to open the database while it is still
' compacting.

Open BatchFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Compacting Front End"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 60000"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & sSourceFile
Print #1, ""
Print #1, "Del %0"
Close #1

' runs the batch file
Shell BatchFile

'closes the current front end and runs the batch file
DoCmd.Quit

End If

Error_Handler_Exit:
Exit Function

Error_Handler:
Select Case Err.Number
Case 3356
sPrompt = "The Back End is currently being used by another User. " & vbCrLf
sPrompt = sPrompt & "You can only Compact the Database if you are the only person using it." & vbCrLf
sPrompt = sPrompt & vbCrLf & "Please try again later."
sTitle = "Back End in use..."
MsgBox sPrompt, vbExclamation, sTitle
Err.Clear
Resume Error_Handler_Exit
Case Else
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Database Error..."
Err.Clear
Resume Error_Handler_Exit
End Select

End Function

HTH

[Missinglinq]

First off, SendKeys has always been problematical, especially from 2007 onward (where it sometimes works, sometimes doesn't) and should only be used as a last resort! Turning Echo off and on in code is simple:

DoCmd.Echo True

DoCmd.Echo False

Whether you elect to use the code from Craig (Irish634) that Burrina posted, or your own, you really need heed Craig's advice

I highly recommend creating a backup prior to running ANY compact/repair operation, regardless of which various method you are using.

Compact & Repair has long been suspected of causing corruptions!

Linq ;0)>