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

ID: Q114080

The information in this article applies to:

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:

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

Select the Publishers text. Then choose the Design button.

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

Enter test1_idx for the Index Name.

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

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

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

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

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

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

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

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

Additional query words: 3.00 multi-field multi

Keywords :
Version :
Platform :
Issue type :