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:
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 StringAdd 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 SubAdd 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 SubAdd 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 SubAdd 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 SubAdd the following code to the command2 click event procedure:
Sub Command2_Click () text1.Text = "" command1.Caption = "Select Query from List box" End Sub- 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
