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

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

I wrote the following article this week based on a comment from a facebbok fan who wanted to know which type of record source should you use when building Microsoft Access database forms and reports.

microsoft access database record sourceHere’s the aritcle…

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:

  1. Using the wizard tool which steps you through screen by screen.
  2. For later versions of Microsoft Access, selecting a pre-defined template by first choosing the data source (table or query).
  3. Starting from a blank canvas and set properties including the ‘Record Source‘ taking full control.
  4. 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.

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 https://AccessDatabaseTutorial.com/eBooks.

From Ben Beitler – “Your Access Database Expert”

Article Source: http://EzineArticles.com/?expert=Ben_Beitler

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

The higher level approach is to use Access code VBA which will mean learning about DAO, ADO and ADO.Net. Even choosing between which three options to opt for when coding opens up another set of decision making actions.

Microsoft Access database applications tend to use a mix of tables, queries, SQL statements and some VBA code too to balance performance with ease of use.

This entry was posted in Forms, MS Access, Queries, Reports, VBA and tagged , , , , . Bookmark the permalink.

One Response to The Pros And Cons Of Using Access Queries Or Table Recordsets In A Form

  1. Ben Beitler says:

    ADO versus DAO, which one? DAO is Access’s own data access connection class for the JET engine it uses and provides properties and methods which are easy to use to handle records and structures. Using ADO is for the wider platform based database applications including the web and connects with other databases like SQL Server.
    Microsoft started with promoting DAO then changed it’s approach to ADO before going back to DAO but both are still supported today.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.