How To Create a Parameter Query via Data Access Objects
Symptoms
This article explains how to create and use a parameter query using DataAccess Objects (DAO). A parameter query is a type of QueryDef specific tothe Microsoft Jet database engine used by Visual Basic, Microsoft Access,and other products. Parameter queries enable you to automate the processof changing query criteria. With a parameter query, you can set new valuesfor the parameters each time you run the query.
Resolution
A parameter query is created in a program by using the CreateQueryDef()function. Following is the syntax for the CreateQueryDef() function:
Set querydef = database.CreateQueryDef(name, sqltext)
querydef – a QueryDef object
database – a Database object
name- string containing query name
sqltext- string containing the SQL query text The sqltext string is optional or it can be defined by using the .SQLproperty of the QueryDef. To create a parameter query, place thePARAMETERS statement in the sqltext string. Here is the syntax for thePARAMETERS statement:
PARAMETERS parametertext datatype
parametertext – name of the parameter
datatype- type of the parameter The following table lists the appropriate Microsoft Jet SQL data typethat should be used with the PARAMETERS statement as well as thecorresponding Microsoft Access field type, Visual Basic variable type,and constant value from the DATACONS.TXT file.
NOTE: Visual Basic 4.0 and later and Office 95 and later versions of Basicsupport additional data types (Boolean, Byte, Byte Array). The table belowlists equivalent Basic types for earlier versions of Basic:
MicrosoftMicrosoftVisualAccess SQLAccess FieldBasic TypeDATACONS.TXT Constant———————————————————————BitYes/NoInteger/BooleanDB_BOOLEAN = 1ByteByteInteger/ByteDB_BYTE = 2ShortIntegerIntegerDB_INTEGER = 3LongLong IntegerLongDB_LONG = 4CurrencyCurrencyDoubleDB_CURRENCY = 5IEEESingleSingleSingleDB_SINGLE = 6IEEEDoubleDoubleDoubleDB_DOUBLE = 7DateTimeDate/TimeVariantDB_DATE = 8BinaryBinaryString/Byte ArrayTextTextStringDB_TEXT = 10LongBinaryOLE ObjectString/Byte Array DB_LONGBINARY = 11LongTextMemoStringDB_MEMO = 12 Following the PARAMETERS statement in the sqltext string, place the query.The query can refer to the parameter (parametertext) named in thePARAMETERS statement. Wherever the query refers to a parameter, the currentvalue will be substituted when the query is executed.
For example, if the query text is:
PARAMETERS i SHORT; SELECT fld FROM tbl WHERE fld=i and the parameter i was set to 42 in the program, the parameter i would besubstituted and the resulting query would be equivalent to:
SELECT fld FROM tbl WHERE fld=42
Multiple Parameters in a PARAMETERS Statement It is also possible to have multiple parameters in a PARAMETERS statement.To do this, use commas to separate the parameters as follows:
PARAMETERS parametertext datatype, parametertext datatype, … Prior to executing the query, set the parameters using this syntax:
querydef!parametertext = value
querydef- a QueryDef objectparametertext – the name of the parameter in the PARAMETERS statementvalue- the value the parameter will have In the previous example, you would use QD!i=42 before executing the query.
Once the parameters are set, you are ready to execute the query. There arethree methods (Execute, CreateDynaset, and CreateSnapshot) supported by aQueryDef that will cause the query to be executed.
Example Parameter Queries The following example illustrates the use of a short parameter in a query.The example has two parts. The first part creates a new QueryDef forBIBLIO.MDB (the sample Microsoft Access database that ships with VisualBasic) and should be executed only once. The second part uses the QueryDefto create a snapshot, which is then displayed. To test the example, placeeach of the following code segments in a CommandButton Click eventprocedure.
NOTE: There are some minor differences between the older DAO syntax used byVisual Basic 3.0 and Access 1.x and the newer syntax used by the otherproducts this article applies to. Old and New DAO syntax are indicated bycomments:
‘Create QueryDef “by date”Dim Db As DatabaseDim Qd As QueryDefSet Db = OpenDatabase(“C:\VB\BIBLIO.MDB”)’ OldSet Db = DBEngine(0).OpenDatabase(“C:\VB4-32\BIBLIO.MDB”) ‘ NewSet Qd = Db.CreateQueryDef(“By date”) ‘Create the query “By date”QdText = “PARAMETERS dp Short; “QdText = QdText & “SELECT * from Titles WHERE [Year Published] = dp”Qd.SQL = QdTextPrint Qd.SQLQd.Close’ Create Snapshot from QueryDefDim Rs As Snapshot’ OldSet Qd = Db.OpenQueryDef(“By Date”)’ OldDim Rs As Recordset’ NewSet Qd = Db.QueryDefs(“By Date”)’ NewQd!dp = 1991′Set the value of the dp parameterSet Rs = Qd.CreateSnapshot()’ OldSet Rs = Qd.OpenRecordset(dbOpenSnapshot)’ NewDo Until Rs.EOFFor i = 1 To Rs.Fields.Count – 1Print Rs(i);’Display results of queryNextPrintRs.MoveNextLoopRs.CloseQd.CloseDb.CloseThe second example shows how to use an action parameter query. Note thataction queries are invoked with the Execute method, not CreateDynaset orCreateSnapshot, because they do not return records:
‘Create QueryDefDim Db As DatabaseDim Qd As QueryDefSet Db = OpenDatabase(“C:\VB\BIBLIO.MDB”)’ OldSet Db = DBEngine(0).OpenDatabase(“C:\VB4-32\BIBLIO.MDB”) ‘ NewSet Qd = Db.CreateQueryDef(“Delete by name”) ‘Create the queryQdText = “PARAMETERS p1 Text; “QdText = QdText & “DELETE * FROM Authors WHERE Author = p1;”Qd.SQL = QdTextPrint Qd.SQLQd.Close’ Execute the QueryDefSet Qd = Db.OpenQueryDef(“Delete by name”)’ OldSet Qd = Db.QueryDefs(“Delete by name”)’ NewQd!p1 = “Bob”Qd.Execute’Perform the action queryQd.CloseDb.Close