Fast Dump to Access

  • Thread starter Thread starter Ryan.Chowdhury
  • Start date Start date
R

Ryan.Chowdhury

After searching the newsgroups, I came across this link to grab PUBLIC
Contact folder items and place them in Access:

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

It works, but gets exponentially slower as the routine crawls through
the public contacts folder. Is there a fix or better code to bring in
my 3,000 - ish contacts to Access? or even better, straight to SQL?

Ryan
 
I should clarify the speed issue. If I set the counter from 1 to 500
it takes about 10 seconds. If I set the counter from 501 to 1000 it
takes 20 seconds. If I set the counter from 2,000 to 2,500 it takes
minutes. I haven't even tried 1 to (TOTAL NUMBER). I imagine I would
need a new hip at that point.
 
Am 29 Oct 2005 07:57:49 -0700 schrieb (e-mail address removed):

Ryan, you should use CDO instead of the Outlook object model. An example for
what I´ve tested once on any machine: 1,000 Contacts, OOM needed 4 seconds,
CDO less than a half.
 
That is an improvement. Michael, could you point me to some sample CDO
code. I took a peek at the CDOlive site. I'm not much of a
programmer. I'm really still in a copy and modify phase.

Ryan
 
If the speed slows as you're increasing the number of records or range,
my cursory opinion is that the issue deals directly with the number of
contacts that you're dealing with. In short, you probably won't be able
to speed up the code. Plus, I wouldn't expect that you'll be running the
code on any regular basis.
 
Of course I would hit SEND too soon. If you're grabbing the Contacts and
dropping them in an Access DB, I would be more inclinded to recommend
that you view Access as your system of record and use VBA code to take
the record from Access and create the ContactItem from there
(Access-to-Outlook) as opposed to going Outlook-to-Access.
 
Am 30 Oct 2005 05:21:20 -0800 schrieb (e-mail address removed):

Ryan, here is a sample. Use the Outlook code to get the folder you want to
start with. Call Logon to logon to a MAPI session and use its result
together with the Outlook folder to get the appropriate MAPI folder.

Then pass the contact folder to LoopItems. The sample prints all contact´s
Fullnames.

On http://www.cdolive.com/cdo10.htm is a list of the most used property
tags. (Scroll the site down to the Contact Properties.)

Another tip: If possible use ADO and a disconnected clientside Recordset.
Add all data and write it into the DB at once.

Public Sub LoopItems(oFld As MAPI.Folder)
Dim colItems As MAPI.Messages
Dim oMsg As MAPI.Message
Dim colFields As MAPI.Fields
Dim oField As MAPI.Field

Set colItems = oFld.Messages
For Each oMsg In colItems
Set colFields = oMsg.Fields
Set oField = colFields(CdoPR_DISPLAY_NAME)
Debug.Print "Fullname=" & oField.Value
Next
End Sub

Public Function LogOn() As MAPI.Session
On Error Resume Next
Dim oSess As MAPI.Session
Set oSess = CreateObject("MAPI.Session")
Set oSess.MAPIOBJECT = Application.Session.MAPIOBJECT
Set LogOn = oSess
End Function

Public Function GetFolder(oFolder As Outlook.MAPIFolder, _
oSess As MAPI.Session _
) As MAPI.Folder
Set GetFolder = oSess.GetFolder(oFolder.EntryID, oFolder.StoreID)
End Function
 
I'm trying. Should some of those variables be publicly declared?

Sub test()

Dim rst As DAO.Recordset
Dim ol As New Outlook.Application
Dim olns As Outlook.NameSpace
Dim cf As Outlook.MAPIFolder
Dim c As Outlook.ContactItem
Dim objItems As Outlook.Items
Dim Prop As Outlook.UserProperty

Set rst = CurrentDb.OpenRecordset("tbl_Contacts")
Set olns = ol.GetNamespace("MAPI")
Set cf = olns.Folders.Item("Public Folders")
Set cf = cf.Folders.Item("All Public Folders")
Set cf = cf.Folders.Item("CompanyName")
Set cf = cf.Folders.Item("Contacts")

'This gets the Mapi.Folder I want

Call LogOn
'There was an error here. VB could not compile this line in the Logon
procedure:
'Set oSess.MAPIOBJECT = 'Application.Session.MAPIOBJECT. It did not
recognize
'Session. I commented that line out and it proceeded

Call LoopItems(cf)
'Here I get a type mismatch error.

I think I'm missing some fundamental logic in your suggestion.
 
Ok, so the cf I produce from the microsoft site gives me an Outlook
MAPI folder. I need to "convert" that, yes? So, I use GetFolder which
requires an Outlook MAPI folder and a session? which is why I need the
logon procedure...I think.

Unfortunately, the Logon procedure doesn't seem to want to compile. Do
I need references beyond CDO and Oulook object libraries?
 
Am 31 Oct 2005 07:23:15 -0800 schrieb (e-mail address removed):

Sorry :-)

It would compile if you first open the Object Browser, right click in it
anywhere and tick "Show hidden member" (or something similar).

But probably better is to replace the function by this one:

Public Function LogOn() As MAPI.Session
On Error Resume Next
Dim oSess As MAPI.Session
Set oSess = CreateObject("MAPI.Session")
oSess.LogOn , , False, False, , True
Set LogOn = oSess
End Function
 
Back
Top