Microsoft KB Archive/104155

= Examples Show How to Query BIBLIO.MDB Database =

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
 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. Add one list box, two command buttons, and one text box to Form1.  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           " "   Add the following code to the (general) (declarations) section of Form1: Dim query_array(0 To 15) As String   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   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 </li>  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 </li>  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 </li>  Add the following code to the command2 click event procedure: Sub Command2_Click text1.Text = "" command1.Caption = "Select Query from List box" End Sub </li> 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.</li></ol>

Additional query words: 3.00

Keywords: KB104155

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.