HandsOn Creating a Primary Key Index with Restrictions

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

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

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

On Error GoTo ErrorHandler

Set conn = CurrentProject.Connection strTable = "Supplier1"

conn.Execute "CREATE INDEX idxPrimary1 ON " & strTable _ & "(SupplierId) WITH PRIMARY ;" ExitHere:

conn.Close Set conn = Nothing Exit Sub ErrorHandler:

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

Figure 21-4: The index created by the procedure in Hands-On 21-5 has the Unique and Primary properties set to Yes, which means that this index is a primary key and every value in this index must be unique.

Note: Primary key indexes are automatically created as unique indexes.

Part III

You can prohibit the entry of Null values in the indexed fields by using the DISALLOW NULL option as shown in the example procedure in Hands-On 21-6.

0 0

Post a comment