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



The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0

This article was previously published under Q114080

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.
You now have a multiple-field index named test1_idx with the following fields in the following order:

  • 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.

Modification Type:MinorLast Reviewed:1/8/2003
Keywords:KB114080