Solved

Fastest most efficient way to populate an MS Access 2007 Listbox control

Posted on 2014-04-15
16
799 Views
Last Modified: 2014-04-20
Hello all,
I have a form with a listbox on it that is currently being populated by a somewhat kludgy SQL statement as it's rowsource.  The problem is that it's kind of slow loading.  This list box will also be filtered by a wildcard textbox control as the end user sees fit.  In an attempt to streamline the use of this form, I want to have the listbox load as efficiently as possible.  Any help would greatly be appreciated.

Thanks

PS - I'm using a concatenated field to search filter via the wildcard textbox control filter.
Here's the where clause I'm using:
WHERE (((tblAssetAuditLog.UDb & tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like (IIf(nz([Forms]![frmAssetAuditLogSearch]![txtFilter],"")="","*","*" & [Forms]![frmAssetAuditLogSearch]![txtFilter] & "*"))))
0
Comment
Question by:shannonds
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
1. Consider a continuous form rather than a list box.

2. Do a separate filter for UDb, UNum, and Desc (Bad field name choice by the way - DESC is a SQL verb) and index on one or all of the fields.

Before doing that however, I'd find where the problem is.  Construct a query of the SQL and time how long it takes alone with the filtering you have now.

Then time how long it takes on the form.  Subtract the two and you'll have your load time for the list control and will know where most of the problem lies.

Jim.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You should be able to drop your IIF() function.
WHERE (tblAssetAuditLog.UDb & tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like "*" & [Forms]![frmAssetAuditLogSearch]![txtFilter] & "*"

Open in new window


Since the ampersand concatenator is Null-tolerant, your like clause will either resolve to
"**"
or
"*valueofyourtxtFiltercontrol*"
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Take your current SQL statement and make it a stored query.  Change the rowsource of the listbox to the queryname.
0
 

Author Comment

by:shannonds
Comment Utility
The iif statement returns all rows when there's nothing entered in the text box.  The after update event of the text box simply requeries the listbox control.  If any thing is entered the rowsource will be filtered, otherwise it returns all rows.
0
 

Author Comment

by:shannonds
Comment Utility
Jim I'm looking into the continuous form vs a listbox and will see if that has any effect, but I tend to doubt it, as my guess is that it's more of a source issue.  I'm thinking that populating it via a Recordset might be a possiblity.  Again, I won't know until I test it.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
Your criteria is preventing the query engine from using any indexes.  That means that each time you run the query, Access must read every single row in the table to determine if it fits the criteria!!!!  The query will be more efficient if you use the individual columns.

WHERE (tblAssetAuditLog.UDb = [Forms]![frmAssetAuditLogSearch]![txtUDb] OR [Forms]![frmAssetAuditLogSearch]![txtUDb] Is Null)
AND (tblAssetAuditLog.UNum  = [Forms]![frmAssetAuditLogSearch]![txtUNum] OR  [Forms]![frmAssetAuditLogSearch]![txtUNum] Is Null)
AND (tblAssets.Desc = [Forms]![frmAssetAuditLogSearch]![txtDesc] OR [Forms]![frmAssetAuditLogSearch]![txtDesc] Is Null);

This WHERE clause allows any/all of the search fields to be null.  If all the search fields are null, all records will be returned.  You can add indexes to the three search fields (if they are not foreign keys) and that will speed up the process.  Access automatically creates hidden indexes on foreign keys when you define them in the relationship window.

If you still need to use LIKE because you have partial strings, Access will probably not be able to use any of the indexes you created and so you will be back to reading all rows of the base table.  The query engine may be able to use an index if the wild card comes at the end of the string - LIKE somefield & "*" but it absolutely cannot use an index if the wild card comes first - LIKE "*" & somefield

LIKE is an extremely expensive search option since it frequently forces full table scans and the bigger your table, the longer the scan will take.  So, NEVER use LIKE just to support optional arguments.  Use the method I suggested above.  ONLY use LIKE when you actually have partial strings as you would if you were searching a name field.  If you are using a combo to select a search value, you always have a complete string and so would never use LIKE.  And finally, LIKE is a string operation and so is intended to operate against strings.  That means you would not use LIKE to search date fields or numeric fields.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
The iif statement returns all rows when there's nothing entered in the text box

Try my version of the SQL.  That is how it should behave.  A Like operator with two asterisks should behave the same as a single asterisk.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
And if you insist on using the syntax that concatenates the fields together, you should probably insert a space between the first two fields in that process, something like:

WHERE (tblAssetAuditLog.UDb & " " &  tblAssetAuditLog.UNum & " - " & tblAssets.Desc) Like "*" & ([Forms]![frmAssetAuditLogSearch]![txtFilter] + "*")

I also think you could wrap the last part of the LIKE clause in ( ) and replace the last & with a + .  If your txtFilter control is NULL, this would return:

LIKE "*" as everything inside the parentheses would evaluate to NULL
0
 

Assisted Solution

by:shannonds
shannonds earned 0 total points
Comment Utility
Thank you all for your input, but I figured it out.  Something in what Pat Hartman wrote jogged my memory.  What I was doing was specifying a criteria in the row source of the list book when the form opened.  On open of the form, it's never going to have a filtered value, so I removed the criteria from the listbox rowsorce and built it on the fly in the afterupdate event of the textbox.  It works perfectly fine and there's no need to specify a Like parameter if no value is entered.

Thanks for all your help...
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@Pat

<<The query will be more efficient if you use the individual columns.>>

Funny, that's exactly what I said in my very first comment:

"2. Do a separate filter for UDb, UNum, and Desc (Bad field name choice by the way - DESC is a SQL verb) and index on one or all of the fields."

  I and other experts certainly don't mind a different point of view, a correction to something that's been said, or even adding on to what's been said (acknowledging that you are adding on), but more then a few times now, you've commented in on threads by saying exactly what has already been said.

 If your going to comment in a question,  please add to the conversation in some way rather then simply repeating what has already been said.

 Sometimes there is overlap and similar comments are posted minute/seconds apart, and that cannot be avoided.  But if at all possible, we like to have threads clean and concise rather then having a bunch of experts all piling in and saying the exact same thing.

Thanks,
Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
@Jim,

You're acting like I took food out of your mouth.

Occasionally the OP needs reinforcement (based on his closing comments, it still sounds like he's doing the wrong thing) and normally when I repeat advice already offered, I mention that I am doing so as a way of emphasis.  Shoot me, I missed your comment about the separate columns but the rest of your advice was off the mark which is why I commented at all.  

I do believe I added to the conversation since I explained WHY what he was doing was wrong and WHY it was so slow.  I also gave him an ALTERNATIVE method of supplying optional parameters so he didn't have to use LIKE as well as a suggestion on how to speed up the search even if he had to stick with the LIKE operator.  Switching to a subform has nothing to do with anything.  In most cases, your advice is sound.  In this case, your explanation was inadequate which is not to say it was incorrect.  It simply didn't go far enough to explain why the OP should change his approach.  Way too many "experts" post "try this" answers and never explain why.  I guess it is a way to garner more POINTS with less effort.  You should also notice that although the OP said he gave me all the points, he didn't so I have none and I am not complaining.
0
 

Author Comment

by:shannonds
Comment Utility
Pat, I did actually give you all of the points, but it's still sitting with a "close request pending" message.  I'm not sure why, but this is what it says:
________________________________________________________________________
Close Request Pending
shannonds requested that this question be closed by accepting PatHartman's comment #a40002647 (500 points) as the solution and shannonds's comment #a40002772 (0 points) as the assisted solution for the following reason:

Pat's solution jogged my memory as to what I needed to do.  My last comment is what I actually ended up doing.  I gave Pat all the points for his knowledgeable approach to the topic.  I selected mine as well, so that if someone is searching for the solution in the future, they can see what I ended up doing...

To cancel this request and generate a request for Moderator review, state your reason for objecting in the standard comment box and click the 'Object' button. This question will be closed on 4/19/2014 if there are no objections.
___________________________________________________________________________

As for Jim, I chose Pat's answer, because it's the one that caused me to head down the right road.  If you're solution was the same as Pat's then I would have chosen yours.  Maybe the way you worded it was unclear or maybe if you spent more time focusing on the issue, rather than commenting on the field name...  All in all, I didn't do what you suggested, because it wasn't what I was looking for.  The fastest way to populate a listbox control isn't by not using one...  LOL.  Thanks for the effort by all, but if you want the points, make sure the one who's giving them out has a reason for selecting your answer.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
OK now with the Expert hat on:


<<As for Jim, I chose Pat's answer, because it's the one that caused me to head down the right road.  If you're solution was the same as Pat's then I would have chosen yours.  Maybe the way you worded it was unclear or maybe if you spent more time focusing on the issue, rather than commenting on the field name... >>

  For the record, I have no issue with the way points were assigned and I've been in the situation where the shoe has been on the opposite foot.   It's always a guessing game as to how much or how little to comment at first.   Sometimes you win, sometimes you loose.  Beyond that, sometimes your words are clearer then someone else's and sometimes not.

 With that said, it's usually best if there is a little back and forth to feel out exactly what the issue is, otherwise the thread gets cluttered up with a lot of comments that may or may not pertain at all.   Right now, the PAQ is loaded with questions like that, but the effort to go back and clean them all up would be astronomical.

 For example, you didn't mentioned record set size.   If you were trying to stuff more then 50,000 records into a a list box (and believe me, it's been tried), then it would never work no matter what the search was like.   That's why I suggested figuring out where the problem was first.  But I also did say "Do a separate filter for", which you found not to be clear.  I have no problem with that.

 The only job at the end of the day in my book is try and get questions answered as quickly and clearly as possible.    That's often a thorny task, but we try to do our best

Jim.
0
 

Author Closing Comment

by:shannonds
Comment Utility
Pat's solution jogged my memory as to what I needed to do.  My last comment is what I actually ended up doing.  I gave Pat all the points for his knowledgeable approach to the topic.  I selected mine as well, so that if someone is searching for the solution in the future, they can see what I ended up doing...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now