But that's not neccessary. By creating UserDefinded Properties in
Outlook, you can add any information that you need including the key
value for ANY Access Record. Using VBA and the Outlook Object Model it
is possible to create any Outlook Item you can think of (MailItem,
AppointmentItem, TaskItem, Contacts). The first three function reside in
Access are the the ones that I use to manage AppointmentItems in my
Outlook calendar that represent vehicle reservations. The AccessDB is
the system of record, Outlook is there just to give a visual display of
them and to allow me to sync them to my PPC. When the reservation is
updated in Access, the information is reflected in Outlook. Actually, I
delete any items associated with the Access record and create a new one.
Prior to implementing this, I did have a function that took the
AppointmentItem and simply updated the information by grabbing it with
the recordId from Access. Now, on the Outlook side of things, the code
further down reaches out to Access and updates the record accordingly.
This allows me to open up the Reservation in Outlook (using a custom
form), change the date/time and have the mod cascade back to Outlook.
The code also allows me to drag and drop the appointment item on my
calendar and have the mod update to Access as well. (Its SOOO Coool!)
Anyways, my point is that I think that it is possible to meet the
underlying BUSINESS NEED by understanding the possibilities that already
exist thanks to Billy up in Redmond. At any rate, with some
imaginaation, GOOGLING, and knowledge of the both the Access & Outlook
Object Models you my find that you can do what you want. You would use
Access to create the CONTACT and update it if neccessary. Outlook would
reach out to Access and update the CONTACT record if the user updated
the record in Outlook. (Pardon the soapbox, but I'm quite proud of the
work that I've done to integrate the two and would like to seem others
doing the same) Sidepoint: You won't need to LINK to your Outlook *.pst
file since the Access/Outlook interface ONLY occurs when the Item is
created or updated which would still keep the records in sync.
[Stepping off of the Soapbox]
David H.
-----------ACCESS FUNCTIONS/SUBS---------------------------
Function createOutlookAppointmentFromId(lngTransportId As Long, frm As
Variant)
Dim objOutlook As Outlook.Application
Dim newAppt As Outlook.AppointmentItem
Dim strLocation As String
Dim strWhereStatement As String
Dim lngOrgKey As Long
Dim lngDestKey As Long
Dim lngPrimaryPassengerId As Long
Dim strPrimaryPassengerFirstName As String
Dim strPrimaryPassengerLastName As String
Dim i As Integer
DoCmd.Hourglass (True)
If IsNull(frm) = False Then
frm!txtAdvisory = "Looking up appointment details from database"
frm.Repaint
End If
If IsNull(frm) = False Then
frm!txtAdvisory = "Accessing Outlook"
frm.Repaint
End If
Set objOutlook = CreateObject("Outlook.application")
Set newAppt = objOutlook.CreateItem(olAppointmentItem)
newAppt.UserProperties.Add "dbAccessID", olNumber
newAppt.UserProperties.Add "dbLastModified", olDateTime
newAppt.UserProperties.Add "dbStatus", olText
If IsNull(frm) = False Then
frm![txtAdvisory] = "Creating new appointment"
frm.Repaint
End If
With newAppt
.Start = dteDate & " " & dteTimeScheduled
.End = dteDate & " " & DateAdd("h", 1, CDate(dteTimeScheduled))
.Subject = strPrimaryPassenger
.Location = strLocation
.UserProperties(1) = lngTransportId
.UserProperties(2) = Now
.UserProperties(3) = strStatusDescription
.Body = getBodyText(lngTransportId)
.BusyStatus = olBusy
.Categories = "Reservations"
.MessageClass = "IPM.Appointment.Reservations"
.Save
createOutlookAppointmentFromId = newAppt.EntryID
End With
On Error GoTo 0
If IsNull(frm) = False Then
frm![txtAdvisory] = "New appointment created"
frm.Repaint
End If
If Err.Number <> 0 Then createOutlookAppointmentFromId = Null
DoCmd.Hourglass (False)
Set newAppt = Nothing
Set objOutlook = Nothing
If IsNull(frm) = False Then
frm![txtAdvisory] = ""
frm.Repaint
End If
End Function
Function changeOutlookAppointmentFromId(lngTransportId As Long, frm As
Variant)
Select Case deleteOutlookAppointmentByTransportId(lngTransportId, Null)
Case 0, -1
changeOutlookAppointmentFromId =
createOutlookAppointmentFromId(lngTransportId, Null)
Case Else
changeOutlookAppointmentFromId = Null
End Select
End Function
Function deleteOutlookAppointmentByTransportId(lngTransportId As Long,
frm As Variant)
Dim objOutlook As Outlook.Application
Dim nms As Outlook.NameSpace
Dim targetCalendar As Outlook.MAPIFolder
Dim targetItems As Outlook.Items
Dim i As Integer
Dim aOutlookEntryIds()
Dim appointmentCount As Integer
Dim targetAppointment As Outlook.AppointmentItem
Dim strFilter As String
Set objOutlook = CreateObject("Outlook.application")
Set nms = objOutlook.GetNamespace("MAPI")
Set targetCalendar = nms.GetDefaultFolder(olFolderCalendar)
strFilter = "[dbAccessId]=" & Chr(34) & lngTransportId & Chr(34)
Set targetItems = targetCalendar.Items.Restrict(strFilter)
ReDim aOutlookEntryIds(targetItems.Count)
For i = 1 To targetItems.Count
Debug.Print i
aOutlookEntryIds(i) = targetItems(i).EntryID
Next i
Select Case targetItems.Count
Case 0
Debug.Print "AppointmentItem not found."
deleteOutlookAppointmentByTransportId = 0
Case Else
Debug.Print targetItems.Count & " AppointmentItem(s) found.
Deleting all instances."
For i = 1 To targetItems.Count
Set targetAppointment =
nms.GetItemFromID(aOutlookEntryIds(i))
Debug.Print targetAppointment.UserProperties(1),
targetAppointment.Start, targetAppointment.Subject
targetAppointment.Delete
Debug.Print "Appoint ID: " & aOutlookEntryIds(i) & "
Deleted"
Debug.Print
Next i
deleteOutlookAppointmentByTransportId = -1
End Select
Set targetItems = Nothing
Set targetCalendar = Nothing
Set nms = Nothing
Set objOutlook = Nothing
End Function
-------------OUTLOOK FUNCTIONS/SUBS------------------------------
Dim myOlApp As New Outlook.Application
Public WithEvents myOlItems As Outlook.Items
Public Sub Application_Startup()
Call Initialize_handler
End Sub
Public Sub Initialize_handler()
Set myOlItems =
myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar).Items
Debug.Print "Initialize_Handler"
End Sub
Private Sub myOlItems_ItemChange(ByVal Item As Object)
Dim objAccess As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
DoCmd.Hourglass True
If Item.Class = olAppointment And Item.MessageClass =
"IPM.Appointment.Reservations" Then
If IsNull(Item.UserProperties("dbAccessId")) = False Then
Set objAccess = CreateObject("Access.Application")
Set db = objAccess.DBEngine.OpenDatabase("C:\Documents and
Settings\dch3\My Documents\Willard Madison\Data\Access\WMS FrontEnd
2005.mdb")
Set rs = db.OpenRecordset("SELECT * FROM tblTransports
WHERE lngTransportID = " & Item.UserProperties("dbAccessID") & ";")
If Not rs.EOF Then
rs.Edit
rs.Fields("dteDate") = FormatDateTime(Item.Start,
vbShortDate)
rs.Fields("dteTimeScheduled") =
FormatDateTime(Item.Start, vbShortTime)
rs.Update
MsgBox ("Transport #" &
Item.UserProperties("dbAccessID") & " updated.")
Else
MsgBox ("Unable to update Transport #" &
Item.UserProperties("dbAccessID") & ". Record may have been deleted.")
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Set objAccess = Nothing
End If
End If
DoCmd.Hourglass False
End Sub
I was looking for the same information as olsonsxq. I have made the link to
Outlook, but it is so incredible slow that it is mostly unusable. My contact
folder is 1100+ names and about to triple. Any ideas on speeding it up?
Running 1600mhz 512mb. It seems like it is a live link that is constantly
refreshing itself.
Any ideas
Thanks
:
In order to have a complete database, the information that is used in
Outlook
and Business Contact Manager must be duplicated in the Access Database
(Names, Addresses, E-mail, etc.). It would be helpful to be able to query
the tables for Outlook to eliminate the need of entering the same info in
two
different places.
File | Get External Data | Link Tables
Select 'Outlook' or 'Exchange' as the type of datasource
(depending on what exactly you are trying to do)
I just linked to my contacts list and got these fields:
FirstLastTitleCompanyDepartmentOfficePost Office
BoxAddressCityStateZip/Postal CodeCountryPhoneMobile PhonePager PhoneHome2
PhoneAssistant Phone NumberBusiness FaxHome FaxOther FaxTelex NumberDisplay
nameEmail TypeEmail AddressAccountAssistantSend Rich TextPrimaryFile AsHome
AddressBusiness AddressOther AddressJournalWeb PageBusiness Address
StreetBusiness Address CityBusiness Address StateBusiness Address Postal
CodeBusiness Address CountryBusiness Address PO BoxUser Field 1User Field
2User Field 3User Field 4
(david)
