Search a view in a form in .accdb.

I am upgrading an Access data project (.adp/.ade) to .accdb. It is a simple app with just two linked SQL views of the same table, filtered so they load faster (current jobs and closed jobs) displayed in separate forms. In the forms in the .adp, I could use <ctl>f to bring up a search box and search any field. In the .accbd, that function now just hangs Access (2016). How can I retain the search function in a form in an .accdb?
Bob GumaerAsked:
Who is Participating?
 
PatHartmanCommented:
It is poor practice to bind a form to a table and when the table is ODBC, it defeats any benefit you might derive from using SQL Server.  The objective when using ODBC is to let the server do the heavy lifting.  If you bind the form to a table, Access must bring down the ENTIRE table to local memory.  If your table is large, this is an unnecessary burden on your network and unless you specified that this is a snapshot, Access will also have to expend resources keeping it updated by constantly refreshing the data from the server.    Access renders the form as soon as it has retrieved enough records to load it but if you have the navigation buttons visible, you will notice that you don't see the record count.  The record count will not be updated until Access finishes fetching the data from the server which it does 10 records at a time.  Think of it as a straw from Access to the server with the Access form just sitting there sucking down data.  This is one of the reasons that DBA's hate Access with a purple passion.

A good technique is to bind the form to a query that has a where clause that references filter options on your form.  The form will open empty because the combos and textboxes will be empty.  Then I generally use a button if there is more than one search field.  The button requeries the form causing the selected data to be fetched from the server.

The ADP did handle this process for you but had too many other shortcomings so in the long run, you'll do a little more work making smart filters and a lot less work in other areas plus you get to use tables linked to Jet/ACE, Excel, and any other ODBC database your company uses.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To answer your question directly:

Ctrl + F should bring up the search box. If it doesn't, try restarting Access. If you still can't, try compacting your database. If you still can't you may have issues with the install of Office/Access. Try a repair install.

I agree with Pat's comments regarding proper techniques, but your Search window should come up ...
0
 
Bob GumaerAuthor Commented:
Thanks for your general and specific answers, gentlemen. The search hung on the 15,000 record view, but worked (albeit slowly) on the 450 record view. It looks like I'm going to have to rethink my app.
0
 
PatHartmanCommented:
Depending on how complex the criteria needs to be, I either build the entire Where clause using VBA or for simple criteria where I have only a couple of choices, I use a parameterized query.

Where (fld1 = Forms!yourform!cboFld1 OR Forms!yourform!cboFld1 Is Null) AND (fld2 = Forms!yourform!cbo2 OR Forms!yourform!cbo2 Is Null) AND ....

Notice the parentheses.  They are required to force the query engine to properly evaluate complex expressions containing different relational operators.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.