Search Listbox vs Table Query

[PeterM]

I have a calendar form in my AC2003 database. I need to verify if the entry
in the access calendar is in their outlook calendar. During the load of the
access calendar for a month I:
1. load all outlook calendar entries into a listbox with start, end and
subject, this happens once per month, then
2. for each entry in the access calendar per month I perform a scan of the
outlook calendar listbox to see if a matching entry is found.

As the users outlook calendar grows, the listbox grows as well. It's now at
a point that it takes about 60 to go thru this process and display the access
calendar.

My question is: Should I change the outlook calendar listbox on the access
calendar form to a table? Using a table I would be able to issue one query
for each access date instead of searching thru 300-500 outlook calendar
listbox entries. Below is the code to search the outlook calendar listbox
(OutlookListbox) for a matching access calendar table (Appointments)

' process all entries in the current listbox
Dim CalendarIndex, calendarCTR As Integer
CalendarIndex = Me.OutlookCalendar.ListCount
For tmpCTR = 0 To tmpIndex - 1
' process all entries in Outlook Calendar
For calendarCTR = 0 To CalendarIndex
If tmp_date & " " & Format(Me("lb" & curBox).Column(0, tmpCTR),
"h:mm ampm") = _
Me.OutlookCalendar.Column(0, calendarCTR) Then
If Me("lb" & curBox).Column(1, tmpCTR) & " - " & Me("LB" &
curBox).Column(3, tmpCTR) = _
Me.OutlookCalendar.Column(2, calendarCTR) Then
' if the start, end and subject match the current day,
set the listbox calendar
' color to the windows theme color and set the counters,
indexes to max to end the search
Me("lb" & curBox).BackColor = OutlookThemeColor
tmpCTR = Me.OutlookCalendar.ListCount
calendarCTR = CalendarIndex
End If
End If
Next calendarCTR
Next tmpCTR

The access calendar form performs the above code for each entry found per
month.

Thanks for your advice, have a great weekend.

 

[Daryl S]

Peter -

My thinking is that a table would be faster. If you have a split database
for multiple users, this would be one case where the table should be in the
front-end. You can index the table so any searches would be fast.