You build your database using tables and queries testing the architecture and setting keys and indexes. Then you want to make the reporting easy to use and pretty to look at and now you are into spending more time developing Microsoft Access forms and reports.
You start building your first form or report and the methods you have available could be one of the following:
- Using the wizard tool which steps you through screen by screen.
- For later versions of Microsoft Access, selecting a pre-defined template by first choosing the data source (table or query).
- Starting from a blank canvas and set properties including the ‘Record Source‘ taking full control.
- Using Access VBA code to build and generate objects dynamically requiring some more advanced knowledge of Microsoft Access including VBA.
But what about the ‘Record Source‘ property itself? Which should it be based; a Table, Query or SQL?
The functional use of a form for example may pretty much dictate the route to take. For example, look at the following questions to help you decide:
Q: Will the form be used to enter new records only?
A: Use a Table because you can guarantee the values in fields will be updated to the source data file for a bound Form.
Q: Will the form be used for find records by searching for a value in a field which is based on two or more tables?
A: Use a Query which is built from pre-defined joined tables (creating that all important relationship).
Q: Will the form need to dynamically pass values with event-driven actions like after entering a value in a control?
A: Use SQL statement or a Query which is linked to the form’s control.
There are performance challenges when choosing the right approach but the first thing to remember is that the use of a form or report should be determined first followed by letting Microsoft Access database engine (JET) decide and optimise the database for you which is why most developers will opt and let the Query object be the main and first choice.
SQL statements are used more with VBA code procedures and can be quicker to run as optimisation can be controlled in code especially when using DAO, ADO or ADO.Net using the types of data recordsets they provide. – Another article perhaps!
The final thought to consider is how reusable will the data source be? Will it serve more than one Access form or report? If so, use a query. If the source is exclusive to the one form or report then embed SQL directly into it as you can pass parameter values, set criteria and calculate within the form or report and save on the extra dedicated query be stored in the first place.