|
vb123.com |
|
|
Home Contact Us Order our Software RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has your database open, start the
correct version of Access, easy compacting and backups, change startup
options, mde compile, shutdown database
Read and
Download Garry's Blog
About The Editor
Garry Robinson
writes for a number of popular computer magazines, is now a book author
and has worked on 100+ Access databases. He is based in Sydney,
Australia or try our new site built with
SharePoint Designer |
Is Your Database Corrupt ?
Seven Performance Hints For Combo And List Boxes In Access
By Sandeep Anand
Things not to do (if you can help it) include ...
1. Using an SQL statement instead of a Query
Don't use an SQL statement as your RowSource for the combo/list box instead use a saved query.
Whenever I use a combo/list box in a form, I first create a query that will be used to supply the data for this combo/list box.
Using a wizard to create a combo/list box is easy but it does one thing wrong. The wizard sets the RowSource of the combo/list box to an SQL statement, which does not give the best performance.
If you used a wizard to create a combo/list box in a form, you should change the RowSource property to a saved query.
To change the RowSource property to a saved query, click the Build button. This opens the Query Builder window. Click on Save on the File menu, and type a name for the query. When you close the Query Builder window, click on Yes to update the property.
2. Keeping unnecessary fields in the RowSource
Leaving extra fields in the query that is used as the RowSource for the combo/list box can decrease performance.
Don't put more fields in the query than are being used in the combo/list box. Include only the fields that are absolutely necessary.
3. Using Linked tables
If the data in the combo/list box is not going to change, don't use linked tables as the RowSource. In this case, it's better to import the data into your database instead.
4. No Indexing
You should index the bound field of the combo/list box. If the first field is not the bound field you should index the first field as well.
5. Using a Number field instead of Text When Using AutoExpand
If you are using the fill-in-as-you-type feature of combo boxes and have the AutoExpand property of the combo box set to Yes, then make sure that the first displayed field is of Text data type.
Access converts a numeric value to text in order to find a match in the list, when AutoExpand is on. If the data type is Text, Access doesn't have to do this conversion.
6. Leaving AutoExpand On
Don't leave the AutoExpand property to Yes, if you are not using the fill-in-as-you-type feature of the combo box. Set this property to No.
7. Using Multiple-table RowSource
If possible, don't use Multiple-table RowSources. Use single-table or query RowSources instead. This is even more important on a form, if the bound field in a lookup combo box is not the displayed field.
Article by Sandeep Anand of Mata Technologies. Visit http://www.matatechnologies.com/ for FREE Code, Tips and Tricks on MSAccess and more.
Too busy to visit the site? Subscribe to their Free "Access Tips And Tricks" Ezine as follows
mailto:AccessTipsAndTricks-subscribe@topica.com
Click on the
button for the next tips page.
Other Related pages at vb123 are
Persistent Caching >>>Take a Load Off Your Server with Access 97-2000
Consolidation Queries
Smart Selection Criteria For Queries using Combo Boxes
Form Based Selection Criteria For Queries using Combo
Boxes