Frustrated by access and outlook calendar

[keri]

Hi,

I am a very frustrated user after searching all day for a solution to
my problem.

I currently have a form in my db that creates an apointment, saves it
to outlook and saves it in a table in my db. This works excellently.
However I want to be able to update my db appointments table with any
appointments edited through outlook, or to update outlook appointments
with any edited appointments in my db appointments table.

I have discovered;

I can link to the outlook calendar table from my db but this does not
expose the start and end times of the apointments, nor am I able to
edit the apointments.

I can export from outlook or import into access the outlook calendar
appointments table (which exposes all of the required fields) however
this is not "up to date" ie, it is only correct immediately after
imort/export, and does not run in "real time" and update after edits

I have read articles about ADO/DAO (neither of which I know anything
about), however all articles are aimed at somebody with a base
knowledge that I do not have. I have read MS articles, slapstick pages
and many many others, alongside posts on these pages, however I still
have not found a hint at a solution that I can understand.

I really am prepared to work on this, however without being able to
find a starting point I am facing a brick wall. Any help would be
appreciated.

 

[Albert D. Kallal]

You in a difficult situation, since what you are attempting to do will take
some good coding and software skills on your part. If those examples and
references are beyond your skills, you going to have to get those skills!



Further, perhaps you dump the use of ms-access, and stick to one
application. I know a number of hair salons and even restaurants that simply
use the great Day-Timer features in outlook. You can view all appointments
(bookings) in a nice calendar, and if you flip into the Day-Timer view,
then all appointments are listed by time of day. Really, quite nice, and
often outlook is overlooked as a Day-Timer, and booking/scheduling solution.



Further, your problem is that you want both programs to communicate with
each other. So, when a user in outlook makes an appointment, does outlook
test/check if ms-access is installed on other computer. Does outlook then
test if access is running? What happens if the access program is printing a
report.but outlook needs to make a new appointment? And, what about the
reverse.is access to test for outlook running? And, what happens if outlook
is busy sending a email?



There really is a terrific amount of issues you have to deal with if you
MUST use both applications together. You can certainly have ms-access open,
and access ALL OF the appointment data in outlook. Further, you can have
ms-access create new appointments in outlook also.



Here is an example:



http://support.microsoft.com/?kbid=209963



I guess the real question is do you really need outlook? I would also
perhaps consider writing your own calendar system in ms-access. The results
can be quite nice. (here is I made with some screen shots)



http://www.kallal.ca/ridestutorialp/setdriver.htm



Note the example calendar in the above. The calendar was actually not that
hard to build, and I used a sub-form for each day. It took me less then a
afternoon to build that calendar. I don't have a sample download (yet), but
you can find a number of access calendars that you can modify here:



http://www.granite.ab.ca/access/calendars.htm



What road you take really depends on if you need other features of outlook,
or just a calendar..and are using outlook to avoid making your own...

 

[keri]

Thanks for the reply.

Unfortunately I do need to use Outlook -I originally included a
calendar I had coded by myself but this was overruled as the db I am
creating will be used by remote employees and their managers need to
view their calendars. (This is only one of the reasons I was given for
using outlook).

In simple terms there are 2 parts to what I need to do;

