Posted in OL, Try for suggestions/comments here.

[Robin]

Ref: Access/Outlook 2003/Exchange Server
I'm far from an expert (learn it as I go along) but I was developing an
application in Access and worked on linking to OL Contacts before I ever
discovered this site. Having not read all the posts about the inherent
limitations I forged ahead and came up with something that seems to work ok,
albeit slowly! In Access I had a "ClientID" field (6-8 alphanumeric
characters) unique to each client. ("Primary Key") In that table I collect
all the data I needed to develop the application. (Client legal/tax types,
fiscal year end, state of organization, etc.) with relationships to other
tables needed. Then in Outlook I used the "User Field 1" (this is one of the
OL fields that DOES link) as the ClientID and in Access I write queries that
establish the relationship between the internal Client table and the linked
OL table. (ClientID-->User Field 1) (This is not a "primary key" in OL so
you must be careful entering values to prevent duplicates.

Initially I had a form that had all the client info, but it was so slow to
populate the linked table values that I created a popup subform with the
linked table fields so users can use the Client form (without
address/phone/etc) as needed and click a button to bring up the additional
information when needed.

If anyone tries this and can come up with a way to speed it up, I'd like to
hear about it!!!

Also, just as a note, at one point during my development I added a user
defined field to Outlook saving that OL form as a new default form in a new
folder. That user defined field DID come through to Access during a link but
when I discovered that Contacts can only be viewed in the form in which they
were created and that I would have to change the message class for all
existing entries I abandoned that approach. (The OL contacts had been
accumulating for years...I am new...didn't want to change their
defaults...they're all paranoid about such things!)

If anyone tries/has tried these approaches and have any suggestions on
improving on the (little) progress I've made, please let me know!

Thanks,
Robin

 

[Sylvain Lafontaine]

Answer in-line.

Ref: Access/Outlook 2003/Exchange Server
I'm far from an expert (learn it as I go along) but I was developing an
application in Access and worked on linking to OL Contacts before I ever
discovered this site. Having not read all the posts about the inherent
limitations I forged ahead and came up with something that seems to work
ok,
albeit slowly! In Access I had a "ClientID" field (6-8 alphanumeric
characters) unique to each client. ("Primary Key") In that table I
collect
all the data I needed to develop the application. (Client legal/tax types,
fiscal year end, state of organization, etc.) with relationships to other
tables needed. Then in Outlook I used the "User Field 1" (this is one of
the
OL fields that DOES link) as the ClientID and in Access I write queries
that
establish the relationship between the internal Client table and the
linked
OL table. (ClientID-->User Field 1) (This is not a "primary key" in OL so
you must be careful entering values to prevent duplicates.

Initially I had a form that had all the client info, but it was so slow to
populate the linked table values that I created a popup subform with the
linked table fields so users can use the Client form (without
address/phone/etc) as needed and click a button to bring up the additional
information when needed.

Usually, a slow populating of a form is often the sign that your database is
missing some indexes on important fields that are used in relationships.
For example, if your Outlook form access the table by using the UserField1,
then you should have an index on this field; otherwise, Access will have to
scan the full table each time in order to find the right row.

Also, if I remember correctly - but I might be wrong - JET will
automatically add an index to any field (or group of fields) used as a
foreign key if you are using DAO but not if you are using ADO. If you are
using the relationships diagram to create your relationship, I don't know
what JET/Access will do but in my opinion, it would not be a bad idea to
first create any necessary indexes on this fields; otherwise you might end
up with a lot of missing indexes in your database and the performance will
drop like a rock.

 

[Robin]

Thank you for your response.
Because this is a "linked table" from Outlook, indexes cannot be set. (At
least not in Access) Am I missing something? Can an index or equivalent be
set in Outlook?

Thanks,
Robin

 

[Sylvain Lafontaine]

From reading your original post, it was my understanding that much of your
data was stored in Access tables and that the linked OL table was only
providing some kind of minimal information when you were populating your
form; ie., the query(ies) used to populate the form was working principally
against Access tables.

However, if this is not the case, then I'm sorry but I cannot help you any
further because I don't know much about OL; so I would suggest that you
repost your question in a newsgroup dedicated to OL; where you'll have a
greater chance of finding people knowing a lot more about OL than in this
newsgroup. Giving some more details about the structures of your tables and
the queries that you are using would also be a good idea.

In all cases, OL is neither a good database nor a good interface for
building forms against a real database. If an important part of your data
is coming from tables stored in OL, then don't expect to break any speed
record.

Good luck!

 

[Robin]

Thank you,

You are correct in that I'm only using OL for minimal information. Since
the management of an entire Client project takes place from within the Access
application, I was just trying to rig it so that when a user finds he has to
contact the client based on something in the Access application, he can
immediately get the contact information rather than opening/switching to OL,
opening the Public Folder, choosing the Client folder, and scrolling through
or "keying" to the client. (Approaching 1000 entries in client contacts).

I had orignially posted this on the OL site but it went unanswered. Thought
I'd try here because it seems there's always an interest in this link!

Thanks for your input,
Robin

 

[Sylvain Lafontaine]

What does it gives if you try to run these queries directly from Access,
with and without using (accessing) the OL linked table?

Generally, you should get a fast answer when not accessing the OL linked
table. After that, you could make a test from Access for retrieving data
only from the OL linked table to see if the problem is there. If there is
none, then access the Access tables + the OL linked table should be fast,
too, if you don't have any major problem with your design of the Access
tables + Relationships + Indexes in the first place.