How to Use Wildcards in SQL Query to Make Dynasets & Snapshots

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

You can build a Dynaset or Snapshot based upon wildcard field-search characters in an SQL query. The find methods (FindFirst, FindLast, FindNext, and FindPrevious) can also search a Dynaset or Snapshot using wildcard search characters in an SQL query.

MORE INFORMATION

By using the Like statement in the SQL query language, you can search for database field values using the asterisk (*) and question mark (?) characters as wildcards. The * and ? wildcards let you find a wider set of field values beginning or ending with any desired root. For example, the following SQL syntax selects the records from a table where the Authorfield field values begin with the letter b:

   Select * from XTable Where Authorfield Like 'b*'

Asterisk (*) Wildcard Usage

In the SQL syntax for the Like statement, the asterisk (*) acts as a wildcard place holder for any number of characters, from zero up to the field length. A search for b* finds any field value beginning with the letter b. A search for *b finds any field value ending with b. A search for *xxxx* finds any field value that contains the xxxx substring. A search for * by itself matches all field values.

Question Mark (?) Wildcard Usage

In the SQL syntax for the Like statement, the question mark (?) acts as a wildcard place holder for a single character. A search for ??b* finds any field value that has b in the third character. A query for *b?? finds any field value with b as the third from the last character.

Speed Considerations

Of the following two techniques, 1 is faster than 2:

1. For greater speed, invoke the SQL wildcard field search only once to build the Dynaset or Snapshot of records that match your search criteria. Then use the fast move methods (MoveFirst, MoveLast, MoveNext, and MovePrevious) or click the data control to quickly navigate between all the records that match the specified search criteria. For example:

   Dim MyDS As Dynaset, MyDB As database, SQLx As String SQLx = "Select * from Authors Where Author Like 'b*' "

      Set MyDB = OpenDatabase("BIBLIO.MDB")   'Open a database.
      Set MyDS = MyDB.CreateDynaset(SQLx)     'Create Dynaset using SQLx.
      While Not MyDS.Eof
         Print MyDS!author
         MyDS.MoveNext
      Wend
   
      The Eof property is True after MoveNext moves past the last record.
   
      Visual Basic creates a Dynaset or Snapshot very quickly when using
      indexes. Subsequent find methods are relatively slow and sequential, as
      shown in technique 2 below.
   
   

2. A slower technique is to create a Dynaset composed of the entire table and then to use multiple find methods. Each FindNext would re-invoke the SQL wildcard field search to find the next matching record. This adds query time overhead. After finding a certain number of records, the total time taken would be slower than with technique 1 described above.

   Dim MyDS As Dynaset, MyDB As Database, SQLx As String SQLx = "author Like 'b*'"

      Set MyDB = OpenDatabase("BIBLIO.MDB")     'Open a database.
      Set MyDS = MyDB.CreateDynaset("Authors")  'Create Dynaset with table.
      MyDS.FindFirst SQLx      'Find first record matching criteria.
      Do Until MyDS.NoMatch
         Print MyDS!author
         MyDS.FindNext SQLx    'Find next record matching criteria.
      Loop
   
      You can invoke the FindNext method until Nomatch = True, as shown.
   
   

Example Using SQL Wildcard Search with a Data Control

The Text1 box in the following program shows individual records of the Author field of the BIBLIO.MDB database. When you click the Command1 button, the program automatically appends and prefixes the * wildcard search character to any search string that you enter in the Text2 text box. That widens the resulting recordset shown in Text1. You can browse the recordset shown in Text1 by clicking the data control.

1. Start Visual Basic or begin a New Project. Form1 is created by default.

2. Double-click the form. Add the following to the Form Load event code:

      Sub Form_Load ()
         text1.Text = "Enter ar* in Text2 and click Command1. Also try *z* "
         text2.Text = "*"  'A lone asterisk finds all records.
      End Sub
   
   

3. Add a data control (Data1) to Form1.

4. Add a text box (Text1) to Form1. Give Text1 the following properties in order to bind it to the data control and to the Author field in the database table:

   DataSource = Data1 DataField = Author

5. Add a second text box (Text2) without setting any properties. You can change the wildcard criteria for database queries in Text2 at run time.

6. Add a command button (Command1) to Form1. Add the following code to its Click event:

      Sub Command1_Click ()
   
         Dim SQLX As String, SearchText As String
   
         'Optional: In Text2, append & prefix the * wildcard to widen search:
         If Right$(text2.Text, 1) <> "*" Then text2.Text = text2.Text & "*"
         If Left$(text2.Text, 1) <> "*" Then text2.Text = "*" & text2.Text
         'Remove the above 2 lines if you want the user to enter the asterisk
         SearchText = text2.Text
   
         ' The following SQL syntax selects all records from the Authors table
         ' where the Author field matches the SearchText string, using any *
         ' or ? wildcard characters. The result is ordered by the Au_id field:
         SQLx = "Select * From Authors Where Author Like '" & SearchText
         SQLx = SQLx & "' Order By Au_id"
         Data1.DatabaseName = "biblio.mdb" ' Tells Data1 the database name.
         Data1.RecordSource = SQLx ' Data1 control will use SQLx query string.
         Data1.Refresh ' Update the data control with results of SQL query.
   
      End Sub
   
   

7. Start the program by pressing the F5 key. When a lone asterisk (*) is in the Text2 box, clicking the Command1 button finds all the records.

   Enter ar in Text2 and click Command1. The program changes the query to *ar*. That finds all Author field values that contain the letters ar.

   Enter z or *z* and click Command1 to find all Author field values that contain the letter z anywhere in the field.

   Close the form to end the program.

The Seek Method Does Not Support Wildcard Searches

The Seek method, which works only with Table object variables, is very fast but doesn't support wildcard searches. Seek is mainly useful for finding a single record that matches a given criteria. The find and move methods are more practical than the Seek method for finding a group of records.

The Seek method feature that is closest to a wildcard search is a comparison operator: >, >=, <=, or <. For example, you could find the first record that is greater than or equal to your search key value as follows:

   Dim MyDB As Database, MyTable As Table
   Set MyDB = OpenDatabase("BIBLIO.MDB")      ' Open a database.
   Set MyTable = MyDB.OpenTable("Publishers") ' Open a table.
   MyTable.Index = "PrimaryKey"               ' Define current index.
   MyTable.Seek ">=", 3         ' Seek a record with PrimaryKey >= 3.
   If MyTable.NoMatch Then
      MsgBox "match was not found"
   Else
      MsgBox "match was found"
   End If