| Access & SQL 1 |
|
|
Access and SQL
|
|||||||||||||||||||||||||||
| Point at the icons below to display the Query view... | ||
|
Design View
|
SQL View
|
Datasheet
View
|
 |
||
In this series of tutorials you will find out how to build new queries and change existing queries using VBA and SQL, and find out why many professional database developers prefer not to use stored queries at all.
Filtering and Sorting Forms and Reports
Access forms offer the user the opportunity to filter and sort the recordset being displayed. This is done by choosing an option from the Records menu or from the form's own right-click context menu. Several options are available.
When a filter is chosen Access applies an SQL WHERE clause to the form's Filter property. The illustration below shows a form being filtered and the resulting property value being shown in the form's property sheet:
 |
>>> |
 |
Similarly, when a sort order is chosen Access applies an ORDER BY clause to the form's Order By property:
 |
>>> |
 |
Both of these properties can be set using VBA in both forms and reports. If fact, a report's Filter and Order By properties can only be changed by the user in the report's design view. This series of tutorials will show you how to write VBA and SQL code to filter and sort forms and reports at run-time and create powerful tools for the user.
Specifying the Recordset Displayed by a Form or Report
When you build a form or report, the purpose of which is to display data, you have to specify the recordset on which it is based. This information is stored as the object's Recordset property. Once set, this property can not be changed by the user without going into the object's design view, which requires knowledge of form or report design and is time-consuming.
Although you are unlikely to want to change the recordset property of a form it is common for database users to want a standardised report design. Many people deal with this by creating multiple copies of the report, but with VBA you can use just one report and change its recordset as many times as you want, simply by creating an SQL statement for each report you want to display.
These are just some of the things that SQL is be used for in an Access database.
Can SQL do things that the Query Design tool can't?
Access uses SQL to perform all the data functions of its Query Design tool, including the various "action" queries (Make Table, Append, Update and Delete). All these functions can be achieved using very simple VBA statements combined with SQL without the need to use the query design tool or to maintain any stored queries.
There a number of functions that can be performed using SQL that can not be represented by the QBE grid (QBE = Query By Example) and can only be executed by creating the SQL manually, either by entering it into the SQL view window or by using VBA. Access refers to these functions as SQL Specific Queries. Access lists these on the Query menu:
There are three kinds of SQL Specific queries: the Union Query, the Pass-Through Query and the Data Definition Query. Here's what they do:
The Union Query
Union queries simultaneously query separate datasets (either tables or other queries) and combine the result into a single recordset. This is not the same as querying linked tables. The datasets must have a similar structure (the data types must match but field names need not be the same).
For example, you might have a Suppliers table and a Customers table and you want to create a phone list from both sets of data. If you were restricted to using the QBE grid, you would have to create two queries, one for each table, and then combine the results. A Union query can interrogate both tables at the same time and present the results as a single set of data.
The Pass-Through Query
Pass-Through queries are useful when you want to query data in another server-type database (such as Microsoft SQL Server or Oracle) that has its own more powerful database engine running on a network server. The query is designed in the SQL view of Access (or with VBA) but when run it is sent direct to the server where it is executed, bypassing the Access Jet engine. The result is returned to Access and displayed in the normal way, and the need to have a linked table in the Access database is eliminated.
The Data Definition Query
Data Definition queries open up a completely different range of possibilities. They are used to create and modify tables. Fields can be defined and their properties specified. Fields and tables can be deleted. Relationships and indexes can be created and defined.
Data Definition queries are a very powerful tool in the SQL language and can perform many tasks on the structure of a database without recourse to VBA programming.
SQL Can Make Query Design Easier!
The QBE grid of the Query design window is a very useful tool, but sometimes it is easier to write SQL directly into the SQL View than to try to represent it on the grid. An example of this is specifying the sort order of a query. Here is a (very) simple example to illustrate the point:
Suppose you want to sort your query by two columns. In the QBE grid you specify the sort direction of each column:
 But Access determines the sort order by the position of the columns in the QBE and sorts left-to-right. Here the data is sorted first by Firstname then by Lastname. But what if you wanted to sort the other way? Rearranging the columns would mean that the Lastname field would appear before the firstname field and you might not want that! |
>>> |
 |
The answer is to specify the correct sort order in the SQL:
 |
>>> |
 |
The SQL SELECT clause determines the order in which the data is displayed and the ORDER BY clause determines the sort order. Getting the desired sort order is simply a matter of editing the ORDER BY clause. Here's how you would have to represent that in the QBE grid:
 |
To get this result in using the QBE grid you have to enter
additional columns (remembering to uncheck their "show" option). This is a very
simple example. With multiple columns and a complex sort order there is a lot
more to do. Whatever way you do it the result is the same. The data is displayed in the order Firstname, Lastname but the sort order is Lastname, Firstname. NOTE: When you make changes the query's SQL view Access modifies the QBE grid to represent the new SQL statement graphically. |
Do I need to know SQL?
Most database users don't need to know SQL. Anyone who has built a anything but the most basic of queries using the Access query design window has already been using SQL without knowing it. Criteria like: Between #01/01/2003# AND #01/07/2003# or Not("London") or In("London", "Paris", "NewYork") are all phrases from the SQL language.
Access contains many tools and wizards that hep the user create a powerful database without having to learn SQL. But if you are at all serious about database building (and the fact that you are reading this suggests that you are!) a knowledge of SQL will help you build better, more powerful databases. So my answer is an emphatic "YES!".
But don't panic... this doesn't mean that you have to go on an SQL course right away, or even buy a book (although there are plenty of excellent books and online resources on the subject). I started learning SQL by building queries in the Access query design window, then looking at the SQL that was Access generated in the SQL View. It's an excellent way to learn the basics. And if you follow this series of tutorials you should learn enough to help you figure out the rest yourself.
Online Resources for SQL
There is plenty of SQL reference material available free on the web. Just go to your favourite search engine and type in "SQL tutorial" for a (large) selection. I had planned to include a selection here but most of them are pretty turgid and don't make easy reading. Whilst I suppose it isn't fair to complain when it's free, it is often difficult to find the SQL amongst the plethora of adverts and pop-ups. Anyway, you've found me so you're in the best place!
For an overview of SQL try W3 Schools - Learn SQL which has introductory and advanced SQL tutorials at http://www.w3schools.com/sql/
Books on SQL
Most good books on Microsoft Access and Access development will have a section on SQL so take a look at the Access page of my bookshelf section.
I have only one book on SQL. I bought it when I was considering a
project working with both Microsoft Access and Microsoft SQL Server,
but it is an excellent and very readable introduction for the
newcomer to SQL as well as a vital reference work for anyone
considering working with both programs:
 |
SQL: Access to SQL Server Susan Sales Harkins, Martin Reid Apress ISBN: 1893115305 Find this book at
Amazon.com (USA) |
What's Next?
The next tutorial in this series will explain how to incorporate
SQL into VBA code:
Access and SQL Part 2: Putting VBA and SQL Together
