Optimize Property Example (VB)

This example demonstrates the Field objects 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.

'BeginOptimizeVB
Public Sub Main()
    On Error GoTo ErrorHandler

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

    ' 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
    rstAuthors.Close
    Cnxn.Close
    Set rstAuthors = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' 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
'EndOptimizeVB

See Also

Field Object | Optimize Property—Dynamic (ADO)