Run Macro Automatically 3

[tdonahue]

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?

 

[manutd1313]

Using a product called Total Visual Agent, you can have your database run macros on a timer. Here is the link:

http://www.fmsinc.com/products/Agent/index.html

I use this product for many things (copying directories, running updates via macros, etc...). It's very useful

 

[FancyPrairie]

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&quot

 

[GDGarth]

There's another way to do this:

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.

 

[tdonahue]

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


End Sub

 

[DrSimon]

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.

 

[perrymans]

GDGarth, why would you use Dim String rather than Date? Just curious (I'm learning, not second geussing).

'Is

TimeBox.Text = TimeNow
If TimeBox.Text = "4:15 AM" Then

'The same as

TimeBox.Text = TimeNow
If TimeNow = "4:15 AM" Then

'I thought that was part of the reason for capturing the value with a variable?


Also, does the timer only count when that form is open? So that it doesn't check 24 hours a day and when users are accessing?

Thanks. Sean.

 

[perrymans]

Also, user MP9 has a downloadable app at

http://www22.brinkster.com/accessory/resources/001.shtml

for scheduling updates

Sean

 

[GDGarth]

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.

 

[tdonahue]

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


End Sub

The TimeBox.SetFocus is highlighted in yellow

 

[perrymans]

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.

Sean.

 

[tdonahue]

Sean,

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.

Tom

 

[perrymans]

Are you using Access 97, 2000, or 2002?

When you typed "TimeBox." did the drop down list appear, and you SELECTED SetFocus?

Does the debugger continue to stop on the same line, TimeBox.SetFocus?

And you get this Fail Message after going to Debug--> Compile?

Did you stop the last code run? (the blue square to stop the hanging operation). Try closing the dBase and then going straight into the design.

Sean.

 

[tdonahue]

Are you using Access 97, 2000, or 2002?

Access 2000 (9.0 .3821 SR-1)

When you typed "TimeBox." did the drop down list appear, and you SELECTED SetFocus?

No, it did not so I manually typed the words in.

Does the debugger continue to stop on the same line, TimeBox.SetFocus?

Yes

And you get this Fail Message after going to Debug--> Compile?
No

Did you stop the last code run? (the blue square to stop the hanging operation). Try closing the dBase and then going straight into the design.

I closed the database and opened it up again and still the same error.
Sean.

 

[perrymans]

I think I know now.

First, this works:

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.

Sean.

 

[GDGarth]

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.

 

[perrymans]

I mean it should be named TimeBox not TimeNow. Sorry, typo.

Sean.

 

[slreynolds]

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&quot

' Open the desired database
objAccess.OpenCurrentDatabase(strPathToMDB)

' EDIT with your Macro Name
objAccess.DoCmd.RunMacro "MacroName"

strMsg = "The macro named" & vbcrlf & " UpdateVendData" & vbcrlf & " execued successfully."
MsgBox strMsg,vbInformation,"Finished"

' Clean up
objAccess.CloseCurrentDatabase
ObjAccess.Quit
Set objAccess = Nothing


**********************************

 

[perrymans]

slreynolds,

What do you create that in? Notepad? The .vbs I mean.

Thanks. Sean.

 

[slreynolds]

I did use Notepad, but any text editor will do.