Examples Show How to Query BIBLIO.MDB Database

- Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

Most of the examples in the Visual Basic Help menu for SQL statements do not show how to work with the BIBLIO.MDB Microsoft Access database that comes with Microsoft Visual Basic version 3.0 for Windows. Therefore this article shows by example how to use SQL statements with the BIBLIO.MDB database.

MORE INFORMATION

The following example gives 16 different SQL statements to test on the BIBLIO.MDB database. If you try one of the query statements on your own database and the result set is not what you had expected, try the Query By Example routine that comes with Microsoft Access to test your query. Note that if you try these examples on a computer that does not have SHARE.EXE loaded in memory, you will see this error:

   Object Variable not Set, number 91

Step-by-Step Example

1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.

2. Add one list box, two command buttons, and one text box to Form1.

3. Using the following table as a guide, set the properties of the controls you added in step 2:

      Control Name   Property       New Value
      ------------------------------------------------------------------
      Command1       Caption        "Select Query from List box"
      Command2       Caption        "Press to Clear Text Box"
      Text1          Multiline      True
      Text1          Scrollbars     Vertical
      Text1          Text           " "
   
   

4. Add the following code to the (general) (declarations) section of Form1:

   Dim query_array(0 To 15) As String

5. Add the following code to the form load event procedure:

      Sub Form_Load ()
         '*** Note that each statement, including those shown on more than one
         '*** line, must be entered as one, single line.
   
         'Load query array with some example queries:
         query_array(0) = "Select all * from publishers"           'Select All
         query_array(1) = "Select all * from publishers"          'From clause
         query_array(2) = "Select publishers.name from publishers
            where publisher s.name in ('ETN Corporation', 'ACM')"    'Where In
         query_array(3) = "Select publishers.name from publishers
            order by publishers.city"                                'Order By
         query_array(4) = "Select publishers.name from publishers,
            [publisher comments] where [publisher comments].publisher =
            publishers.name group by publishers.name"                'Group By
         query_array(5) = "Select publishers.name from publishers
            where publisher s.name between 'ETN Corporation' and
            'ACM'"                                              'Where Between
         query_array(6) = "Select Distinct publishers.name from
            publishers, [publisher comments] where
            [publisher comments].publisher = publishers.name
            group by publishers.name"                                'Distinct
         query_array(7) = "Select publishers.name from publishers
            In biblio.mdb"                                          'In clause
         query_array(8) = "Select Distinctrow publishers.name
            from publishers, [publisher comments] where
            [publisher comments].publisher = publishers.name
            group by publishers.name"                             'Distinctrow
         query_array(9) = "Select all * from publishers order
            by Publishers.name WITH OWNERACCESS OPTION"    'Owneraccess Option
         query_array(10) = "Select publishers.name from
            publishers group by publishers.name having
            publishers.name like 'A*'"                          'Having clause
         query_array(11) = "Select publishers.name from
            publishers, [publisher comments], [publisher comments]
            left join publishers on [publisher comments].pubid =
            publishers.pubid"                                       'Left Join
         query_array(12) = "Select publishers.name from
            publishers, [publisher comments], [publisher comments]
            right join publishers on [publisher comments].pubid =
            publishers.pubid"                                      'Right Join
         query_array(13) = "Select publishers.name from
            publishers, [publisher comments], [publisher comments]
            inner join publishers on [publisher comments].pubid =
            publishers.pubid"                                      'Inner Join
         query_array(14) = "Select publishers.name from
            publishers order by publishers.name ASC"                'ASC order
         query_array(15) = "Select publishers.name from
            publishers order by publishers.name DESC"              'DESC order
         list1.AddItem "Example of:  'Select All' Query"
         list1.AddItem "Example of:  'From clause' Query"
         list1.AddItem "Example of:  'Where In' Query"
         list1.AddItem "Example of:  'Order By' Query"
         list1.AddItem "Example of:  'Group By' Query"
         list1.AddItem "Example of:  'Where Between' Query"
         list1.AddItem "Example of:  'Distinct' Query"
         list1.AddItem "Example of:  'In clause' Query"
         list1.AddItem "Example of:  'Distinctrow' Query"
         list1.AddItem "Example of:  'Owneraccess Option' Query"
         list1.AddItem "Example of:  'Having clause' Query"
         list1.AddItem "Example of:  'Left Join' Query"
         list1.AddItem "Example of:  'Right Join' Query"
         list1.AddItem "Example of:  'Inner Join' Query"
         list1.AddItem "Example of:  'ASC order' Query"
         list1.AddItem "Example of:  'DESC order' Query"
      End Sub
   
   

6. Add the following code to the list1 click event procedure:

      Sub List1_Click ()
         idx% = list1.ListIndex
         Select Case idx%
            Case 0: command1.Caption = "Press for 'Select All'"
            Case 1: command1.Caption = "Press for 'From clause'"
            Case 2: command1.Caption = "Press for 'Where In'"
            Case 3: command1.Caption = "Press for 'Order By'"
            Case 4: command1.Caption = "Press for 'Group By'"
            Case 5: command1.Caption = "Press for 'Where Between'"
            Case 6: command1.Caption = "Press from 'Distinct'"
            Case 7: command1.Caption = "Press from 'In clause'"
            Case 8: command1.Caption = "Press from 'Distinctrow'"
            Case 9: command1.Caption = "Press from 'Owneraccess Option'"
            Case 10: command1.Caption = "Press from 'Having clause'"
            Case 11: command1.Caption = "Press from 'Left Join'"
            Case 12: command1.Caption = "Press from 'Right Join'"
            Case 13: command1.Caption = "Press from 'Inner Join'"
            Case 14: command1.Caption = "Press from 'ASC order'"
            Case 15: command1.Caption = "Press from 'DESC order'"
            Case Else: command1.Caption = "Select Query from List box"
         End Select
      End Sub
   
   

7. Add the following code to the text1 keypress event procedure:

      Sub Text1_KeyPress (keyascii As Integer)
         If keyascii > 0 Then  '** this routine makes it a read-only text box
            keyascii = 0
         End If
      End Sub
   
   

8. Add the following code to the command1 click event procedure:

      Sub Command1_Click ()
         Dim db As database
         Dim ds As dynaset
         On Error GoTo type_error
         idx% = list1.ListIndex
         tmp$ = query_array(idx%)
         Set db = OpenDatabase("C:\vb3\biblio.mdb")
         Set ds = db.CreateDynaset(tmp$)
         Do Until ds.EOF = True
            If IsNull(ds(0)) Then
               text1.Text = text1.Text + " " + Chr$(13) + Chr$(10)
            Else
               text1.Text = text1.Text + ds(0) + Chr$(13) + Chr$(10)
            End If
            ds.MoveNext
         Loop
         ds.Close
         db.Close
         command2.SetFocus
         type_error:
         If Err = 13 Then     '*** Type Mismatch error
            Do Until ds.EOF = True
               If IsNull((ds(1))) Then
                  text1.Text = text1.Text + " " + Chr$(13) + Chr$(10)
               Else
                  text1.Text = text1.Text + ds(1) + Chr$(13) + Chr$(10)
               End If
               ds.MoveNext
            Loop
            ds.Close
            db.Close
            command2.SetFocus
            Exit Sub
         Else
            command2.SetFocus
            Resume Next
         End If
      End Sub
   
   

9. Add the following code to the command2 click event procedure:

      Sub Command2_Click ()
         text1.Text = ""
         command1.Caption = "Select Query from List box"
      End Sub
   
   

10. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run

        the program. Select a query from the list box. Press the command button
        to have the result set added to the text box.
    

        To clear the contents of the text box, press the second command button.