To selectively display records in a recordset, the feature to use is SQL - Structured Query Language. By creating a query (a text string which tells VB what to include in a recordset or what actions to take against the data in a recordset) you can greatly simplify the code you have to write in an application that utilizes databases. You can even write SQL queries which will modify many records in a single operation. Once I understood the basics, the use of SQL hit me like a revelation. It's easily one of the top 5 features of VB's database handling capabilities!

Introduction to SQL

VB has very poor documentation of its support for SQL. What SQL is or how it is used is a mystery for most beginners simply because it is so darned hard to find out any information on the topic! Those few references to SQL that exist in the VB documentation are very short examples and there is virtually no discussion on how to create your own queries.

I assume that part of the reason for the Microsoft approach to SQL is that it sells a product called SQL Server, which is a very powerful database interface. VB offers a limited set of the SQL Server features but Microsoft doesn't highlight those capabilities, preferring instead to sell its larger, more profitable product.

Considering the power of SQL statements I'm very surprised that Microsoft doesn't highlight the features more than they do. However, the fact is that beginners have to look hard to find help so that's where this tutorial comes into play.

Sample SQL Queries
Looking at the positive side of things, I think you'll find that SQL is so intuitive that just by seeing a few examples you will gain a fair understanding of what is going on. Before I get into some of the details about using SQL, here are some examples that should help you get the feel for an SQL query.

"Select * From Title Where [Year Published] < 1889"

"Delete From Titles Where [Year Published] < #1/1/1889#"

"Select Name, Picture From Authors Where Date_of_Birth = #2/1/1947#"

"Select * From Employees"

"Select [First Name], [Last Name] From Employees"

"Select Employees, Department, SupvName From Supervisors, Employees Where Employees.Department = Supervisorts.Department"

"Select Distinct [Last Name] From Employees"

"Select [Last Name], Salary From Employees Where Salary > 2100"

"Select * From Orders Where [Shipped Date] = #5/12/93#"

"Select [Product Name], Sum ([Units in Stock]) From Products Group By [Product Name]"

"Select * From Employees Order By [Last Name], Asc"

"Select [Last Name], [First Name] From Employees Order by 2 Asc"

"Select [Last Name], Salary From Employees Order By Salary, Desc, [Last Name]

Three things to note about the examples:

• "*" is used to denote all fields 
• Dates are enclosed by pound signs, like this: "#2/1/1947#" 
• Fields with multi-part names which include spaces are enclosed in brackets: [ ]

Now that you've read some of the examples, how do you use them? Simply set the RecordSource property of a data control to an SQL statement such as those above and refresh the control like this:

Data3.RecordSource = "SELECT * FROM Agency ORDER BY [City]"

Data3.Refresh

Just make sure that any references to fields match those contained in the actual database. Doing so will create a recordset whose content will match the constraints described by the SQL statement.

Considering that there are entire books on this subject, I can hardly expect to do it serious justice but there are some basics which can be summarized in a short tutorial like this one.

First of all, there are 5 parts to an SQL statement which you should recognize:

Command	Clauses	Predicates	Operators	Aggregate Functions
Create	From	Distinct	AND	Avg
Drop	Where	Top	OR	Count
Alter	Group By		NOT	Sum
Select	Having		Between	Max
Insert	Order By		Like	Min
Update				In
Delete

With a little inspection you can pretty much guess what each of these pieces of an SQL statement can do. However, here are a couple which you'll not want to miss and which I use pretty regularly. Don't miss the last one in my list, which is a very easy way to sort a recordset!

• Select
  This is the most basic command which tells VB which fields to show in the recordset.
•   Delete
  Simple, but very powerful means of deleting many records at one time.
•   From
  Defines the Table from which the fields will be extracted. 
• Where
  Precedes the conditions by which records are selected from the database 
• Order By
  Sorts the records by any combination of fields you chose.

This should get you started. Try out a few of the examples on one of the databases which come with VB and you'll see that it's really very easy to use the power of SQL!