Microsoft KB Archive/142938

= How To Create a Parameter Query via Data Access Objects =

Article ID: 142938

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Word 97 Standard Edition

-



This article was previously published under Q142938



SUMMARY
This article explains how to create and use a parameter query using Data Access Objects (DAO). A parameter query is a type of QueryDef specific to the Microsoft Jet database engine used by Visual Basic, Microsoft Access, and other products. Parameter queries enable you to automate the process of changing query criteria. With a parameter query, you can set new values for the parameters each time you run the query.



MORE INFORMATION
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 .SQL property of the QueryDef. To create a parameter query, place the PARAMETERS statement in the sqltext string. Here is the syntax for the PARAMETERS statement:

PARAMETERS parametertext datatype

parametertext - name of the parameter

datatype - type of the parameter

The following table lists the appropriate Microsoft Jet SQL data type that should be used with the PARAMETERS statement as well as the corresponding 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 Basic support additional data types (Boolean, Byte, Byte Array). The table below lists equivalent Basic types for earlier versions of Basic:   Microsoft      Microsoft        Visual Access SQL    Access Field     Basic Type      DATACONS.TXT Constant -  Bit            Yes/No           Integer/Boolean   DB_BOOLEAN = 1 Byte          Byte             Integer/Byte      DB_BYTE = 2 Short         Integer          Integer           DB_INTEGER = 3 Long          Long Integer     Long              DB_LONG = 4 Currency      Currency         Double            DB_CURRENCY = 5 IEEESingle    Single           Single            DB_SINGLE = 6 IEEEDouble    Double           Double            DB_DOUBLE = 7 DateTime      Date/Time        Variant           DB_DATE = 8 Binary        Binary           String/Byte Array Text          Text             String            DB_TEXT = 10 LongBinary    OLE Object       String/Byte Array DB_LONGBINARY = 11 LongText      Memo             String            DB_MEMO = 12 Following the PARAMETERS statement in the sqltext string, place the query. The query can refer to the parameter (parametertext) named in the PARAMETERS statement. Wherever the query refers to a parameter, the current value 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 be substituted 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 object parametertext - the name of the parameter in the PARAMETERS statement value - 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 are three methods (Execute, CreateDynaset, and CreateSnapshot) supported by a QueryDef 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 for BIBLIO.MDB (the sample Microsoft Access database that ships with Visual Basic) and should be executed only once. The second part uses the QueryDef to create a snapshot, which is then displayed. To test the example, place each of the following code segments in a CommandButton Click event procedure.

NOTE: There are some minor differences between the older DAO syntax used by Visual Basic 3.0 and Access 1.x and the newer syntax used by the other products this article applies to. Old and New DAO syntax are indicated by comments: 'Create QueryDef "by date" Dim Db As Database Dim Qd As QueryDef Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")                ' Old Set Db = DBEngine(0).OpenDatabase("C:\VB4-32\BIBLIO.MDB") ' New Set 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 = QdText Print Qd.SQL Qd.Close

' Create Snapshot from QueryDef Dim Rs As Snapshot                  ' Old Set Qd = Db.OpenQueryDef("By Date") ' Old Dim Rs As Recordset                 ' New Set Qd = Db.QueryDefs("By Date")    ' New Qd!dp = 1991                        'Set the value of the dp parameter Set Rs = Qd.CreateSnapshot               ' Old Set Rs = Qd.OpenRecordset(dbOpenSnapshot)  ' New Do Until Rs.EOF For i = 1 To Rs.Fields.Count - 1 Print Rs(i);                  'Display results of query Next Print Rs.MoveNext Loop Rs.Close Qd.Close Db.Close The second example shows how to use an action parameter query. Note that action queries are invoked with the Execute method, not CreateDynaset or CreateSnapshot, because they do not return records: 'Create QueryDef Dim Db As Database Dim Qd As QueryDef Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")                ' Old Set Db = DBEngine(0).OpenDatabase("C:\VB4-32\BIBLIO.MDB") ' New Set Qd = Db.CreateQueryDef("Delete by name") 'Create the query QdText = "PARAMETERS p1 Text; " QdText = QdText & "DELETE * FROM Authors WHERE Author = p1;" Qd.SQL = QdText Print Qd.SQL Qd.Close

' Execute the QueryDef Set Qd = Db.OpenQueryDef("Delete by name") ' Old Set Qd = Db.QueryDefs("Delete by name")    ' New Qd!p1 = "Bob" Qd.Execute          'Perform the action query Qd.Close Db.Close

