How to Create and Use Multiple-Field Index in Visual Basic 3.0

- Microsoft Visual Basic programming system for Windows, version 3.0

SUMMARY

Indexes significantly speed up searches in Visual Basic. This article shows by example how to use a multiple-field index to perform a search on the BIBLIO.MDB database.

MORE INFORMATION

Creating a Multiple-Field Index

Before trying the example in this article, you need to use Data Manager, which is located on the Window menu of the main Visual Basic menu, to create a multiple-field index. Here are the steps:

1. In Data Manager, from the File menu, choose Open Database Access. Double-click the BIBLIO.MDB database to open it.

2. Select the Publishers text. Then choose the Design button.

3. From the Design window, choose the Add button in the Indexes: section of the window.

4. Enter test1_idx for the Index Name.

5. Select the PubID field. Then click the Add(Asc) button.

6. Select the Name field. Then click the Add(Asc) button.

7. Select the Company Name field. Then click the Add(Asc) button.

8. Click the Done button.

• PubID
• Name
• Company Name

Using the Multiple-Field Index

1. Start a new project in Visual Basic. Form1 is created by default.

2. Load the following into a text editor and fix all the two-line statements, so that each statement appears on one, single line. Then save the file as MULTIF.FRM.

   VERSION 2.00 Begin Form MultiF

      Caption         =   "Sample of using Multiple field index to Seek with"
      ClientHeight    =   4020
      ClientLeft      =   1095
      ClientTop       =   1485
      ClientWidth     =   7365
      Height          =   4425
      Left            =   1035
      LinkTopic       =   "Form1"
      ScaleHeight     =   4020
      ScaleWidth      =   7365
      Top             =   1140
      Width           =   7485
      Begin TextBox Text3
         Height          =   495
         Left            =   2040
         TabIndex        =   7
         Text            =   "Brady Books Div., Simon & Schuster, Inc."
         Top             =   1200
         Width           =   5295
      End
      Begin TextBox Text2
         Height          =   495
         Left            =   4440
         TabIndex        =   5
         Text            =   "Brady Pub."
         Top             =   240
         Width           =   1815
      End
      Begin TextBox Text1
         Height          =   495
         Left            =   1560
         TabIndex        =   3
         Text            =   "5"
         Top             =   240
         Width           =   735
      End
      Begin ListBox List1
         Height          =   810
         Left            =   1440
         TabIndex        =   1
         Top             =   2280
         Width           =   4095
      End
      Begin CommandButton Command1
         Caption         =   "Search on Multiple index test1_idx"
         Height          =   495
         Left            =   2040
         TabIndex        =   0
         Top             =   3240
         Width           =   3135
      End
      Begin Label Label4
         Caption         =   "Results"
         Height          =   255
         Left            =   3000
         TabIndex        =   8
         Top             =   1920
         Width           =   855
      End
      Begin Label Label3
         Caption         =   "Company Name ----->"
         Height          =   255
         Left            =   120
         TabIndex        =   6
         Top             =   1200
         Width           =   1815
      End
      Begin Label Label2
         Caption         =   "Name -------->"
         Height          =   255
         Left            =   3120
         TabIndex        =   4
         Top             =   360
         Width           =   1215
      End
      Begin Label Label1
         Caption         =   "Pubid------>"
         Height          =   255
         Left            =   360
         TabIndex        =   2
         Top             =   360
         Width           =   1095
       End
      End
   
      Sub Command1_Click ()
       Dim tbl As table
       Dim db As database
   
       a% = Val(text1.Text)
       tmp1$ = Trim$(text2.Text)
       tmp2$ = Trim$(text3.Text)
   
       Set db = OpenDatabase("biblio.mdb")
       Set tbl = db.OpenTable("publishers")
       tbl.Index = "test1_idx"
       tbl.Seek "=", a%, tmp1$, tmp2$
   
       If tbl.NoMatch Then
         MsgBox "No matches found"
       Else
         ' Change the following two lines to one, single line:
         list1.AddItem "" & tbl("PubID") & " "& tbl("Name")
            & " " & tbl("Company Name")
       End If
   
      End Sub
   
   

3. Remove Form1 from the new Visual Basic project list, and add MULTIF.FRM. Then set MULTIF as the start up form.

4. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button.