HandsOn Creating an Index that Disallows Null Values in the

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

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

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

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "Supplier3"

conn.Execute "CREATE INDEX idxSupplierCity ON & "(SupplierCity) WITH DISALLOW NULL ;" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

& strTable

Figure 21-5: The result of running the procedure in Hands-On 21-6 is an index called idxSupplierCity that does not allow Null values.

You can prevent records with Null values in the indexed fields from being included in the index by using the IGNORE NULL option, as illustrated in Hands-On 21-7.

Programming with the Jet Data Definition Language

0 0

Post a comment