How to save ADO Recordet as Access Table

[Guest]

I am extremely sketchy on databases, so I may well be doing this all wrong. I
hope someone can give me a little guidance here.

I have a lot of data collected in Outlook folders. As you may know,
Outlook's reporting facilities are non-existent. My plan is to extract the
Outlook data to an Access table and then run reports on the Access table.

I have, through reading around on the web, worked out how to extract my
Outlook data using ADO into an open ADO recordset. The next step is to assign
the data to an Access table. I get the feeling that doing this is so basic,
it isn't worth a database expert commenting on. AT the moment, for me, it is
an insurmountable problem.

My code so far is:

Sub OpenExchange_Folder()

Dim objOLApp As Outlook.Application
Dim objOLExp As Outlook.Explorer
Dim ADOConn As ADODB.Connection
Dim ADORS As ADODB.Recordset
Dim strConn As String
Const strMinFolderPath = "Mailbox - Vaughan Davies\Projects\Minutes"
Dim objMinuteFolder As Outlook.MAPIFolder
Dim objView As Outlook.View
Dim cnnAccess As ADODB.Connection

Set objOLApp = New Outlook.Application
Set objOLExp = objOLApp.ActiveExplorer
With objOLExp
Set .CurrentFolder = GetMapiFolder(strMinFolderPath) 'Routine to
return

'MAPI folder
.CurrentView = "Transfer"
End With

Set ADOConn = New ADODB.Connection
With ADOConn
.Provider = "Microsoft.JET.OLEDB.4.0"
.ConnectionString = "Exchange 4.0;" _
& "MAPILEVEL=Mailbox - Vaughan Davies|Projects;" _
& "Profile=SBSOutlook;" _
& "TABLETYPE=0;" _
& "DATABASE=C:\WINDOWS\TEMP;"
.Open
End With
Set ADORS = New ADODB.Recordset
With ADORS
.Open "Select * from Minutes", ADOConn, daOpenForwardOnly, _
adLockBatchOptimistic
End With

{
This is where I need the help
}

ADORS.Close
ADOConn.Close
Set ADORS = Nothing
Set ADOConn = Nothing
End Sub


Please could anyone give me any pointers?

Many thanks in advance

Vaughan

 

[SA]

Vaughan:

You are taking the hard way around this. In later versions of Access, you
can actually link to your Exchange / Outlook folders by going to File -> Get
External Data -> Link and choosing Exchange() as the file type. From
there, you can then run simply queries to import data into tables. Using
this method not all fields are available (e.g. secondary e-mail addresses in
contacts) but most of the important stuff is there.

If you don't want to use that method, there's no real easy way to take an
ADO recordset and move it to a table in code. In essence you can either a.)
loop through the recordset's fields and for each row, add the data to a
second recordset opened on your target table, or b.) Save the recordset to
XML and then use a query linked to that source to append the data to a
table.

 

[Guest]

Thanks for replying Steve.

Sadly, a lot of the data on Exchange is stored in custom fields which are
not available using the linked table approach.

I took option (a) of your advice and looped through the fields and records,
and that has worked admirably.

Thanks for your help

Vaughan