Parametric Queries with the Data Control

by Ian Roberts - GUI Computing

One of the shortcomings of the data control in VB3 was that you couldn't attach it to a querydef that needed parameters. So you were stuck with placing you SQL string into the recordsource property of the control, and modifying the SQL for different conditions. Another option was to build a parameter table and by using an inner join clause to attach parameters you query you could change the parameter by writing to the parameter table. But by doing things like this the power of the jet engine was lost.

With VB4 and Jet 2.5/3.0 we have a nice fix for this, the introduction of the Recordset object. You can create three types of Recordset objects, table, dynaset and snapshot. (They look sort of familiar don't they). A Recordset object represents the records in a base table or the records that result from running a query (with or without parameters). And you can bind the data control directly to a Recordset, something like this...

  Dim qd as querydef
  Dim rs as recordset

Data1.Datbasename = "Biblio.MDB" Data1.Recordsource = "Titles" Data1.Refresh ' Create database object
Set qd = Data1.database.querydefs("qryTitles") ' Open a query in the MDB qd!parTitle = "*SQL*" ' Set parameter 1 Set rs = Data1.Database.Openrecordset(qd,dbOpenDynaset) ' Create a recordset
Set Data1.recordset = rs

Written by: Ian Roberts
Aug 95