Trying to link items to Access DB

  • Thread starter Thread starter Simon Shaw
  • Start date Start date
S

Simon Shaw

Dear Experts,

I am trying to experiment with keeping extended information about Outlook
items in an Access database, for a posisble training application. My first
test was to link a contact item to a table in an MDB. Already I seem to be
having difficulties :-(

In the example below, I am (successfully) getting the next available
autonumber ID from the access db, and then trying to assign it to a user
property of the contact item. But for some reason, the assignment does not
succeed, and no error is returned.

Any ideas?

TIA
Simon Shaw

<Code Starts>

Public Sub trainer_open()
Dim rs As DAO.Recordset
Dim myItem As Object
Dim Property As UserProperty
Dim ID As Long

Set db = OpenDatabase(strDBPath)
Set myItem = Application.ActiveInspector.CurrentItem

' this might be a new one - check to see if TrainerID already set
ID = 0
For Each Property In myItem.UserProperties
If Property.Name = "TrainerID" Then
ID = CInt(Property.Value)
End If
Next

If ID = 0 Then ' its new
Set rs = db.OpenRecordset("SELECT * FROM Trainer WHERE 1=0",
dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs.Update
rs.MoveFirst
myItem.UserProperties("TrainerID") = CStr(rs!TrainerID)
myItem.Save
End If

rs.Close
Set rs = Nothing
End Sub
 
You don't have any code to add the TrainerID property to the item if it's
not already present. Try this:

Set Property = myItem.UserProperties("TrainerID")
If Not Property Is Nothing Then
Set Property = myItem.UserProperties.Add "TrainerID", olText
Set rs = db.OpenRecordset("SELECT * FROM Trainer WHERE 1=0", _
dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs.Update
rs.MoveFirst
Property.Value = CStr(rs!TrainerID)
myItem.Save
End If
 
Automatic linking with Access doesn't support user defined fields and for a
contact item doesn't even support all standard fields. If I recall correctly
you only get about 45 fields from an Outlook contact item.

You will have to write code to do what you want. See
http://www.outlookcode.com/d/database.htm for more information.
 
Ken,
Sorry, but I think you missed the point. I _did_ write code, because I was
aware of the limiations of simply using linked tables in Access. I even
included my code in the posting, for your expert review. Perhaps I
shouldn't have used the words "link to Access" because you automatically
assumed I was using the link tables facility in Access.

Actually, I have just discovered the reason for my problem, which is that
you cannot programmatically assign values to userproperties unless there is
a field on the custom form which is bound to that property.

I hope this will be useful - it's perhaps a very basic point, but I didn't
know, so perhaps others might not.

Thanks
Simon
 
Great - much neater than my clumsy code.
I hadn't realised you needed to explicitly create the UserProperty first.
Many thanks, and I've just bought your book on Amazon.
My next question is....(see new thread) :-)
Simon
 
Back
Top