How to trigger MS Access from a timer? 1

[deekydoo]

HI,

The system I am developing comprises 5 desk top Access(.mdb) systems accessing tables on a server.

I need to trigger an Access system on a 6th desktop which will examine 'work in progress' tables on the server and fire off emails when certain conditions apply.

My question is: How do I schedule the 'regular' Access system to run at a certain time interval (eg one hour) and what code do I need to put into that Access system to shut itself down after completing its tasks?

Thanks

Deek

 

[Remou]

It is best to use the Task Scheduler to run Access. You can use DoCmd.Quit to exit.

 

[deekydoo]

Thanks Remou - I'll take a look along that line.
Deek

 

[iojbr]

Hi:

I have two Access databases. The backend database (BE.mdb) is on a network drive (L The Front end (FE.mdb) is located at users' workstations on local drives (C. I am trying to use Windows task scheduler to run a Macro (Scheduled1) in FE.mdb to update some tables in BE.mdb. It only works when I instruct the task scheduler to run the macro every time a user logs onto his workstation or at a specific time everyday. However, the second option will only work when the user is already logged into the computer. I want the system to do a daily update in the background when the user is logged off. So far, everytime I try this, it doesn't work, and the workstation creates an .ldb file. I thought the problem might be that you need to instruct Windows to map to the network drive before running the macro, but it still won't work. Any help will be appreciated. Below is some code. IMS Back End is the folder that contains BE.mdb in Drive L:


Dim MyDriveName As String
MyDriveName = "\\dhsrlcsrv01\vrdlgroups\VRDLGROUP\IMS Back End (L)"
'------------------------------------------------------------
'- map drive
On Error Resume Next ' DRIVE MAY BE MAPPED ALREADY
Set MyDrive = CreateObject("WScript.Network")
MyDrive.MapNetworkDrive "L:", MyDriveName
DoEvents
'--------------------------------------------------------------
'- error check
If Err.Number <> 0 Then
MsgBox (" Drive already mapped or not available ")
Else
End If

 

[deekydoo]

Thanks Remou, that works a treat.



Hi iojbr,

I have a setup similar to yours I believe - A 27MB(a lot of VBA) Fe on 5 desktops linking remotely to a +17MB(solely tables) Be on a file server. There are no operational tables local to the Fe machines.

On the Fileserver box itself I have another( small) .mdb running software that simply trawls thru the 'emails to do' table on the Be sends emails as and when necessary via CDO. This email sender is triggered by windows task scheduler @45 minutes whether or not anyone is logged in.

Although the Fe's link to the Be tables via a network drive, the email sender links to the Be tables. Could you not build your macro in a separate .mdb on your Fe box? Or does it need info from the FE machines?

Deek

 

[mp9]

Handy Access Launcher is a useful Access scheduling tool

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