scheduling Access/VBA events

[accessguy52]

Hello..anyone know how I can "schedule" an Access/VBA event such as appending/deleting records at a certain time? I know about the OnTime event, but that is in Excel. Will OnTime work in Access? I want to let an event kick in at certain time, on a daily basis, without my intervention. Thanks in advance.

 

[RottPaws]

Here's how I did it:

I created a form with the following unbound controls:
[tt] Control Type Name Caption/text
text box txtUpdateTime default=time()
Command Button cmdSchedule &Schedule Update
Command Button cmdCancel &Cancel
Label lblMessage -blank-
Label lblFinish -blank-
[/tt]
Set the command button OnClick Events to Event Procedure.

Enter the following code:
Private Sub cmdCancel_Click()
If cmdSchedule.Enabled = False Then
cmdSchedule.Enabled = True
lblMessage.Caption = "Scheduled update canceled"
lblFinish.Caption = ""
txtUpdateTime.Enabled = True
Me.TimerInterval = 0
Else
DoCmd.Close
End If

End Sub

Private Sub cmdSchedule_Click()
If IsNull(txtUpdateTime) Then
MsgBox "Enter time to begin update."
Exit Sub
End If

'My macro runs some queries that hit linked tables that are in Oracle databases that require a login/password.
'So I open one and close one table from each db to get the user to login.
'Open the Circuits table to establish connection to Oracle DB1, then close
DoCmd.OpenTable "linked oracle table name"
DoCmd.Close acTable, "linked oracle table name", acSaveNo

'Open Job_Steps table to establish connection to Oracle db2, then close
DoCmd.OpenTable "Linked Oracle Table Name"
DoCmd.Close acTable, "Linked Oracle Table Name", acSaveNo

Me.TimerInterval = 60000

