Select queries Versus action queries

To this point in this chapter, we talk only about Access select queries. That type of query gets its name from the fact that it only selects fields and records from a table. A select query never alters the contents of a table.

An action query is different from a select query in that an action query changes the contents of a table. In Access, you create action queries in much the same way you create select queries. You start off by creating a new, regular query so that you're at the Query Design grid. Then you choose the type of action query you want to create from the Query Type group on the (Query Tools) Design tab, shown in Figure 7-5.

Figure 7-5:

The (Query Tools) Design tab in Access.

Figure 7-5:

The (Query Tools) Design tab in Access.

Access Query Table Images

The main types of action queries that you can create and their purposes and relevant SQL buzzwords (described in the sections that follow) are summarized in Table 7-1.

Table 7-1 Access Action Query Types and

Corresponding SQL Keywords

Table 7-1 Access Action Query Types and

Corresponding SQL Keywords

Action Query Type

Purpose

Relevant SQL Keywords

Make-Table

Create a new table by using data from an existing table.

SELECT...INTO

Update

Change multiple fields and records within a table.

UPDATE...

Append

Add records from one table to another table.

INSERT INTO...

Delete Remove multiple records from a table. DELETE

Delete Remove multiple records from a table. DELETE

The changes that an action query makes to a table can be extensive and permanent! Never test or play around with action queries on data you need. It would be a shame (to put it mildly) to test out a delete query on your only copy of 10,000 names and addresses, only to realize that it worked — and now you have 11 names and addresses in your table and no backup. Always make a copy of your database, and test your action queries on the copy.

After you create an action query in Access, you still have to run the query before it makes any changes to your database. To run an action query in Access, the action query must be open and visible on-screen in Design view. From there, you click the Run (!) button in the Results group on the (Query Tools) Design tab to run the query.

Every action query that you create is also a SQL statement, just like when you create select queries. You get to an action query's SQL statement just like you get to a select query's — by right-clicking the title bar in Query Design and choosing SQL View. For example, Figure 7-6 shows an update query that changes the value of a field named SentWelcome to True wherever that City field contains "Houston". (Note the Update To row in the QBE grid.)

Right-clicking the title bar shown in Figure 7-6 and choosing SQL View reveals the SQL version of the query. Because this is an update query, the SQL statement doesn't start with SELECT. Rather, it starts with UPDATE, as shown here:

UPDATE [Address Book] SET SentWelcome = True WHERE (((City)="Houston"));

Figure 7-6:

Sample update query in Query Design.

Figure 7-6:

Sample update query in Query Design.

Design Address Book Vba

Still, the SQL statement is perfectly valid and runs just fine as VBA code. You can select and copy the SQL statement just as you could any other.

Getting SQL into VBA

The bottom line (again) is that if you know how to create queries in Access, you know how to write (most) kinds of SQL statements. We mention earlier that you can copy and paste a SQL statement just like you can copy and paste any other hunk of text that you see on-screen. But we would be lying if we said that you just have to drop the SQL statement into your VBA code to make it work. Here are the reasons that it's not that simple:

1 You need to get rid of the semicolon (;) at the end of the SQL statement in SQL view. (VBA doesn't like that last semicolon.)

i If the SQL statement is broken into multiple lines, unbreak it back to a single line. (Make sure to place a blank space where the line breaks used to be.)

i The whole statement needs to be placed inside quotation marks (alternating single and double quotation marks).

i If the SQL statement represents an action query, the whole SQL statement needs to be preceded by DoCmd.RunSQL in your code.

Look at an example starting with the UPDATE SQL statement shown earlier. When you copy and paste the statement into VBA code, the entire statement turns red, indicating a problem. The only real problem, though, is that things need to be reformatted a bit.

1. Move the cursor to the end of the top line, press Delete (Del) to unbreak the line, and then press the spacebar to insert a blank space where the line break used to be.

Repeat this step as necessary until the whole SQL statement is on one, long line in the code.

2. Delete the semicolon at the end of the statement and put the whole statement into quotation marks.

You can use either single (') or double (") quotation marks. However, if any quotation marks are already in the statement, you can't use the same type. For example, the sample SQL statement has a pair of double quotation marks around the word "Houston", as shown here:

To avoid a conflict with the embedded quotation marks, you have to either use single quotation marks to enclose the whole SQL statement:

'UPDATE [Address Book] SET SentWelcome = True WHERE (((City)="Houston"))'

or change the inner quotation marks to single quotes and then use double quotation marks around the whole statement — which is the preferred method:

"UPDATE [Address Book] SET SentWelcome = True WHERE (((City)='Houston'))"

3. Tell VBA that the statement is SQL and that you want VBA to execute the statement by adding DoCmd.RunSQL to the start of the line:

DoCmd.RunSQL "SELECT [City], [StateProv] FROM [Address Book] WHERE ((([StateProv])='CA'))"

Adding DoCmd.RunSQL to the SQL statement is necessary because, otherwise, VBA doesn't recognize the SQL as being different from any other code in the procedure.

The final statement in the VBA Editor, after making all the necessary changes, looks like this:

Sub

whatever()

'Set SentWelcome field to True for DoCmd.RunSQL "UPDATE [Address Book] (((City)='Houston'))"

all Houston addresses. SET SentWelcome = True WHERE

End

Sub

Was this article helpful?

0 0

Responses

  • dahlak
    How to execute update query in open office?
    8 years ago
  • tullia
    What is select query and action query?
    2 years ago
  • INES
    What is the differnt beetwn select query and action query?
    1 year ago
  • bobbi
    How to declare DoCmd.RunSQL statement in access 2007?
    10 months ago

Post a comment