Optimize Property Example (VB)
Collapse the table of content
Expand the table of content

Optimize Property Example (VB)


This example demonstrates the Field object's dynamic Optimize property. The zip field of the Authors table in the Pubs database is not indexed. Setting the Optimize property to True on the zip field authorizes ADO to build an index that improves the performance of the Find method.

Public Sub Main()
    On Error GoTo ErrorHandler

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string.

    ' Declare the recordset and connection variables.
    Dim Cnxn As ADODB.Connection
    Dim rstAuthors As ADODB.Recordset
    Dim strCnxn As String
    Dim strSQLAuthors As String

     ' Open connection.
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Set Cnxn = New ADODB.Connection
    Cnxn.Open strCnxn

     ' open recordset client-side to enable index creation.
    Set rstAuthors = New ADODB.Recordset
    rstAuthors.CursorLocation = adUseClient
    strSQLAuthors = "SELECT * FROM Authors"
    rstAuthors.Open strSQLAuthors, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
     ' Create the index.
    rstAuthors!zip.Properties("Optimize") = True
     ' Find Akiko Yokomoto
    rstAuthors.Find "zip = '94595'"

     ' Show results.
    Debug.Print rstAuthors!au_fname & " " & rstAuthors!au_lname & " " & _
             rstAuthors!address & " " & rstAuthors!city & " " & rstAuthors!State
    rstAuthors!zip.Properties("Optimize") = False  'Delete the index.

    ' Clean up.
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    Exit Sub

    ' clean up
    If Not rstAuthors Is Nothing Then
        If rstAuthors.State = adStateOpen Then rstAuthors.Close
    End If
    Set rstAuthors = Nothing

    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
© 2015 Microsoft