I have designed a macro to automatically update some of my tables. This macro takes about 15 minutes to run. I was hoping it was possible to have this macro run an hour or so before I start work every day. Is this possible?
You can also use Windows Task Scheduler. (GoTo START | PROGRAMS | ACCESSORIES | SYSTEM TOOLS | SCHEDULED TASKS. Note that instead of "SCHEDULED TASKS" it might be called "WINDOWS TASK SCHEDULER"
Create a new form
Put a text box in the form named "TimeBox"
Put this code in the On Timer event:
Dim TimeNow As String
TimeNow = Str(Time)
TimeBox.SetFocus
TimeBox.Text = TimeNow
If TimeBox.Text = "4:15 AM" Then
DoCmd.runmacro "YourMacro"
end if
exit sub
Set the Timer Interval to 60000 (one minute). When you go home, leave Access on, with this form open. It will check the time every minute, and run the macro at 4:15.
I don't remember why I converted the text to a string, but that may not be necessary in your case. If it is, you'll need to make sure the format of your string matches exactly.
I use this to automatically update some configuration files every night when there's little or no activity on the computers. It's crude, but it works OK.
GD Garth I believe your solution is the one I want to try.
I have tried it thou and I am getting a runtime error 424 Object required.
My code is as follows:
Private Sub Form_Timer()
Dim TimeNow As String
TimeNow = Str(Time)
TimeBox.SetFocus
TimeBox.Text = TimeNow
If TimeBox.Text = "8:25 AM" Then
DoCmd.RunMacro "RunTotalUpdates"
End If
Exit Sub
I'd definitely go with FancyPrairie's idea. I have a user who wanted spreadsheets updated every morning. I create files every night and call an Access macro using the /x command line parameter using the scheduler.
Psalm6229 - The only problem I can see is if you didn't type your macro name exactly right. Check that and see. Other than that, it should work (unless there's a problem with the macro, itself).
Perrymans - I'm sure you're right. If you read my original post, I said I didn't remember why I did that. I wrote that code a couple of years ago - I've since learned a few things. I just posted it the way it was because I knew it worked that way.
The timer will only count while the form is open. I wouldn't worry too much about slowing things down, though. It doesn't take much in the way of resources to check the time once a minute.
After I get the run time error 424 Object required and I run the debugger this is the message I get.
Private Sub Form_Timer()
Dim TimeNow As String
TimeNow = Str(Time)
TimeBox.SetFocus
TimeBox.Text = TimeNow
If TimeBox.Text = "6:18 AM" Then
DoCmd.RunMacro "RunTotalUpdates"
End If
Exit Sub
It is an error from copying from html to VBA. Just delete the "TimeBox.Setfocus", and retype "TimeBox.". When you get to the ".", the drop down list will appear, select SetFocus, and you're done.
It should compile after that.
Remember to compile before you try running it (Debug->Compile). This will help make sure that your code runs fast, and correctly, before you leave VBA.
I tried your solution and it did not work, I still get the same error. Also, the firewall at work will not allow me to downlaod anything from the internet, that is why I could not try your previous solution.
Private Sub Form_Timer()
Dim TimeNow As String
TimeNow = Str(Time)
TimeBox.SetFocus
TimeBox.Text = TimeNow
If TimeBox.Text = "3:04 PM" Then
DoCmd.RunMacro "RunTotalUpdates"
End If
Exit Sub
End Sub
There are two key things. Even if you have the code correct, you have to set the Format property of the TextBox to "Medium Time". Otherwise, you will get the seconds shown, and 4:15 doesn't equal 4:15:04. Don't try to guess at the seconds, because it changes.
Your problem is likely you don't have the text box named "TimeNow". That is why it fails when it tries to SetFocus, it's not there.
That is also why you did not get the drop-down list after typing "TimeNow. ". If VBA recognized the control TimeNow, it would have given the properties automatically after the " . " was typed.
That's correct, I forgot to mention the format of the text box is important.
"TimeNow" isn't the textbox name - it's the variable that holds the string. The text box name is "TimeBox". Other than that, Perrymans' analysis is correct.
Now that I think of it, the reason I changed the time value to a string is so I didn't have to worry about seconds. There are other ways around that, but that was the easiest way at the time. Mystery solved, I guess.
You can run an Access macro from a simple Windows script file. Edit this script with your database/macro information and save with the extension .vbs. You can now run it through the Task Scheduler. (You should test run it separately first).
*************************************
Dim objAccess
Dim strPathToMDB
Dim strTableName
Dim strFilePath
Dim strMsg
' EDIT with your path and database name
strPathToMDB = "c:\Database.MDB"
' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.8"
' Open the desired database
objAccess.OpenCurrentDatabase(strPathToMDB)
' EDIT with your Macro Name
objAccess.DoCmd.RunMacro "MacroName"
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.