Microsoft Access Database: The Pros And Cons Of Using Access Queries Or Table Recordsets In A Form

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:

Microsoft Certification Courses

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.

Microsoft Access Database: The Pros And Cons Of Using Access Queries Or Table Recordsets In A Form

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.

Microsoft Access Database: The Pros And Cons Of Using Access Queries Or Table Recordsets In A Form

Another Tip for you! In Microsoft Access, you can perform what is known as 'Performance Analyzer' which runs a report on your database architecture and shows you where you can improve the performance of your database application setting primary keys, indexes and joins.

I invite you to keep up to date with my articles and eBooks which covers a lot of details and can be found at http://AccessDatabaseTutorial.com/eBooks.

From Ben Beitler - "Your Access Database Expert"

watch mobile phone Buy Auburn Gear 5420113 High Performance Series Differential