Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 6th, 2006, 01:32 AM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Time triggered macro in MS Access

G'day,

Is there a way to trigger an MS Access macro based on time, on a daily frequency?

The macro imports a file which is used to update a table.

Using the startup option or an autoexec macro isn't really an option as the database has multiple users who might not close it.

Appreciate any advice.


Thanks,
Dustin
  #2 (permalink)  
Old December 6th, 2006, 05:29 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

If you have a form that is always being shown in your application, then you could use the Timer event, to check the time and when the time is right, run some code. Or a time elapsed etc.

If you have no form that is always open, how about making one and then making it invisible.
  #3 (permalink)  
Old December 6th, 2006, 06:54 AM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Great idea.
Thanks leehambly.
I'll give that a go.
  #4 (permalink)  
Old December 6th, 2006, 08:34 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If you put this code in the front end, and multiple users are running the same front end code, then the event will trigger more than once.

You could write a script that is an old fashioned tsr to run the code from your workstation, or from a server, or you could create a log table that holds a value for a certain time period, and have your cade check that table first before it does anything to make sure you are not running the code as many times as you have users with the app open.

HTH

mmcdonal
  #5 (permalink)  
Old December 6th, 2006, 06:28 PM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Good point mmcdonal. I'll take that into consideration.
Thanks for your help folks. P2P and its members are an invaluable source of information. :)


Cheers,
Dustin

  #6 (permalink)  
Old December 19th, 2006, 06:57 AM
Friend of Wrox
Points: 912, Level: 11
Points: 912, Level: 11 Points: 912, Level: 11 Points: 912, Level: 11
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Dublin, , Ireland.
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
I us the following code to send an e-mail from my database I thought it might be helpful. The timerinterval is set in the form load. Best of luck
 Me.TimerInterval = 60000


Private Sub Form_Timer()
On Error GoTo ErrorHandler
    Dim dtrunreport As Date
    Dim dtCurTime As Date

    dtrunreport = Format(#9:55:00 AM#, "hh:mm")
    dtCurTime = Format(Time(), "hh:mm")

    If dtCurTime = dtrunreport Then
       DoCmd.SendObject acSendReport, "No Assignment Report", acFormatHTML, "Brendan.Bartley@anpost.ie", "", "", "NO Assignment Report", "Suppliers with NO ASSIGNMENT", True
ErrorHandlerExit:

   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

    End If

End Sub

Brendan Bartley
  #7 (permalink)  
Old December 19th, 2006, 06:39 PM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That actually helps with a question my friend was asking. I'll direct him to this bit of code.
Thanks mate. :)

...Dustin


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert UNIX Time to MS Access Corey Linux 4 April 7th, 2009 11:52 AM
Closing MS Access at a set time firefighter2045 Access VBA 5 October 29th, 2007 12:15 PM
Convert UNIX Time to MS Access Corey Access 1 January 23rd, 2006 12:32 AM
Syntax to query Date/Time in MS ACCESS chiuyianl Access 4 March 31st, 2005 09:08 AM
Builiding a time triggered process zicaden Pro PHP 4 September 10th, 2003 04:44 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.