Synchronize with Access DB

J

Jonathan Brown

Okay, stupid question. I've got the following code. I sort of ripped it off
a code snippet I found in an Outlook programming book. I've modified it to
apply to my situation. I'd like to run it by all of you out there to see if
I've missed anything. One problem I have is that I can't figure out how to
get the code to run with my OnClick event of my Synchronize button.

My objective is to synchronize my contact management database with outlook.
I've made a new form in my access database with a Synchronize button. I
believe the code I have should create a new contact folder called "FSR
Tracking Contacts" and then create a new contact item for each record in my
database.

Here's what I've got:


Function CheckNull(s As Variant) As Variant
On Error Resume Next
If IsNull(s) Then
CheckNull = ""
Else
CheckNull = s
End If
End Function

Private Sub Synchronize(objFolder As MAPIFolder)

Dim cnnDB As ADODB.Connection
Dim rstCust As ADODB.Recordset
Dim oContactsFolder As Outlook.MAPIFolder
Dim oContact As Outlook.ContactItem
Dim oContactLinks As Outlook.Links
On Error Resume Next
Me.lblSyncStatus.Caption = "Removing existing items, please wait..."
DoEvents
Set cnnDB = New ADODB.Connection
Set oContactsFolder = objFolder.Folders("FSR Tracking Contacts")
'Delete Existing Items
Do Until oContactsFolder.Items.Count = 0
oContactsFolder.Items.Remove (1)
Loop
On Error GoTo CreateContacts_Error
'Create New Contact Items
Set rstCust = cnnDB.Execute("Select * from qrySynchronize")
Do Until rstCust.EOF
Set oContact = oContactsFolder.Items.Add("Contact")
'CheckNull replaces database Nulls with empty string
oContact.FirstName = CheckNull(rstCust!FirstName)
oContact.LastName = CheckNull(rstCust!LastName)
oContact.BusinessTelephoneNumber = CheckNull(rstCust!WPhone)
oContact.HomeTelephoneNumber = CheckNull(rstCust!HPhone)
oContact.MobileTelephoneNumber = CheckNull(rstCust!CPhone)
oContact.PagerNumber = CheckNull(rstCust!Pager)
oContact.Birthday = CheckNull(rstCust!Bdate)
oContact.BusinessFaxNumber = CheckNull(rstCust!Fax)
oContact.Email1Address = CheckNull(rstCust!Email)
oContact.HomeAddress = CheckNull(rstCust!HAddress)
oContact.HomeAddressCity = CheckNull(rstCust!HCity)
oContact.HomeAddressState = CheckNull(rstCust!HState)
oContact.HomeAddressPostalCode = CheckNull(rstCust!HZip)
oContact.OtherAddress = CheckNull(rstCust!PAddress)
oContact.OtherAddressCity = CheckNull(rstCust!PCity)
oContact.OtherAddressState = CheckNull(rstCust!PState)
oContact.OtherAddressPostalCode = CheckNull(rstCust!PZip)
oContact.FileAs = CheckNull(rstCust!FileAs)
oContact.FullName = CheckNull(rstCust!FullName)
oContact.JobTitle = CheckNull(rstCust!Title)
oContact.Save
rstCust.MoveNext
Me.lblSyncStatus.Caption = "Loading " & CheckNull(rstCust!FullName)
DoEvents
Loop

CreateContacts_Exit:
On Error Resume Next
rstCust.Close
cnnDB.Close
Exit Sub

CreateContacts_Error:
MsgBox "Error#: " & Err.Number & vbCr & Err.Description, vbInformation
Resume CreateContacts_Exit
End Sub


So if everything here looks good then how do I run this code with my
cmdSynchronize OnClick event?
 
R

Radek Mrkvicka

http://www.geniusatwork.nl/geniusconnect_ms.asp

