Timer application to run macro inside of MS Access database everyday @ 6:00am. Is it possible?

Hello. I'm trying to create this stand alone app that will run a macro inside of a MS Access database store in a shared folder everyday @ 6:00am. I could certainly acomplish this in Control Panel > Schedule Tasks however our wonderful IT department has that feature disable and don't make exceptions. Since leaving MS Access open 24x7 and expecting it not to crash is virtually "crazy" of my part, I'd say that is not an option as well.

How can I do this you guys? Any suggestions/code sample would be awesome!
LVL 1
donniedarko801Asked:
Who is Participating?
 
Mike TomlinsonConnect With a Mentor Middle School Assistant TeacherCommented:
You would need to change the OnceADayProcessing() sub using the info I gave in the link.  It would look something like:

    Public Sub OnceADayProcessing()
        Shell "C:\Program Files\Microsoft Office\Office10\MSAccess.exe" & " " & Chr(34) & "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb" & Chr(34) & " /x Macro1"
    End Sub

You need to change:

    (1) The path to MSAccess,exe
    (2) The path to your Access Database (mdb file)
    (3) The name of your macro (change "Macro1" to the macro in your DB you want to run)

~IM
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Do you have VB6 available to you then?
0
 
donniedarko801Author Commented:
yes...
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
donniedarko801Author Commented:
so, do you have any idea?
0
 
Mike TomlinsonMiddle School Assistant TeacherCommented:
Hi donniedarko801,

Sorry it has been so long...below is basic VB6 code that demonstrates how to make something run each day at a specified time.

Also, you can make a macro run inside your Access Database by using the /x parameter as shown in this PAQ:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20920950.html

' --------------------------------------------------
' Module1
' --------------------------------------------------
' (Go to Project --> Properties and
' set the "Startup Object" to "Sub Main")
' --------------------------------------------------
Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub Main()
    If App.PrevInstance Then
        MsgBox "Another instance is already running", vbInformation, "Already Running"
        End ' kill this instance
    End If

    Dim targetTime As Date
   
    ' setup targetTime for today
    ' (change the time portion to suit your requirements)
    targetTime = CDate(Format(Now, "m/d/yyyy") & " 6:00 am")
   
    ' if it is past that time then set it up for tomorrow
    If Now > targetTime Then
        targetTime = DateAdd("d", 1, targetTime)
    End If
   
    ' just to show when the next targetTime is
    Debug.Print "targetTime = " & targetTime
   
    While True
        ' wait for the targetTime to pass...
        If Now < targetTime Then
            DoEvents ' keep app responsive
            Sleep 100 ' reduce CPU usage
        Else
            ' call your sub that does the desired processing
            Call OnceADayProcessing
           
            ' setup targetTime for the same time tomorrow
            targetTime = DateAdd("d", 1, targetTime)
           
            ' just to show when the next targetTime is
            Debug.Print "targetTime = " & targetTime
        End If
    Wend
End Sub

Public Sub OnceADayProcessing()
    Debug.Print "OnceADayProcessing..."
End Sub
0
 
donniedarko801Author Commented:
wow, awesome code. What part of it calls a sub inside the accesss database?
0
 
GPrentice00Commented:
So, donniedarko801, does this futher input help you, or is there STILL something missing that you can elaborate to Idle_Mind at this time?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.