Create Timed Events in a Closed Database

[yeatmanj]

I have a couple of questions, but I will start with a little background.

Database stores and calculates report due dates. When a due date occurs I would like the database to notify the project manager by email. The database is broken up into a front and back end application. Each user opens the front end and works on it daily. Obviously I don't want every front end running code and emailing project managers, so my first questions is: How can you make code run forever in a back end?

Now, on a daily basis, I want the code to run and check to see what reports are due today and email the project manager. What commands (functions, proceedures, methods, etc...) would I use to cause this? I have a module that performs the necessary calculations to determine what report is due today, so all I need is how to make it run that daily from a back end.

I am assuming that this can all be done by a module, so if you find my post is the wrong location please let a moderator know so that it can be moved.

Thanks in advance!

 

[yeatmanj]

Since no one has an answer to the first part let me try new approach (to human sacrifice).

How about using an open front end to perform functions like compacting on the back end?

I am simply going to key the notification portion to only email information pertaining to the user logged in, but that still leaves my desire to do things to the back end since it is not directly logged into.

 

[jeff_i]

When I have done this in the past I have ended up with the folowing:

1. One backend database (you have this already)
2. One user Front End (you have this already)
3. An Admin database which runs the updates daily send the reports etc

What you would do is create a new front end which I call the Admin database, then using either queries or VBA code your procedure to email reports etc

Then use windows task scheduler to open the database at set times during the day to call this macro or VBA. (You will always use a macro since they can be referenced in the command line of opening the database)

This is only my solution hope it helps

 

[yeatmanj]

That's not a bad idea. I do need to know what I would use to control the back end (i.e. DDE? OLE? ODBC? simple VBA to open & compact?). Thanks.

I am still open to other suggestions, like maybe a way to embed things in the back end. It isn't opened directly but it does get opened due to linked tables. Don't know if that matters or not.

 

[yeatmanj]

Ideas? Anyone?

 

[The_Doc_Man]

The last time anyone had a problem like this, the approach was to open the database via a command line that named a macro to be executed. /X switch, I believe. Look it up under Access Command Line options, 'cause it has been a while since we dealt with that problem here.

Build the macro that you name in the switch. Make it run code, run action queries, do whatever it is that you want it to do. Probably CANNOT make it meaningfully open forms, but you might be able to open and save a report to a file, which you would then make as part of your mailout if that was where you were going with this.

Then build an icon containing that command line. Get the macro to work correctly when you launch the icon. Details you have to worry about for this macro include specifying a username and password if you have secured the front-end with a workgroup. Include a way of resolving whether ANY e-mail should be sent at all.

Then when it all works and all you had to do was double-click the icon, put that icon in your Windows Task Scheduler to run on the schedule you want.

 

[adaniele]

solution

yeatmanj, sorry for bothering u. I'd like to know if you find a solution to your problem, run a code while the DB is closed. I am haveing the same issue but i dont have a front end and backend, i have a db in a network resource and people access to it through a shortcut.

thx, max.

 

[yeatmanj]

No ideas really. I have heard mention of a program that will perform remote maintenance, but have not researched it further.