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!
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
VB offers a limited set of the SQL Server features but Microsoft doesn't
highlight those capabilities, preferring instead to sell its larger, more
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]"
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
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!
This is the most basic command which tells VB which fields to show in the
Simple, but very powerful means of deleting many records at one time.
Defines the Table from which the fields will be extracted.
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!