lblMessage.Caption = "Update will begin at " & Format(txtUpdateTime, "Medium Time&quot
lblFinish.Caption = ""
txtUpdateTime.Enabled = False
cmdCancel.SetFocus
cmdSchedule.Enabled = False

Dim sTo As String 'Mailing list
Dim sSub As String 'Text to appear in the subject line
Dim sMsgText As String 'Text to appear in the message

sTo = "username1; username2; username3"

sSub = "Process Schedule Notification Subject Line"

sMsgText = "Process has been scheduled to begin at " & Format(txtUpdateTime, "Medium Time&quot

DoCmd.SendObject , , , sTo, , , sSub, sMsgText, False

MsgBox "Process schedule notification has been sent."

lblFinish.Caption = "Do not close this database or shutdown the computer. This will cancel the scheduled update." '"Closing this database or shutting down the computer will cancel the scheduled update."
lblFinish.ForeColor = vbRed

End Sub

Private Sub Form_Timer()

If Format(txtUpdateTime, "Medium Time&quot = Format(Time(), "Medium Time&quot Then
Me.TimerInterval = 0
lblMessage.Caption = "Update began at " & Time()
'Run the scheduled procedure
DoCmd.RunMacro "macUpdateOrderStatus"
lblFinish.ForeColor = vbBlack
lblFinish.Caption = "Updated completed at " & Time()
txtUpdateTime.Enabled = True
txtUpdateTime = Null
cmdSchedule.Enabled = True
Notification
End If


End Sub

Private Sub Notification()

Dim sTo As String 'Mailing list
Dim sSub As String 'Text to appear in the subject line
Dim sMsgText As String 'Text to appear in the message

sTo = "username1; username2, username3"

sSub = "Notification email subject"

sMsgText = "Process was completed at " & Format(Time(), "Medium Time&quot

DoCmd.SendObject , , , sTo, , , sSub, sMsgText, False


End Sub

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[PaulBricker]

Well I can't find my post from a few days ago so I'll repeat it. If you are running Win98 or newer or NT 4.0 there is a Task Scheduler. You can usually see the icon in the lower right hand corner of your Start Bar. Open Task scheduler. In the Run command line I have

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "\\SCHOOLHOUSE\PhyPlant\Physical Plant 2002\PysPlant2002.mdb" /xrintMacro

/xrintMacro is a switch that will call a Macro in Access which I use to call a Function to print a Report every day.
/x: is the switch and PrintMacro is the name of my Macro.

The first Path is to the .exe file, the second path is to my database that I want to run.


In the Look In command line I just have the path to the Access.exe file.

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"

Then you just have to fill in the info about when you want to run it. After that, the only requirement is that the PC be on.

Paul

 

[RottPaws]

Paul's solution sounds a lot easier than mine if you have the task scheduler. Due to our IS dept's never-ending quest to limit our ability to function, we don't have the task scheduler on our workstations, so we have to find creative ways around some of these limitations. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[accessguy52]

Thanks, RottPaws and PaulBricker..I'll consider both solutions and try them out, though I agree that the Task Scheduler solution sounds more promising. I'm still open to other solutions from anyone else out there...

accessguy52

 

[PaulBricker]

Rott Paws I share your pain. Task Scheduler is a breeze to use compared to how we used to do it. It opens a second instance of Access, runs my report and shut Access in about 10 seconds. I don't have to be anywhere near my PC and I don't have to have Access open which is wonderful. Fortunately we are a private school that has some funds for software. Now if we could just talk about Salary

Paul

 

[accessguy52]

Hey RottPaws, looks like I don't have Task Scheduler either, so I'll have to try your code. I have everything set up, macro and VB automation demo (this is an experiment, really). So I share your pain, also. My co-worker in cube across from me is trying to find out if we can set up T.S. for me. So, R.P. we get to see if YOUR code will do it in the meantime. Many thanks, guys!!

accessguy52

 

[RottPaws]

Good luck. Let me know if you have any problems w/ it and I'll try to help. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[accessguy52]

Rott - quick question..Do I have to open and close the tables like you did? All I'm doing for now is just exporting data from Access to Excel as an experiment, but, will be similiar to what I really want to do. You had to open and close to allow users to login. What about me? My code opens a database to allow data to be exported, but that's all. Hmm??

 

[RottPaws]

No. My app has linked Oracle tables which did not have the password saved when they were linked. So each time the Access DB is opened, the first time a query/form/report goes to those tables, the user is prompted for their username/password.

Since the process is being scheduled to run automatically at a different time (4 a.m. in my case), the connection to Oracle needs to be made ahead of time. Without that, the user would schedule the process to run at 4 a.m. and when they come in at 8, they would have an Oracle login box waiting for them.

The process that I schedule (where I copied that code from) uses tables from 2 different Oracle databases. So I open and immediately close 1 table from each database.

Another way around this would be save the password when you link the tables. This way, it would not stop to prompt for login. In this case, this is not something I want to do. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[accessguy52]

Rott, I'm afraid it did'nt work. I copied your code in except for the Sub Notification (I got the notification in Outlook anyway).

I typed in 3:30 for my execute time and nothing happend except for the labels and Outlook notification. I expected that I would see Excel rearing up and having the data exported from Access. Nada. 3:30 came and went. Any ideas what I could be doing wrong? I know you can't see what I'm doing here. I've got your 2 buttons with appropriate code.
?????

accessguy52

 

[RottPaws]

It's 3:30 already!??!??!

Try 3:30 PM or 15:30 _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[accessguy52]

Rott - I did type in 3:30 PM and again at 3:50 PM. Still didn't work. I pointed to the macro (after slight mis-spelling on my part). The Task Scheduler option is prpbably my best bet but until I get that...I'll keep on truckin' with your code.
Since we're both busy people I'll work on it and let you know later..thanks!

 

[RottPaws]

Forgot 2 settings earlier:

Set the Format property of the txtUpdateTime to 'Medium Time' and set the form's OnTimer event to [Event Procedure]

Then try it again. When the time comes, does the label on the form change to say 'Update began at 3:50:02 PM'? If it does, then it means the even is being triggered and you just have a problem with what is supposed to be running. If not, then it's a problem with the even itself.

Not sure the Format would have any real effect, but if you don't have the OnTimer even set to run the code, then that will definitely make it not work. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[accessguy52]

Rott - I hadn't associated your code for Form_Timer with that event. Was wondering what OnTimer was for. Wow!! IT'S ALLLLIIIVE!!! Thanks so much!

accessguy

 

[RottPaws]

No problem. Glad I could help.

I had to figure out how to do that (and a few other things that Access "can't do&quot before I knew tek-tips.com existed . . . That was fun.....

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!