Hamid-World

Coding Queries

Once you’ve created a database and populated it with data you need to find a way to get the data out to view. You also need to be able to change, remove or update the data in order for a database to be truly useful. So how do you go about doing this?

Well, to communicate with a database in Microsoft Access you use what is called SQL or Structured Query Language.

Microsoft Access makes manipulating database information very easy because it allows users to manipulate data visually or through coding using VBA. Both methods enable you to retrieve specific information based on set conditions.

For example in a database that holds information about books you might want to view books that were written by a specific author instead of viewing all the books in the database. This type of limitation is also called filtering and is fully supported by MS Access.

To demonstrate the use of SQL in Access we are going to create a database called Books with two tables called genre and book. So, we startup Microsoft Access 2000 and create a database with the above details. Then after creating the database, create the tables with the following data and structure:

Fig 1. Books table structure

Fig 2. Books table Data

Fig 3. Genre table structure

Fig 4. Genre table Data

Creating a Query

Microsoft Access makes this extremely easy to do. Simply click on Insert » Query, you will then get dialog box popping up that will give you various options to create a query. If you are new to creating queries then I suggest you take the Simple Query Wizard option, because the wizard will guide you in creating the kind of query that you want otherwise use the design view option.

After creating the query, you have a choice of saving it for later use. For the purposes of this article, save the query as ListEducationalBooks. I’ve created this query using the visual design method which as the name suggests lets you construct a query visually:

Fig 5. Creating the query in design view

The query retrieves a list of all the titles that fall within the educational genre. You can create the same query programmatically, using CreateQueryDef() function.

The function has the syntax: CreateQueryDef(QueryName, SQLStatement).

The first argument of the function is the name of the new query that you want to create and the second argument is the SQL statement i.e.:

Private Sub createquery_Click()
  Dim query As String
       Query = "SELECT books.title FROM books WHERE (((books.genreID)=3))"
       CreateQueryDef(ListEducationalBooks, Query) 
End Sub

Running a query

After creating and saving the query it automatically becomes a method of the database object. You can execute the query or “open” it by clicking on the red exclamation mark on the toolbar or selecting Query and then run from the menu bar.

To run the query through code, you use the OpenQuery() method of the doCmd object. The method takes three arguments which is the name of the query, view (optional) and the datamode (optional).

The syntax is as follows: OpenQuery(QueryName, View, DataMode)

You can use the method in something like this:

Private Sub Command2_Click()
    On Error GoTo Err_Command2_Click

    Dim stDocName As String

    stDocName = "ListEducationalBooks"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click

End Sub