?
Solved

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

Posted on 2005-04-12
8
Medium Priority
?
174 Views
Last Modified: 2010-05-02
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!
0
Comment
Question by:donniedarko801
  • 3
  • 3
7 Comments
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 13769336
Do you have VB6 available to you then?
0
 
LVL 1

Author Comment

by:donniedarko801
ID: 13775328
yes...
0
 
LVL 1

Author Comment

by:donniedarko801
ID: 14140902
so, do you have any idea?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 14369106
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
 
LVL 1

Author Comment

by:donniedarko801
ID: 14397060
wow, awesome code. What part of it calls a sub inside the accesss database?
0
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 2000 total points
ID: 14397218
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
 
LVL 6

Expert Comment

by:GPrentice00
ID: 14471315
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 4 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question