Here's some helpful tips on how to maximize your opening speed of forms.
http://www.dbforums.com/6356210-post76.html
and
http://www.dbforums.com/6366815-post77.html
In regards to DAO versus ADO, I've seen this topic come up often. Here's comments from someone who has extensively used both DAO and ADO of 20+ years coding...
DAO coders will say DAO is the best while ADO coders will say ADO is the best.
1. MSAccess has defaulted to DAO coding in some MSAccess versions and ADO in others. It's more of a personal preference on DAO/ADO syntax. Don't let the small, small speed difference between DAO and ADO persuade you to use DAO over ADO. Use what syntax you feel comfortable with learning versus any differences someone might say where one is better than the other!!
2. In some newer MSAccess versions, some DAO commands were eliminated, replaced with ADO commands. There are subtle differences such as a DAO command to run a make-table query will produce an error if the table exists while an ADO command to run the same make-table query will prompt the user to overwrite the existing table. I've seen a trend throughout the MSAccess version years where Microsoft seems to be leading coders down the path of ADO, back to DAO, then back to ADO syntax.
3. If using ADO coding, make sure to have the ADO in your references. If using DAO, make sure to have the DAO in your references. You'll notice if you've worked in different MSAccess versions, ADO references are automatically added.
5. Again, DAO coders will say DAO is the best while ADO coders will say ADO is the best. Again, a personal preference but I've found ADO much easier syntax-wise.
6. You won't
really notice a difference in any speed retrieval of records between DAO and ADO (I ran tests on 5+ million records using both DAO and ADO and the speed difference was negligable - we're talking milliseconds in speed difference.)
Concentrate instead on good db structure design for the fastest record retrieval - ie. de-normalize some fields to prevent linking of multiple tables in your queries. For example, over-normalizing a structure so the city, state, and zip are in separate tables would be ridiculous since this obviously would cause linking of several tables to return something common such as a customers address (not to mention complexity with form design).
Note that each table you have to link in a query slows down it's execution!! Think about your common totalling queries when designing the structure and again, perhaps putting 'grouping' type totalling fields in the main table versus relational tables. Especially on large recordsets where you get into the 100,000+. You can easily get into the trap of over-normalizing your structure which won't matter if you use DAO or ADO. If the relational structure is designed optimally and well, form design usually follows and should be easy and produce fast data retrieval. A poor structure leads to slow form performance as well as a good structure but a poorly designed form. Design forms without multiple, multiple subforms or use a 'shell' subform without a sourceobject and populate the sourceobject as needed (such as in the OnClick event of a button.) You can test by having 5 subforms all bound to a mainform which opens all the records. Then remove the sourceobject for the subforms and test. Ideally, return only 1 recordset to the mainform and use criteria in your subforms to only return the relational record. You can again, test by opening all records on the main form versus using criteria on the mainform to only open a specific record (see links above.)
-** Use unbound form design if you have slow external users. This will give you the FASTEST form loading time. Keep in mind that when a form opens, each
bound combobox/listbox rowsource
also takes time when loading (ie. you can test this by designing a form with 15 comboboxes and then design the same form with only 1 or 2 comboboxes/listboxes (where the rowsource of each combobox is bound to a large recordset). Again, see the links above for recommendations.
7. Using SQL Server will improve speed (along with other benefits.) Since linked SQL Server tables versus linked MSAccess tables go directly to the SQL Server versus linking to another mdb on the network drive. This will give you better performance, especially if the bottleneck is the network drive itself.
8. ADO syntax (I've found), is much easier than DAO. For every DAO command, I've found the corresponding ADO command to be much easier (again, for me) to understand.
and
9. Avoid using the Filter (as the link above suggests) and instead use the Where clause. The Filter seems to be problematic with upsizing MSAccess along with other problems with odd syntax values in the Filtering data field. For example, you can test by opening a form using the Filter parameter versus opening a form using the Where parameter. You'll notice a difference in the speed on large recordsets since filtering is applied after the recordset is loaded versus a where clause which limits loading of the recordset to the specific record.
and
10. Timer type events on forms can be problematic if not done correctly, use them carefully. Avoid timer events which close the form on the user. This causes data issues. I never use timer events on data forms the user opens. I use them on unbound forms such as closing a splash form or for mdb's which automate tasks that a user never opens (such as an IVR phone calling type mdb).
