Databases - Using DAO
Although the data control does not allow us to run SQL queries directly, it is possible to assign the RecordSet object which the data control reads from. We will not take an in depth look at SQL here, as that would require an entire book, but we will have a look at the practical basics of the SELECT statement.
The overall form of the SELECT query is as follows:
FROM tablenames IN databasename
GROUP BY fieldlist
HAVING group criteria
ORDER BY fieldlist
WITH OWNERACCESS OPTION
fieldlist contains the fields that you want to return. These are separated by commas, and if they contain a space, are enclosed in square brackets . If you want to return all fields, use the * operator. If you accessing more than one table, refer to the fields by:
tablenames and databasename are the tables from which you wish to retrieve the data. Multiple ones are separated by commas, as above. You can use the database name parameter if there are tables in other databases, but this is beyond the scope of the article, and you can look in help for more info.
searchconditions is where this statement's power really is. You can use it to limit the records selected to within only certain parameters. This is similar to the operations that you can do with the If statement in VB, and multiple statements can be linked with AND and OR. See help file for more details.
The GROUP BY and HAVING parts are used to group and select certain records when using aggregate functions, such as SUM, AVG, MIN and MAX.
ORDER BY selects which field is used to sort the returned records.
WITH OWNERACCESS OPTION is used in multi-user environments to enable other users to access the table data while you are.
That is only a very brief introduction to the SELECT statement. It is so useful because, as it is a string, you can include variables, such as textboxes, so that the user can change the data that is returned.
Well, now that we have got the basic idea of what the SELECT statement can do, let's see some examples. These examples require a Data control called Data1, and you may have to modify the path to the database:
Dim db As Database Set db = OpenDatabase("C:Program _ FilesDevStudioVBBiblio.mdb") Set Data1.Recordset = db.OpenRecordset("SELECT _ * FROM Titles WHERE Title Between 'A' _ And 'B';")
The best way to display the data is to use a MS FlexiGrid, setting the DataSource property to Data1. The SELECT statement can be used to build very complex queries, and can be nested inside other SELECT statements, as well as joining to other tables.
Note that when you have set the data control's record set using this method, you must keep the database the you got the data from open, otherwise moving between records will fail.
Page 2 of 3