HandsOn Creating an Index Based on Two Fields

1. Switch to the Visual Basic Editor window and insert a new module.

2. In the module's Code window, enter the MultiField_Index procedure as shown below.

Sub MultiField_Index()

Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "Supplier2"

conn.Execute "CREATE TABLE " & strTable _ & "(Supplierld INTEGER, " _ & "SupplierName CHAR (30), " _ & "SupplierPhone CHAR (12), " _ & "SupplierCity CHAR (19), " _" & "CONSTRAINT idxSupplierNameCity UNIQUE " _ & "(SupplierName, SupplierCity));"

Application.RefreshDatabaseWindow ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere

End Sub

Programming with the Jet Data Definition Language

Programming with the Jet Data Definition Language

Figure 21-2: The procedure in Hands-On 21-3 adds an index based on two fields to the existing Supplier2 table.
0 0

Post a comment