Question Outlook Global Address List

[April15Hater]

I have a table linked to our Outlook Exchange Global Address List. This list is spread across many locations worldwide and I'm not quite sure where the actual server is housed.

My problem is that there are over 64,000 records in the linked table, and it is being brought over as 255 char strings, so you can imagine between the excessively large fields and the remote property of the linked table there is some unbearable delay when loading the table. I even tried kicking it into a query, but then realized that good ol' access still has to pull the whole table and process it.

I cannot edit the table as it is linked, and there are a lot of records I simply don't need such as distribution lists and inactive employees.

Do I have any options to speed this up?

 

[spikepl]

In the Outlook Object Model you have ContactItem objects http://msdn.microsoft.com/en-us/library/ms268893.aspx or specifically VBA here http://msdn.microsoft.com/en-us/library/bb176619.aspx (for O2007) , but the first reference is good for seeing some VB (not VBA code, but similar) to see what it takes.

For a huge table a search might be slow, unless it can be executed with Instant Search (an add on for XP, built-in into Vista/W7 but not sure).

You can search Outlook from within Access, but Outlook is likely (but not guaranteed) to wail like crazy (Outlook safety features, not always easy to bypass, so you have been warned). To arrive at the 100 lines of code or so that you'll need, it will take you quite a while, if you are not familiar with the Outlook model..

 

[April15Hater]

No warning needed, Outlook's security is tight and justifiable so. But I'm avoiding that route at all costs. I looked at that VB code, but I'm terrible at converting it down to VBA.

The query is unbearable, I mean like an hour minimum to process, then everytime I nav the query, it takes forever to process that too.

Thinking I should do an overnight batch update to run a maketable query properly formatted to accommodate the fields, and filter out unneeded fields and records. It'll be one day behind, but I think it might be the only pragmatic way without spending hours on code or waiting on queries.

 

[spikepl]

Sounds very sensible - I'd do that myself

 

[April15Hater]

Yeah, but we all know there's that nice warm fuzzy feeling about live data that a batch just doesn't provide. I just need to learn my way around Outlook VBA one of these days Thanks for the input though!

 

[darbid]

Linking a Global Address list is like taking a bucket and walking all the way to a well, filling up the bucket and walking all the way back, to only have a glass of water and to throw the rest of the water away.

What exactly does a user need the address list for? How often does he use it?

 

[April15Hater]

Nice analogy.

Its for a security review across several accounting software systems.

Want to go through list of user's system access and then pull the user supervisor's email address (or just name for that matter since it's all internal anyways) so we can email the manager a list of employees and the respective access to said accounting systems.

 

[darbid]

So a user of of your database - searches a system or table for supervisors and he gets an identifier which he needs to get a name and email address. The user then wants to email this supervisor.

IS that it?

So what is the identifier that the user gets? and what field of the Global Address list needs to be searched?

 

[darbid]

Before you get too excited, You are going to have to do some coding in VBA as I do not have a fully written example nor have I done this. I actually thought it would be easy but it is not as easy as I thought. You will have to use a combination of the Outlook Object Model and CDO depending on what you are searching for. e.g. if you can nail the item or contact exactly then this would work (meaning there is no multiple people)

http://www.codeforexcelandoutlook.com/blog/2010/09/link-excel-cells-to-outlook-data-fields/

If we need to do a more extensive search and for example return an array of people to allow the user to choose the right one then it gets more complicated.

 

[April15Hater]

So a user of of your database - searches a system or table for supervisors and he gets an identifier which he needs to get a name and email address. The user then wants to email this supervisor.

Almost. The accounting system is going to give us an export of the users and thier permissions. I need to email the users' supervisors to verify the permissions in place are still appropriate or if they need to be changed/removed. It's basically an entitlement review.

To add one more layer of complexity, there is no unique identifier connecting the outlook contact name to the accounting system name.

Complexity layer #2 this is for 5 different software systems- all of which may have different names amongst those systems.

 

[darbid]

ok let me try it another away. What exactly will you get from the accounting system? What exactly do you search for i.e. the field in the Global Address list?

 

[April15Hater]

-Last name
-First name

Any multiples or not founds are left blank and looked up manually.