Search Listbox vs Table Query
in [General]
|
Prev: Change default export to Excel location - Access 2007
Next: Can't open my Access files after conversion from Access 97 to Access 2003
From: PeterM on 9 Apr 2010 15:59 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.
From: Daryl S on 12 Apr 2010 13:54
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. -- Daryl S "PeterM" wrote: > 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. > |