Question Automatic reminder on shared database

[BrankoG]

Hi,

I am making simple Contacts database that would be stored on the server and used by up to 6 users. As I am new to Access, I looked arround and found that simple placing a database file in a shared folder would be good enough for this.

What I am trying to do is to have a reminder that will notify user of important date. I found out that I can easily do that with On Load. The thing is that this is a Contacts database and I don't expect users to start it every day. OK, backup solution is to run it on computer startup, but I hope to find better solution.

The best idea I found so far is to have Access make a reminder in Outlook when important date is inserted into database. Very interesting, but...

Idealy, since I want this to be independant of wheather the user is running the application or not, is to have reminder e-mails delivered to the user on important event. Is there a way to do it? I understand that database file sitting on a server somewhere cannot iniciate anything itself, but I am not sure how to automate it. Would that be a script running on the server, would I need to install Access on the server (hope not), or something like that?

Best regards,
BrankoG

 

[HiTechCoach]

Welcome!

I looked arround and found that simple placing a database file in a shared folder would be good enough for this.

Only the back end should be on the server and shared. NEVER, NEVER NEVER share an Access database except by linking to it as a back end.


Why not just schedule a task in Outlook?

See:
Task w/reminder - from Access to Outlook

and

Set Task into Outlook

 

[BrankoG]

Hi HiTechCoach,

Thanks for a welcome and quick reply.

Regarding sharing a database, you got me confused now. I actualy read that in "Microsoft Office Access 2007 - The Complete Reference" by Virginia Andersen. There were three options for sharing a database - one is to "share entire database", two is to split it to back- and front-end and three is to use SharePoint Services. Can you please tell me why isn't it such a good idea?

Regarding solution with setting a task in Outlook - as I said, that's the best thing I found so far, but there are a couple of things that I am not comfortable with:
- user that is making an entry might not be the only one who needs to be reminded (sometimes, there will be another user, specified by the first one)
- sometimes the date will be changed before it has "expired" - is there a way to remove the previously created task or reschedule it?

My thinking was that e-mail that is triggered on certain date is much "cleaner" solution (in case you need to reschedule, you wouldn't need to worry about if the other user has its Outlook on, is that user still in the system, or whatever else could cause the reminder not to reschedule).

Thank you and best regards,
BrankoG

 

[Mr. B]

BrankoG,

First, it many not seem so, but everyone is actually on the same page here when it comes to using (sharing) an Access database. The info from "Microsoft Office Access 2007 - The Complete Reference" by Virginia Andersen is correct but HiTechCoach is also correct. Both are saying the same thing, the article just takes it a step further. Always split out your data (tables) from what is refered to as the "front-end" file which contains all of the other objects in you applicaiton (forms, queries, reports, modules, etc.).

Now for the issues at hand, have you considered adding process (code) on startup of your existing application (this is assuming that you will be using the split methodlogy) that would check for the important dates and any other necessary conditions and under the specified conditions have the application immediately send out email(s) to the appropriate receipients based on options you write into your code? You could actually then have the application to be started automatically by using the Windows Scheduler.

Once the emails were sent or whatever other actions were taken, if the applcation was started by the Windows Scheduler (when started by the Scheduler you can have the application run a specific, specially designed macro that will set a variable that can then be checked at the end of the automated process) it would then just close the application but users would have been notified.

This is a little hard to write out and I may have not explained the process very well, but prehaps it is clear enough to at least give you some ideas to run with.

HTH

 

[BrankoG]

Hi Mr. B,

Thanks for clearing up about the sharing.

If I understand right, your suggestion is to have the application start up on Schedule (or Windows startup) with flag set. Then it runs, e.g. On Load action - checks condition, sends mails, etc. After that, checks the flag, and if set, reset it and closes the application. If that is so, it sounds as a best solution yet.

Since user application will be responsible for that, I would have to introduce a flag so that e-mails are not sent more than once (for example, new field - yes/no, that would be set on e-mail send and reset on date change).

Thanks very much - I will try this.

Of course, if anybody has another idea, I would be glad to hear it.

Best regards,
BrankoG

 

[Mr. B]

BrankoG,

Yes, you seem to have the general plan down. There will be some specific issues to deal with as you add this type of junctionality, but you can just start and then address each issue as it arises.

The new field you mentioned would most likely need to be a Date type field. When the process for checking and sending emails has been successfully completed, the new field would be updated to the current date. Then when the application is run again by users or opened automatically via the scheduled, that date can be checked and if that date is equal to the current date then the process has already been run for that day.

Actully, the way you initiate something like this is to write a user defined public function and call that function through a macro. That macro can be run on startup by using command like options.

HTH