HandsOn Creating an Index with the Ignore Null Option

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

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

Sub Index_WithIgnoreNullOption() Dim conn As ADODB.Connection Dim strTable As String

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "Supplier3"

conn.Execute "CREATE INDEX idxSupplierPhone ON " & strTable _ & "(SupplierPhone) WITH IGNORE NULL ;" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

Figure 21-6: The result of running the procedure in Hands-On 21-7 is an index called idxSupplierPhone that allows Null values in the key. However, records containing Null values will be excluded from any searches that use that index.

Part III

0 0

Post a comment