Querying a Database

See Also278J785

To work with the data in a database3GGA17, you can use queries3MZK9H9 to manipulate or extract data.  A query either asks the database for a set of records matching specified criteria (a SELECT query), or tells the database to perform a specified operation on a set of records that match specified criteria (an action query).

You can use a data control's RecordSource property to query the database, or you can use one of the Find methods with a data control's Recordset property.

Using an SQL query with a data control

  1. Design your query so the database returns only the records you want.

  2. Enter the SQL statement29F05E5 in the RecordSource property.  You can do this at design time or run time.  At run time, you can use this code:

      Data1.RecordSource = "SELECT * FROM Publishers WHERE State = 'NY'"

  3. At run time, use the Refresh method to recreate the underlying Dynaset in the data control:

      Data1.Recordset.Refresh

Using an existing QueryDef in the RecordSource property

  1. Enter the name of the QueryDef in the RecordSource property.  You can do this at design time or run time.  At run time, you can use this code:

      Data1.RecordSource = "Full Name"

  2. At run time, use the Refresh method to recreate the underlying Dynaset in the data control:

      Data1.Recordset.Refresh

Professional Edition

You can also use SQL statements or QueryDef objects with the Professional Edition.

Creating a new QueryDef object

You can set the SQL property of a QueryDef in either of two situations:

  1. Design your query so the database returns only the records you want.

  2. Create a complete QueryDef in code:

      Dim MyQuery As QueryDef, Db As Database, MySQL As String
Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
MySQL = "SELECT Author FROM Authors WHERE Author LIKE 'A*'"
Set MyQuery = Db.CreateQueryDef("A Names", MySQL)

Or you can create a blank QueryDef and set the SQL property later.

  1. Design your query so the database returns only the records you want.

  2. Create a QueryDef in code:

      Dim MyQuery As QueryDef, Db As Database, Dn As Dynaset
Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
Set MyQuery = Db.CreateQueryDef("A Names")

  3. Set the SQL property later:

   MyQuery.SQL = "SELECT Author FROM Authors WHERE Left(Author, 1) = 'A'"

  4. Now you can use the QueryDef to create a Dynaset with either of these lines of code:

      Set Dn = MyQuery.CreateDynaset()
Set Dn = Db.CreateDynaset("A Names")

You can also create parameter queriesG72497 that allow you to modify parts of a query at run time without changing the query saved in the database.

Creating a parameter QueryDef object

  1. Design your parameter1U30NBO query so the database returns only the records you want.

  2. Create a parameter QueryDef in code:

      Dim MyQuery As QueryDef, Db As Database
Dim MySQL As String, Dn As Dynaset
Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
MySQL = "PARAMETERS Param Text;"
MySQL = MySQL & " SELECT Author FROM Authors"
MySQL = MySQL & " WHERE Author = [MyParam]"
Set MyQuery = Db.CreateQueryDef("A Name", MySQL)

  2. You must set the parameter before you use the query:

      MyQuery!MyParam = "Jones, Arthur"
Set Dn = MyQuery.CreateDynaset()

See Also

Help:

CreateQueryDef Method (Professional Edition Only)

Data Access11WWTV1

Data Control2E1FEX3

Name Property2016V9P

OpenQueryDef Method (Professional Edition Only)

QueryDef ObjectDFCEMQ

Recordset Property2D16TH3

SQL Property (Professional Edition Only)

Using SQL (Structured Query Language)2WCWZZP

Data Access Guide:

Chapter 3, "Creating Programs to Manipulate Data"