Hello all

I have a Outlook macro communicating with MS Access.

I've set a reference to Access and am using
"CurrentProject.Connection.Execute strSQL" to post a record based on email
data.

I have not referenced a new instance of Access, I just have the appropriate
Access database running in the background.

Is this good practice? I realize I must have Access open for this Outlook
macro to properly operate as it stands now.

What is the downsided of this approach?


Regards
Dale

You don't need to automate Access at all. There are several UI-independant
API's that can read/write to an Access .mdb file, such as DAO or ADO. See my
code below which exports an Outlook Calendar to an Access database. You can
easily customize it to output e-mail items instead.

'--------------------------------------------------------------------------Â*-------------
' Procedure : ExportCalendarToDatabase
' DateTime : 11/09/2006 19:44
' Author : Eric Legault [MVP - Outlook]
' Purpose : Exports Outlook Calendar items to an Access database.
' : Requires Reference to Microsoft ActiveX Data Objects 2.X
Library
' : Assumes existence of these fields in a table named 'Calendar':
' : Subject (Text)
' : Contents (Memo)
' : Start (Date/Time)
' : End (Date/Time)
'
' Example Call:
' ExportCalendarToDatabase "C:\Test\db1.mdb"
'--------------------------------------------------------------------------Â*-------------


Sub ExportCalendarToDatabase(PathToAccessDB As String)
On Error GoTo ExportCalendarToDatabase_Error


Dim objFolder As Outlook.MAPIFolder, objItems As Outlook.Items
Dim objAppt As Outlook.AppointmentItem, objMessageObj As Object
Dim conThis As ADODB.Connection, rstThis As ADODB.Recordset


Set conThis = New ADODB.Connection
conThis.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
PathToAccessDB & ";Persist Security Info=False"
Set rstThis = New ADODB.Recordset


rstThis.Open "Calendar", conThis, adOpenDynamic, adLockOptimistic,
adCmdTable


MsgBox "Please select the Calendar that you want to export to Access
with the next dialog..." _
, vbOKOnly + vbInformation, "Export Calendar"


Set objFolder = Application.GetNamespace("MAPI").PickFolder
If objFolder.DefaultItemType olAppointmentItem Then
MsgBox "Invalid folder. Export aborted.", vbOKOnly + vbExclamation,
"Invalid Folder Type"
GoTo Exitt:
End If


Set objItems = objFolder.Items
For Each objMessageObj In objItems
If objMessageObj.Class = olAppointment Then
Set objAppt = objMessageObj


'SAVE TO ACCESS DATABASE
rstThis.AddNew
rstThis("Subject").Value = objAppt.Subject
'If the Body field is a memo data type, ensure that zero length
strings are allowed
If objAppt.Body "" Then rstThis("Contents").Value =
objAppt.Body
rstThis("Start").Value = objAppt.Start
rstThis("End").Value = objAppt.End
rstThis.UPdate
End If
Next


Exitt:
On Error Resume Next
Set rstThis = Nothing
conThis.Close
Set objFolder = Nothing
Set objItems = Nothing
Set objAppt = Nothing
Set objMessageObj = Nothing


On Error GoTo 0
Exit Sub


ExportCalendarToDatabase_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ExportCalendarToDatabase"
Resume Next
End Sub


--
Eric Legault (Outlook MVP, MCDBA, MCTS: Messaging & Collaboration)
Try Picture Attachments Wizard for Outlook:
http://www.collaborativeinnovations.ca
Blog: http://blogs.officezealot.com/legault/


"Dale" wrote:

Hello all

I have a Outlook macro communicating with MS Access.

I've set a reference to Access and am using
"CurrentProject.Connection.Execute strSQL" to post a record based on email
data.

I have not referenced a new instance of Access, I just have the appropriate
Access database running in the background.

Is this good practice? I realize I must have Access open for this Outlook
macro to properly operate as it stands now.

What is the downsided of this approach?


Regards
Dale