Microsoft KB Archive/104155

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:42, 20 July 2020 by X010 (talk | contribs) (Text replacement - ">" to ">")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


Article ID: 104155

Article Last Modified on 1/8/2003



APPLIES TO

  • Microsoft Visual Basic 3.0 Professional Edition



This article was previously published under Q104155

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

SHARE.EXE must be loaded for the Microsoft Access database to work.

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.



Additional query words: 3.00

Keywords: KB104155