Jonathan Brown said:
Okay, stupid question. I've got the following code. I sort of ripped it
off
a code snippet I found in an Outlook programming book. I've modified it
to
apply to my situation. I'd like to run it by all of you out there to see
if
I've missed anything. One problem I have is that I can't figure out how
to
get the code to run with my OnClick event of my Synchronize button.

My objective is to synchronize my contact management database with
outlook.
I've made a new form in my access database with a Synchronize button. I
believe the code I have should create a new contact folder called "FSR
Tracking Contacts" and then create a new contact item for each record in
my
database.

Here's what I've got:


Function CheckNull(s As Variant) As Variant
On Error Resume Next
If IsNull(s) Then
CheckNull = ""
Else
CheckNull = s
End If
End Function

Private Sub Synchronize(objFolder As MAPIFolder)

Dim cnnDB As ADODB.Connection
Dim rstCust As ADODB.Recordset
Dim oContactsFolder As Outlook.MAPIFolder
Dim oContact As Outlook.ContactItem
Dim oContactLinks As Outlook.Links
On Error Resume Next
Me.lblSyncStatus.Caption = "Removing existing items, please wait..."
DoEvents
Set cnnDB = New ADODB.Connection
Set oContactsFolder = objFolder.Folders("FSR Tracking Contacts")
'Delete Existing Items
Do Until oContactsFolder.Items.Count = 0
oContactsFolder.Items.Remove (1)
Loop
On Error GoTo CreateContacts_Error
'Create New Contact Items
Set rstCust = cnnDB.Execute("Select * from qrySynchronize")
Do Until rstCust.EOF
Set oContact = oContactsFolder.Items.Add("Contact")
'CheckNull replaces database Nulls with empty string
oContact.FirstName = CheckNull(rstCust!FirstName)
oContact.LastName = CheckNull(rstCust!LastName)
oContact.BusinessTelephoneNumber = CheckNull(rstCust!WPhone)
oContact.HomeTelephoneNumber = CheckNull(rstCust!HPhone)
oContact.MobileTelephoneNumber = CheckNull(rstCust!CPhone)
oContact.PagerNumber = CheckNull(rstCust!Pager)
oContact.Birthday = CheckNull(rstCust!Bdate)
oContact.BusinessFaxNumber = CheckNull(rstCust!Fax)
oContact.Email1Address = CheckNull(rstCust!Email)
oContact.HomeAddress = CheckNull(rstCust!HAddress)
oContact.HomeAddressCity = CheckNull(rstCust!HCity)
oContact.HomeAddressState = CheckNull(rstCust!HState)
oContact.HomeAddressPostalCode = CheckNull(rstCust!HZip)
oContact.OtherAddress = CheckNull(rstCust!PAddress)
oContact.OtherAddressCity = CheckNull(rstCust!PCity)
oContact.OtherAddressState = CheckNull(rstCust!PState)
oContact.OtherAddressPostalCode = CheckNull(rstCust!PZip)
oContact.FileAs = CheckNull(rstCust!FileAs)
oContact.FullName = CheckNull(rstCust!FullName)
oContact.JobTitle = CheckNull(rstCust!Title)
oContact.Save
rstCust.MoveNext
Me.lblSyncStatus.Caption = "Loading " & CheckNull(rstCust!FullName)
DoEvents
Loop

CreateContacts_Exit:
On Error Resume Next
rstCust.Close
cnnDB.Close
Exit Sub

CreateContacts_Error:
MsgBox "Error#: " & Err.Number & vbCr & Err.Description, vbInformation
Resume CreateContacts_Exit
End Sub


So if everything here looks good then how do I run this code with my
cmdSynchronize OnClick event?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Synchronize with Outlook 4
Working outlook via vba code with xlsx - error 0
exporting contacts as vcards with Visual Basic. 1
How to add a user defined field to Redemption RDOContactItem object? 3
export excel data to access table using ADO 0
ItemAdd and FolderAdd events problem with Public Folders 0
Adding table into existing access database via ADO 0
Exporting data with vba from Excel to an Access 2007 db with password 8

Top