Microsoft KB Archive/114080

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

Article ID: 114080

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



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.



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
 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: KB114080

-

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

© Microsoft Corporation. All rights reserved.