The Queries of a Database

Queries Fundamentals

After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives.

Data filtering is performed using the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement.

To create a query in Microsoft Access, on the main menu, you can click Insert -> Query and select an option in the New Query dialog box. Alternatively, in the Database window, you can first click the Queries button. If you click New under the title bar of the Database window, the New Query dialog box would display. While in the Queries section of the Database window, you can also use one of its options to proceed.

After creating a query as we will see in different examples, you can either only view and dismiss it after the necessary analysis, or you can save it for later use. When saving a query, you must give it a name. You can follow the rules and suggestions we have applied to the tables and forms so far. Additionally, when naming a query, because it can be used, as we will see later on, as a data source for a form, avoid giving it the same name as an existing table. If you try this, Microsoft Access would display an error:

To programmatically create a query using either the Microsoft Access Library or DAO, you can use the CreateQueryDef() method of the Database object. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query.

1. Start Microsoft Access and open the ROSH database
2. On the Database window, click the Forms button
3. On the main menu, click Insert -> Query
4. In the New Query dialog box, click Simple Query Wizard and click OK.
   If necessary, in the Tables/Queries combo box, select Table: StaffMembers

After creating a query and saving it, you can use it as the source of data for a form. To do this, you can visually specify it in the Record Source property of the form. You can also assign the name of a query, as a string, to the form's RecordSource property. Here is an example:

Private Sub cmdGetRecordset_Click()
    Me.RecordSource = "ListOfEmployees"
End Sub

Instead of first creating a query, you can access the Record Source property of the form and click its ellipsis button. This would prompt you to visually create a query. After creating it, you can close the query window. Microsoft Access would create the necessary SQL statement for you and assign it to the Record Source property of the form.

Instead of visually creating a query, you can write a SQL statement that selects the records and assign that statement to the RecordSource property of the form in code.

Once a query has been created and saved, it becomes a database object like the others we have used so far. If a query exists already, to use it, the user can open it like a table. To do this, in the Queries section of the Database window, the user can double-click the query or right-click it and click Open.

To programmatically open a query, you can call the OpenQuery() method of the DoCmd object. This method takes one string argument as the name of the query. Here is an example:

Private Sub cmdOpenVideoTitles_Click()
    DoCmd.OpenQuery "VideoTitles"
End Sub

After using a query, the user can close it like a regular window by clicking its system Close button. To programmatically close a query, you can call the Close() method of the DoCmd object, passing the first argument as acQuery and the second argument as the name of the query. Here is an example:

Private Sub cmdCloseVideoTitles_Click()
    DoCmd.Close acQuery, "VideoTitles"
End Sub

When this method is called, it checks whether the query is opened. If a query with that name is opened, it would be closed. If no query with that name is opened, nothing would happen (Nice!).