Synchronize with Access DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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...
 
Back
Top