
'----------------------------------------------------------------
' Hands-On 21-1
'----------------------------------------------------------------

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

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection

    strTable = "Supplier1"

    conn.Execute "CREATE TABLE " & strTable _
        & "(SupplierID INTEGER, " _
        & "SupplierName CHAR (30), " _
        & "SupplierPhone CHAR (12), " _
        & "SupplierCity CHAR (19), " _
        & "CONSTRAINT idxSupplierName UNIQUE " _
        & "(SupplierName));"
    Application.RefreshDatabaseWindow
ExitHere:
        conn.Close
        Set conn = Nothing
        Exit Sub
ErrorHandler:
        MsgBox Err.Number & ":" & Err.Description
        Resume ExitHere
End Sub


'----------------------------------------------------------------
' Hands-On 21-2
'----------------------------------------------------------------

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

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
        strTable = "Supplier1"


    conn.Execute "CREATE INDEX idxCity ON " & strTable & _
                 "(SupplierCity) ;"
ExitHere:
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ":" & Err.Description
    Resume ExitHere
End Sub


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 _
        & "(SupplierID 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


'----------------------------------------------------------------
' Hands-On 21-3
'----------------------------------------------------------------

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

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
    strTable = "Supplier3"

    conn.Execute "CREATE TABLE " & strTable _
        & "(SupplierID INTEGER, " _
        & "SupplierName CHAR (30), " _
        & "SupplierPhone CHAR (12), " _
        & "SupplierCity CHAR (19), " _
        & "CONSTRAINT idxPrimary PRIMARY KEY " _
        & "(SupplierID));"
    Application.RefreshDatabaseWindow
ExitHere:
        conn.Close
        Set conn = Nothing
        Exit Sub
ErrorHandler:
        MsgBox Err.Number & ":" & Err.Description
        Resume ExitHere
End Sub


'----------------------------------------------------------------
' Hands-On 21-4
'----------------------------------------------------------------

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


'----------------------------------------------------------------
' Hands-On 21-5
'----------------------------------------------------------------

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 " & strTable _
        & "(SupplierCity) WITH DISALLOW NULL ;"
ExitHere:
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ":" & Err.Description
    Resume ExitHere
End Sub


'----------------------------------------------------------------
' Hands-On 21-6
'----------------------------------------------------------------

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


'----------------------------------------------------------------
' Hands-On 21-7
'----------------------------------------------------------------

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

    On Error GoTo ErrorHandler

    Set conn = CurrentProject.Connection
    strTable = "Supplier1"

    conn.Execute "DROP INDEX idxSupplierName ON " & strTable & ";"
ExitHere:
    conn.Close
    Set conn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ":" & Err.Description
    Resume ExitHere
End Sub

