What the Heck is SQL

Although you might not realize it, every time you create a query in Access, you're actually creating an SQL statement. This is a good thing because as a rule, creating a query in Access is a lot easier than writing an SQL statement from scratch.

To illustrate how every query is really a SQL statement in disguise, Figure 7-1 shows a basic Access Select query that (in Datasheet view) displays some fields and records from a table.

Figure 7-1:

Simple, sample select query.

Figure 7-1:

Simple, sample select query.

So where's the SQL statement in Figure 7-1? Well, it's not visible when you're looking at the query in Design view. To see the SQL statement that defines a query, right-click the title bar of the query Design screen and choose SQL View. The whole window changes, hiding the QBE (Query-by-Example) grid and displaying the SQL statement that the query actually performs, as in Figure 7-2.

Figure 7-2:

SQL statement for the query in Figure 7-1.

Queryl : Select Query

¡SELECT [Address Book].[Last Name], [Address Book].[First Name], [Address Bo

ok].StateProVj [Address Book].[Tax Exempt]

A

=ROM [Address Book]

WHERE ((([Address Book].[Tax Exempt])=True))

ORDER BY [Address Book].[Last Name], [Address Book].[First Name];

ill

At first glance, the SQL statement and query might seem to be unrelated. However, if you look closely at the SQL statement, you'll see that it is indeed a reflection of what the query says. The syntax of an SQL statement generally looks like this:

SELECT fieldnames FROM tableName WHERE condition ORDER BY field(s)

where il fieldnames is a list of fields from the underlying table to be displayed by the query (or SQL statement).

I FROM tableName specifies the name of the table from which the data is being drawn.

i WHERE condition is an expression specifying which records to include in the query.

i ORDER BY field(s) lists the names of used for sorting (alphabetizing) records in the query results.

If I take the repetitive table name [Address Book] out of the sample SQL statement shown in Figure 7-1 (just to make the statement a little easier to read), the SQL statement is actually this:

SELECT [Last Name], [First Name], [StateProv], [Tax Exempt]

FROM [Address Book]

Figure 7-3 shows how the various parts of the QBE grid in fact do correspond to text in the SQL statement. Note the following:

1 The fields listed across the Field row specify the fields to display (for example, SELECT [Last Name], [First Name], [StateProv], [Tax Exempt]).

i The table name in the top half of the grid specifies where the fields and records will come from (for example, FROM [Address Book]).

i The WHERE clause gets its expression from the Criteria rows of the QBE grid (for example, WHERE [TaxExempt] = True).

i The ORDER BY fields come from the Sort row in the grid (for example,

Figure 7-3:

How parts of a query translate to an SQL statement.

FROM

FROM

Figure 7-3:

How parts of a query translate to an SQL statement.

i ERE... ORDER BY SELECT

Writing SQL without knowing SQL

The example I've shown you is just an example. Every query has a corresponding SQL statement. You can prove this to yourself by opening any query in any Access database, anywhere. Right-click that query's title bar and choose SQL View, and there you'll see that query's SQL statement. Right-click the title bar again and choose Query Design, and you're back to the Query Design grid.

The real beauty of it all is that you really don't need to learn SQL to write SQL statements. If you know how to create an Access query, you know how to write SQL statements because you can just create your query to do whatever you want it to do. Then right-click and choose SQL View, and there's your SQL statement. Drag the mouse pointer through that statement to select it, press Ctrl+C to copy it, and then you can just paste the SQL statement wherever you want.

You could even discard the original query after you have the SQL statement because the SQL statement and query are essentially one in the same. The only real difference is in how you use them. To do a query in Access, you create the query and switch to Datasheet view to see the results. To do the query from VBA, you execute the SQL statement instead.

The bond between Access queries and SQL is a two-way street. For example, suppose that the current database has a table like Address Book shown in earlier figures in this chapter, and I type the following SQL statement into some text editor like Notepad:

SELECT [Last Name], [First Name], [City], [StateProv]

FROM [Address Book]

Now suppose I go into Access and create a new query but don't add any tables to it. I just have a blank QBE grid to start with. In that query, I right-click the title bar and choose SQL View. Then, say I copy and paste (or type) the preceding SQL statement into a window that displays the SQL statement. Intuitively, this might seem weird because normally the query creates the SQL statement, not the other way around. But given the two-way street of SQL and Access queries, going back to Query Design view after entering the SQL statement almost miraculously translates the SQL statement into a QBE grid, as in Figure 7-4.

It's a lot easier to create a query in the query Design grid and convert it to SQL than it is to write an SQL statement and convert it to a query. If you put an incorrectly written SQL statement into the query, it won't translate. In truth, I doubt anyone would ever go to the trouble of writing out a SQL statement first to create a query. The point is that an SQL statement is an Access query. It's just that an SQL statement is a query expressed in words (which can be placed in VBA code) rather than a query expressed as information in a QBE grid (which can't be dropped into VBA code).

Figure 7-4:

Sample SQL statement translated to an Access query.

Figure 7-4:

Sample SQL statement translated to an Access query.

Exactly how you use SQL in VBA is a long story, which this chapter and the next will describe in detail. Also, not all SQL statements contain exactly the words SELECT, FROM, WHERE, and ORDER BY. Although there are lots of different words you can use in SQL to perform different kinds of tasks, the first thing you need to realize is that an SQL statement is just an Access query expressed as words rather than graphically on a grid.

0 0

Responses

  • Aoife
    What the heck is sql?
    7 years ago

Post a comment