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.

'BeginOptimizeVB
Public Sub OptimizeX()

    '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=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   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
End Sub
'EndOptimizeVB

Optimize Property Example (VB)

See Also