VB3 Creating a Parameter Query in Visual Basic for Windows

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

This article explains how to create and use a parameter query. A parameter query is a type of QueryDef specific to Visual Basic and Microsoft Access. 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. Here 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

   PARAMETERS parametertext datatype

   parametertext - name of the parameter
   datatype      - type of the parameter

   Microsoft      Microsoft        Visual
   Access SQL     Access Field     Basic Type     DATACONS.TXT Constant
   -----------------------------------------------------------------
   Bit            Yes/No           Integer        DB_BOOLEAN = 1
   Byte           Byte             Integer        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
   Text           Text             String         DB_TEXT = 10
   LongBinary     OLE Object       String         DB_LONGBINARY = 11
   LongText       Memo             String         DB_MEMO = 12

For example, if the query text is:

   PARAMETERS i SHORT; SELECT fld FROM tbl WHERE fld=i

   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, ...

   querydef!parametertext = value

   querydef      - a QueryDef object
   parametertext - the name of the parameter in the PARAMETERS statement
   value         - the value the parameter will have

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.

More information on parameter queries is available in the Visual Basic, version 3.0, "Professional Features Book 2."

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 command button Click event procedure:

   'Create QueryDef "by date"
   Dim Db As Database
   Dim Qd As QueryDef
   Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
   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
   Db.Close

   ' Create Snapshot from QueryDef
   Dim Db As Database
   Dim Qd As QueryDef
   Dim Sn As Snapshot
   Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
   Set Qd = Db.OpenQueryDef("By Date")  'Open the "By date" query
   Qd!dp = 1991                         'Set the value of the dp parameter
   Set Sn = Qd.CreateSnapshot()         'Create a snapshot from the query
   Sn.MoveFirst
   Do Until Sn.EOF
      For i = 1 To Sn.Fields.Count - 1
         Print Sn(i);                   'Display results of query
      Next
      Print
      Sn.MoveNext
   Loop
   Sn.Close
   Qd.Close
   Db.Close

   'Create QueryDef
   Dim Db As Database
   Dim Qd As QueryDef
   Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
   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
   Db.Close

   ' Execute the QueryDef
   Dim Db As Database
   Dim Qd As QueryDef
   Set Db = OpenDatabase("C:\VB\BIBLIO.MDB")
   Set Qd = Db.OpenQueryDef("Delete by name")
   Qd!p1 = "Bob"
   Qd.Execute           'Perform the action query
   Qd.Close
   Db.Close