Start an Access DB with Outlook information

[Daryl G.]

I am designing a new database in Access 2003. I have seen and read a lot of
information about creating tasks from Access to Outlook, I am looking for a
link to information about going from Outlook to Access.

What I am trying to accomplish is the ability to manage a contact list,
schedule, and tasks in outlook but be able to use the contact as a record in
Access to which I could add information.

Example,

A Contact card in Outlook already can manage name, title, company, email,
phones etc,

I want to use the contact as a record to add skills (from a Skills Table)

To put this into context, I am building a database in which there will be
Jobs and Resources. Jobs will require certain skills. I can assing those
skills to resources (people who are already contacts in Outlook). I then
want to be able to query from a Job Form to find all resources with the
skills required to satisfy the job.

Thanks.

Daryl G.

 

[Daryl G.]

OK, I created a linked table to a contacts folder, but I have no key from
which to I can link the resources. Also, the table is not normalized. I will
have trouble with even simple things like State. Any suggestions? I really
do not want to recreate the wheel. Outlook already has good UI for contact
management, and linking into Calendar, Tasks. So I would prefer to manage
all those feature in Outlook, only keeping the Jobs and Skills tables in
Access.

Thanks,

Daryl G.

 

[Arvin Meyer]

The name of the Exchange field that you need to Link on is EntryID. I assume
it's the same in Outlook.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Daryl G.]

Arvin,

Thanks for the response. I am new to Access, but am running into issue
trying to use an outlook profile. I have linked Contacts, Tasks, Calendar,
and Notes into the Access Database. I am not using Exchange, so I am forced
to use the Outlook() datasource.

Issue I am try to solve:
1. Access requires a profile, which is fine for one user, but I want the
database to be multi-user. So that any user could find any contact with the
skills necessary to match against a job. I could import the data, but it
seems then that the ability to manage the data in outlook would be lost.

2. When I link these tables in Access, it appears to take just the front end
table (for example Tasks) which contains some data, but Outlook seems to
store some data in supporting tables, to which I do not appear to be able to
gain connectivity to. Some of the field names even suggest this (such as
normalized subject). Also, key data, such as contacts assigned to tasks are
not (or I cannot find) associated with the tasks, or Contacts table.

3. Access says that external relationships will automatically maintain there
linkage when linking to an external table. This does not appear to function
for me in linking to Outlook. Every field appears to be flat, no lookups of
any kind. And the relationships view does not show and one to ones, one to
manys or any join tables.

I have read the Microsoft provided white paper on Relational Database
design, and will be reading some additional text, but would greatly
appreciate any guidance from your or anyone else's experiences. Am I going
the wrong way on this, should I just manage all the data in my DB?

Also, any references to good sites, or even other newsgroup post (on this or
other newsgroups) would be appreciated. I have experience working with
database structure from a consultant point of view working in Microsoft
Navision (setups, support tables, etc.), but I have always had developers
doing the actual code for me, this is a personal project so I am using it as
a learning experience.

Thanks,
Daryl

 

[Arvin Meyer]

Outlook stores its data in a pst file which can only be accessed by the user
who owns it. The only way you can access the data in a multi-user
environment is through a Public Folder in Exchange.

Perhaps you need to rethink your methodology. Either build a network and use
Exchange as the mail server or use Access as a contact manager and Outlook
as the mail client. Personally, I find the second choice more robust.
Exchange folders and the event service that runs the code are neither
flexible nor reliable, at least in my experience. There's some code on my
website for working with Outlook and Access. The Outlook and Echange MVPs
have even more on their websites.

Exchange and Outlook use a massive not-structured JET database for storage.
I say non-structured because of the way it stores the data. Most databases
use the intersection of a row and column as a defined place within an array
to poke a piece of data. Exchange and Outlook do too, but only if the data
exists. If it does not, you cannot address the NULL. So you cannot, for
instance check for duplicates until after the record has been saved. Trying
to sync 2 data structures where 1 of them has a value that shouldn't be
saved and the other that must be saved is a nightmare.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Daryl G.]

Arvin,

Thanks Again for your reply. I've begun to write the DB such that it is the
contact manager, and have followed (99% of the time) the rules for 3rd
normalization. However just to follow up on this thought, Now that I am
maintaining all this data in Access, and using my tables for the rest of my
functions, is there any way to also have the linked tables in the database
and write code such that a change in either place will sync to the other.
For Example, I have People.Last for Last Name capture. Could I Sync my
People.Last to the linked IPMContacts.Lastname such that a change in either
record will update the other? If yes, then One record in the Contacts table
would contain fields that would point to many different tables, is that an
issue?

Thanks again!

Daryl

 

[Arvin Meyer]

Yes and no. There are no Triggers in JET, so the data cannot cause the
changed. BUT, Exchange has an Event Service which responds to 4 possible
events similar to a Trigger. The problem is that it doesn't work all the
time. Any error, stops the process until the service is restarted.

On the the Access side, you can use code in response to the AfterUpdate
event of a control on a form. There are a few pitfalls with this too. First,
you must link using the EntryID. You can only get that from the Outlook
contact. So what you need to do is import the contacts from Outlook into
Access. Actually, the only way to make that work is to pull the data with
code from Outlook. I haven't done it in quite some time, and the code I
wrote works on Exchange (it may work in Outlook too, but I suspect that it
may need some changes) I think I adapted the code I had from something on
Helen Feddema's website, so you might hunt around there a bit:

http://www.helenfeddema.com/CodeSamples.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access