Seiect queries Versus action queries

So far in this chapter, I've really only talked 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 will never alter the contents of a table.

An action query is different from a select query in that an action query actually does change 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 you're at the Query Design grid. Then you choose the type of action query you want to create from the Access Query menu, shown in Figure 7-5.

Figure 7-5:

The Query menu in Access.

Figure 7-5:

The Query menu in Access.

The main types of action queries that you can create, their purpose, 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

Action Query Type

Purpose

Relevant SQL Buzzwords

Make-Table query

Make a new table using data from an existing table.

SELECT...INTO

Update query

Change multiple fields and records within a table.

UPDATE...

Append query

Add records from one table to the bottom of some other table.

INSERT INTO...

Delete query

DELETE

Delete query

Delete 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 actually need. It would be a shame (putting 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.

After you create an action query in Access, you still have to run the query before it will actually make any changes to your database. To run an action query in Access, you need to have the action query open and visible onscreen in Design view. From there, you click the Run (!) button on the Access toolbar or choose QueryORun from the Access menu bar 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 do 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 words Update Query in the title bar and the Update To row in the QBE grid.)

Figure 7-6:

Sample update query in Query Design.

Figure 7-6:

Sample update query in Query Design.

Sample Action Query

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 follows. But still, the SQL statement is perfectly valid and will run just fine as VBA code. You can select and copy the SQL statement just as you could any other.

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

Getting SQL into VBA

So the bottom line here, once again, is that if you know how to create queries in Access, you know how to write (most) kinds of SQL statements. I 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 onscreen. But I'd be lying if I said you just have to drop the SQL statement into your VBA code to make it work. Here are the reasons why it's not that simple:

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, you need to unbreak it back to a single line (with exactly one blank space between each).

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 will turn red, indicating a problem. The only real problem, though, is that things do need to be reformatted a bit.

First, you need to unbreak the lines so that the whole SQL statement is on one, long line in the code. 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.

Next, you need to get rid of 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 follows:

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

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

Or, you have to change the inner quotation marks to single quotes, and then use double quotation marks around the whole statement, as follows:

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

Finally, VBA doesn't recognize SQL as being different from any other code in the procedure. So to tell VBA that the statement is SQL and that you want VBA to execute the statement, add DoCmd.RunSQL to the start of the line:

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

The final statement in the VBA editor, after making all the necessary changes, will look like this:

Sub whatever()

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

End Sub

0 0

Post a comment