Microsoft Access 2010 Multiple Joins In Visual Basic' title='Microsoft Access 2010 Multiple Joins In Visual Basic' />ASP. NET web forms can query a Microsoft Access database, retrieve records and display them on the browser. SharePoint Server 2010 via Access Services allows for. Database Solutions for Microsoft Access. Database design and implementation articles, tips, tricks, code samples, Access FAQs and downloadable database examples. Use. Microsoft Access tips Optimizing queries. Provided by Allen Browne,  Created June 2. Last update March 2. Common query hurdles. This article addresses mistakes people often make that yield poor query performance. We assume you have set up relational tables, with primary keys, foreign keys, and indexes on the fields you search and sort on. When you installed Microsofts Word Flow keyboard on your iPhone, you probably thought it was an app or extension. Turns out, it was an experiment, an. Microsoft. The fiercely competitive software giant is positioning its wares for cloud computing with software and services. The companys two cash cows operating. Microsoft Access 2010 Multiple Joins In Visual Basic' title='Microsoft Access 2010 Multiple Joins In Visual Basic' />Use SQL rather than VBAJETACE the query engine in Access uses Structured Query Language SQL, as many databases do. JET can also call Visual Basic for Applications code VBA. This radically extends the power of JET, but it makes no sense to call VBA if SQL can do the job. Is Null, not Is. NullIs Null is native SQL. Is. Null is a VBA function call. There is never a valid reason to call Is. Null in a query, when SQL can evaluate it natively. IIf, not NzThe Nz function replaces Null with another value usually a zero for numbers, or a zero length string for text. The new value is a Variant data type, and VBA tags it with a subtype String, Long, Double, Date, or whatever. This is great in VBA a function can return different subtypes at different times. But in a query, a column can be only be ONE data type. JET therefore treats Variants as Text, since anything numbers, dates, characters,. Text column. The visual clue that JET is treating the column as Text is the way it left aligns. Numbers and dates display right aligned. If you expected a numeric or date column, you now have serious problems. Text fields are evaluated character by character. So 2 is greater than 1. Similarly, 412. Text column, because 4 comes after 1. Alarm bells should ring as soon as you see a column left aligned as Text, when you expected it handled numerically. Wrong records will be selected, and the sorting will be  nonsense. You could use typecast the expression with another VBA function call, but a better solution would be to let JET do the work instead of calling VBA at all. Instead of    NzMy. Field,0use    IIfMy. Field Is Null, 0, My. FieldYes its a little more typing, but the benefits are You avoid the function call to Nz. You retain the intended data type. The criteria are applied correctly. The column sorts correctly. This principle applies not just to Nz, but to any VBA function that returns a Variant. Its just that Nz is the most common instance we see. Note JETs IIf is much more efficient than the similarly named function in VBA. The VBA one wastes time calculating both the True and False parts, and generates errors if either part does not work out even if that part is not needed. The JET IIf does not have these problems. Domain aggregate functions. DLookup, DSum, etc are slow to execute. They involve VBA calls, Expression Service calls, and they waste resources opening additional connections to the data file. Particularly if JET must perform the operation on each row of a query, this really bogs things down. A subquery will be considerably faster than a domain aggregate function. In most cases, a stacked query will be faster yet i. There are times when a domain aggregate function is still the best solution you have e. For those cases, it might help to use ELookup instead of the built in functions. Craft your expressions to use indexes. The query will be much faster if the database can use an index to select records or sort them. Here are two examples. Criteria on calculated fields. In the example at right, the Year function looks easier, but this will execute much slower. For every record, JET makes a VBA function call, gets the result, and then scans the entire table to eliminate the records from other years. Without the function call, JET could use the index to instantaneously select the records for 2. This will execute orders of magnitude faster. You could use WHERE Table. My. Date Between 112. And 1. 23. 12. Particularly in criteria or sorting, avoid VBA calls so JET can use the index. Sorting on concatenated fields. Picture a combo box for selecting people by name. The Client. ID is hidden, and Surname and First. Name are concatenated into one column so the full name is displayed even when the combo is not dropped down. Budgeting Tools. Do not sort by the concatenated field Sort by the two fields, so JET can use the indexes on the fields to perform the sorting. Optimize Totals queries. The JET query optimizer is very good, so you may find that simple queries are fast without the tips in this section. It is still worth the effort to create the best queries you can, so they dont suddenly slow down when you modify them. WHERE versus HAVINGTotals queries those with a GROUP BY clause can have both a WHERE clause and a HAVING clause. The WHERE is executed first before aggregation the HAVING is executed afterwards when the totals have been calculated. It makes sense, then, to put your criteria in the WHERE clause, and use the HAVING clause only when you must apply criteria on the aggregated totals. This is not obvious in the Access query designer. When you add a field to the design grid, Access sets the Total row to Group By, and the temptation is type your criteria under that. If you do, the criteria end up in the HAVING clause. To use the WHERE clause, add the field to the grid a second time, and choose Where in the Total row. FIRST versus GROUP BYWhen you add a field to a Totals query, Access offers Group By in the Total row. The default behavior, therefore, is that Access must group on all these fields. A primary key is unique. So, if you group by the primary key field, there is no need to group by other fields in that table. You can optimize the query by choosing First instead of Group By in the Total row under the other fields. First allows JET to return the value from the first matching record, without needing to group by the field. This makes a major difference with Memo fields. If you GROUP BY a memo Notes in the example, Access compares only the first 2. By choosing First instead of Group By, JET is free to return the entire memo field from the first match. So not only is it more efficient it actually solves the the problem of memo fields being chopped off. A downside of using First is that the fields are aliased, e. First. Of. Notes. Other optimizations. Other broad suggestions for optimizing queries in JET With multi table queries, use JOINs where possible. JET will execute this faster than a WHERE clause on the foreign key. Return as few fields as possible. This optimizes memory use, and may decrease disk reads. But do include the key fields, so JET has a quick way to identify the records. Build query strings dynamically, as demonstrated in this search form. Particularly where the user will enter only a few of the criteria options you give them, this radically simplifies the criteria. Access applies filters intelligently, i. Filter or Where. Condition is typically applied before it requests the records from the back end. Avoid multiple tables on the outer side of a JOIN, as JET can misinterpret these. To control the order of execution, save one query and use it as an input table to another query stacked queries. This is important, as JET does not honor the bracketing in the FROM clause when it compiles an execution plan. Subqueries are generally less efficient than other techniques such as JOINs or stacked queries, but more efficient than using domain aggregate functions. For suggestions on crosstab queries, see Crosstab Techniques. Use JETs Show. Plan for more detailed information on how JET plans to execute a query.