autoexec macro (1 Viewer)

[geno]

Hi,

I have some code that will copy a database then compact repair it if no one is using it. I've put this module in an autoexec macro and am trying to run it from task scheduler. When I activate the task the db opens to the module but doesn't run the code. What am I missing here? Here's the code I'm using:

Option Compare Database
Option Explicit
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare PtrSafe Function GetFocus Lib "user32" () As LongPtr
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare Function GetFocus Lib "user32" () As Long
#End If

Public Function fncbackup() As Boolean
Dim objws As Object
Dim strLocal As String
Dim sDest As String
Dim sSource As String

If Dir("C:\Users\Geno\Desktop\2003\NewWebDb.ldb") = "" Then

sSource = "C:\Users\Geno\Desktop\2003\NewWebDb.mdb"
sDest = "C:\Users\Geno\Desktop\dbbackup\NewWebDbBU" + Format(Now, "YYYY-MM-DD") + "__" & Format(Now, "hh-mm-ss") + ".mdb"

FileCopy sSource, sDest 'On Error Resume Next
strLocal = "C:\Users\Geno\Desktop\2003\NewWebDb.mdb"
strLocal = Chr(34) & "MSACCESS.EXE" & Chr(34) & " " & Chr(34) & strLocal & Chr(34) & " /compact"
Set objws = CreateObject("wscript.shell")
'0 - hide
'1 - visible
objws.Run strLocal, 1, "false"
Call Sleep(3000) 'Wait 3000 milliseconds
objws.SendKeys "test", True 'password
objws.SendKeys "{ENTER}"

DoEvents
'Call Sleep(200) 'aguarda por meio segundo

Set objws = Nothing

fncbackup = True

Else

End If

End Function

Thanks for any help in advance.

Gene

 

[geno]

Hi,

I figured this out and it works except for one thing.
When I run the code directly from the db that has the function everything works great but when I run it from the task scheduler the source db that compacts does compact but when the task finishes running this db is left open at the login. Then I click cancel and the Db closes. Is there a way to close the compacted db after the task runs?

Thanks