Outlook Schema

[Picine Karppehd]

Is there a place on the web that describes the schema for Outlook data,
especially Journal and Calendar data?

I am trying to write some reports on the Office 2003 Journal file using the
Crystal Reports that come with Visual Studio.Net and it shows some of the
Journal fields but not all. For example, there is a field on the Journal
form called Company but the Company field does not show on the Crystal
Reports Field Explorer. Am I supposed to look somewhere else other than the
Journal file to find the Company field? Is there a join I can do to another
files?

 

[Jim Vierra [661815]]

I found that using the ADO connector for Outlook from MSAccess was a good
way to view the schema. I believe Crystal uses this but doen't display it
in a verry friendly way. Loading Outlook into Access lays it out as simple
tables.

 

[Picine Karppehd]

This is really good info. However, I am pretty ignorant about using the "ADO
connector for Outlook from MSAccess". I've used ADO and ADO.Net to connect
to SQLServer dozens, if not hundreds, of times but I am a novice with Access.
Could you please lay out the connect string to connect to the Outlook tables?

 

[Jim Vierra [661815]]

The simple way is to open access and link table - select Outlook() in the
type box and it will let you select a profile and then it will show you all
of the items in the profile. Browse them and add the ones you want schema
info for. If yuo have the Outlook documentation utility you can print a
document of schema info.

 

[Picine Karppehd]

Thank you, Jim, for this advice as I was able to open the tables and look at
the data. However, I still have questions. I open the Journal table and see
the data but not all the data on the journal form is in the journal table.
For example, the company name field on the form is not in the table. I would
like to find a schema that shows where the data on the form is stored and
what the links are between the tables. Is there such a schema?

 

[Jim Vierra [661815]]

Picine - Yes - no company and no other things too.

As far as I know there is no schema for Outlook or Echange. They do not use "relational" technology or "ISAM" tecnology but rather use Microsofts own storage engine API I believe. The driver for Outlook data access was written a long time ago and probably has not been updated regularly except to get it to work. If you need more detail then a macro that would generate a CSV file could get you the data in a format that would be easy to use with Access, Crystal, Excel or a SQL databas reporting tool. This would probably be the fastest way to get at the data. If you need to do this in a more formal way then you would have to build a COM add-in or create a custom OLEDB provider.

Here is a connect string to use Jet directly

Connecting to an Outlook 2000 personal mail box using the JET OLE DB Provider: (By J. Cardinal)
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;MAPILEVEL=;DATABASE=C:\\Temp\\;"Replace c:\temp with any temporary folder. It will create a schema file in that folder when you open it which shows all the fields available. Blank MAPILEVEL indicates top level of folders).

I haven't tried this for awhile so it may need some tweeking.

Here is sample code to use ADO

Sub test()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;MAPILEVEL=;Database=c:\temp;"

For Each tbl In cat.Tables
Debug.Print "FOLDERS:" & tbl.Name
Next tbl

Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"Exchange 4.0;MAPILEVEL=;TABLETYPE=1;Database=c:\temp;"

For Each tbl In cat.Tables
Debug.Print "ADDRESS BOOKS:" & tbl.Name
Next tbl

Set cat.ActiveConnection = Nothing
Set cat = Nothing

End Sub

I don't know if this technique brings back company field. There is little documentation.
seearticle with more details at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00h12.asp