1. Appointments will be created in access and stored in a table. On
creation they will be saved to the Outlook Calendar. (I have coded
this part fine). To simplify the problem I have here I COULD just tell
everybody not to update theit appointments in outlook - only to use
access - however in the real world this may not happen - therefore
leaving the user with unmatched appointments in outlook and the db.
This doesn't sound like the end of the world, however the db uses the
appointments for sceduling, and therefore the scheduling would be
wrong if outlook was updated but access wasn't.
Some possible solutions to this-
a) Have the appointments "uneditable" in outlook. I have not
researched the possibilty of doing this as I think it is "messy"
however it could be simpler than another solution
b) Have outlook & access "talk" to each other about updates in either
place. (This is what I can't find a solution to)

2. In my db a table assigns a number (1 - 6) in sequence to each
working day in a year.
Eg
Mon 1 Jan 1, Tue 2 Jan 2, Wed 3 Jan 3, Thu 4 Jan 4,
Fri 5 Jan 5, Mon 8 Jan 6, Tue 9 Jan 1, Wed 10 Jan 2
etc etc

These numbers are to be sent to Outlook as appointment items, and
these tell the user which area to visit on each day.
However the user will need to change some of these items - eg if they
have a holiday booked on the 10th Jan they would need to use a form in
the db and update the number on the 10th Jan to say "Holiday" instead
of "2" (as they won't be visiting area "2" on that day, and their
outlook calendar needs to be updated to reflect that. This change then
needs sending to Outlook. I'm sure this is possible through a kind of
afterupdate however I need to find the previous appointment in outlook
(the one that was "2") and edit it to be "holiday", otherwise I will
be duplicating appointments. I believe to do this I will need to give
each appointment created a unique index, but I am not sure where to
put this, or how to search for it and edit the matching record in
Outlook.

I am prepared and more than willing to learn the processes required,
however wherever I turn I am pointed to slipstick and outlookcode, but
I need more introduction before these sites help me.

 

[Albert D. Kallal]

Some possible solutions to this-

a) Have the appointments "uneditable" in outlook. I have not
researched the possibilty of doing this as I think it is "messy"
however it could be simpler than another solution

b) Have outlook & access "talk" to each other about updates in either
place. (This is what I can't find a solution to)

I see where you stand here. Remember, users of palms, ,or even blackberry's
phones often use the daytiming feature of outlook. I would be absolute livid
if a application came along that prevented me from using outlook for my
personal appointments. I actually near live out the outlook calendar. So, as
you say above....locking up outlook is "messy".

I am prepared and more than willing to learn the processes required,
however wherever I turn I am pointed to slipstick and outlookcode, but
I need more introduction before these sites help me.

yes, and what about people editing, and moving, and changing appointments to
repeating occurrences etc?
(and worse, how you do decide what appointments belong in both? In other
words, are all of my birthday
and meeting reminders to also be put in ms-access? Like you say, you can't
go around taking outlook away
from people..it is my daytimer!!!

There are some new features in outlook 2007 that expands the ability to use
code *outside* of outlook.

Some of these abilities require you to write in .net code.

http://msdn2.microsoft.com/en-us/library/ms772422.aspx


You going to have to get some books on this, and you have to be able to
actually update each users version of outlook for the code (this would be a
good reason to consider using .net as your development for this, as the code
could be outside of outlook 2007).

 

[keri]

Thanks again Albert.

There are some more points to this.

1. The users who will be working in my db do NOT currently use Outlook
for personal appointments. (This still doesn't mean I want to "lock"
outlook - it's just for info)
2. Only appointments created in my db will be stored both in my db and
outlook. I am not worried about users creating new appointments in
outlook, only about editing appointments that were created originally
in the db.
3. I definately do not want to code Outlook, for the reason you
mentioned (that this would need updating for each end user's Outlook),
all code needs to be contained within the db object
4. If I disregard the problem of having to have outlook talk to my db
(by presuming that no edits will be done in outlook, only in the db)
my main problem is to get the db table(s) to update existing
appointments in outlook by finding them and then changing the details.
This is the problem I really need to conquer.

If anyone has any book suggestions I will be more than pleased to
start there. I already have 3 books on access, however none of this is
covered so I will need something more specific.

If it helps we use Outlook 2003 and Access 2000.

 

[Albert D. Kallal]

If anyone has any book suggestions I will be more than pleased to

start there. I already have 3 books on access, however none of this is
covered so I will need something more specific.

If it helps we use Outlook 2003 and Access 2000.

The one link I mentioned has some examples:

http://support.microsoft.com:80/?kbid=209963

so, you going to have to use automation. For changing something in ms-access
(like deleting, or moving a appointment),and then updating the change in
outlook, you simply use the above type "automation" code. You have to pull
out the original outlook appointment for that day, and match it to your
change. (ie: then move it, or delete it). How you match up that appointment
for the day is going to be up to you (ie: some number you include in the
booking text on the outlook side, or even perhaps a match of the same "text"
that you have in ms-access will suffice).

Assuming the booking came from ms-access, then matching up the text and date
should work quite well - this is especially so, since you note that users
don't use outlook for other appointments anyway.

What this means is that if you provide a interface in ms-access to delete,
or move, or create a new appointment, then that makes the automation code
quite a bit more easy. This also means you don't want users to "type", or
simple "edit" a booking date in the ms-access side, but provide some button
to be pressed (that way, you can easily control the code that runs when I
user needs to change/create/delete the booking information on the ms-access
side).

 

[aaron.kempf]

hey is this keri, keri?

I'd check out www.freevbcode.com it has some examples on using outlook
programmatically

you can paste outlook automation code into Access VBA (Modules) and
then it won't take a ton of maintennce

hey if it's keri-keri; then the check's in the mail !

-Aaron