my programatic export of Calendar to Access has problems! Plus I'm a lousy programmer.

[krislikesmath]

I'm using this code to export a calendar to access and it works, but
there's some weird 'errors'.

A) not every calendar item was 'grabbed.' 2 fridays ago everything
was taken, but last friday several things were not added to the
database.

B) There seems to be no rhyme or reason to the order of what it
grabs. (maybe some other field i'm not looking at). Could I, (should
I?) sort the record set before grabbing?



Here's my limitations which I would love to know how to overcome. I
know these things suck and good programmers will chastize me. But we
have a fast computer and low amounts of data so I can be lazy (but i'd
rather find a more elegant solution).

1) Since there seems to be no order I'm stuck with just guessing where
to stop grabbing, and since there are 20,000 items in the calendar
going back to the late 90s, i can't grab them all. (i can, but it
takes alot longer than grabbing 900).

2) I delete everything at the start so I can get the 'freshest' data.
(someone might change a 2 week old calendar item).

3) A link to outlook through access doesn't grab the start time of the
calendar item (why it doesn't is beyond my guess, anyone who makes a
calendar program should expect users would want to know start time)

Here's the code, it takes parts from other people on the net so i
don't claim 100% credit:

Sub pushcalendartoaccess_beta()
Dim fld As Outlook.MAPIFolder
Dim appAccess As Access.Application
Dim objItems As Outlook.Items
Dim lngcount As Long
Dim debug1 As Date
Dim debug2 As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblCalendar")

Set fld = GetFolder("Public Folders/Favorites/Conyers Interviews")
If fld Is Nothing Then
GoTo ErrorHandlerExit
End If
If fld.DefaultItemType <> olAppointmentItem Then
MsgBox "Folder is not a calendar folder"
GoTo ErrorHandlerExit
End If
Debug.Print fld.FolderPath
lngcount = fld.Items.Count
Debug.Print lngcount
If lngcount = 0 Then
MsgBox "No appointments to export"
GoTo ErrorHandlerExit
Else
Debug.Print lngcount & " appointments to export"
End If

'this code deletes the database every time, so there's no duplicates
If rst.BOF And rst.EOF Then
Else
rst.MoveFirst
Do Until rst.EOF
rst.Delete
rst.MoveNext
Loop
End If

Set objItems = fld.Items
Dim i
For i = 1 To 170
' debug1 = fld.Items(i).Start
' debug2 = fld.Items(i).Subject
' Debug.Print debug1
' Debug.Print debug2
rst.AddNew
rst!Subject = fld.Items(i).Subject
rst!Date = fld.Items(i).Start
rst.Update
Next

Set fld = GetFolder("Public Folders/Favorites/Interviews")
Set objItems = fld.Items

For i = 2 To 890
' debug1 = fld.Items(i).Start
' debug2 = fld.Items(i).Subject
' Debug.Print debug1
' Debug.Print debug2
rst.AddNew
rst!Subject = fld.Items(i).Subject
rst!Date = fld.Items(i).Start
rst.Update
Next
rst.Close

'error handlers
End Sub

 

[Sue Mosher [MVP-Outlook]]

1) There's no order unless you set one by calling the MAPIFolder.Items.Sort method.

2) Consider filtering for a date range. See http://www.outlookcode.com/d/finddate.htm

3) The linked table method has major limitations, including the inability to show even all the reasonably important fields.

FYI, there is a newsgroup specifically for general Outlook programming issues "down the hall" at microsoft.public.outlook.program_vba or, via web interface, at http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.outlook.program_vba

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003

and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

 

[krislikesmath]

Oh, thanks, I didn't know of the sort methods. Nor how to filter. I
will better assign my posts next time.

Thanks for posting code, I don't think i used any of yours in the
above code (maybe getfolder() method was yours) but looking at your
code snipits allowed me to better understand what was going on and how
to change things to suit my needs.