Optimize-Eigenschaft – Beispiel (VB)
In diesem Beispiel wird die dynamische Optimize-Eigenschaft des Field-Objekts veranschaulicht. Das Zip-Feld der Tabelle Authors in der Pubs-Datenbank ist nicht indiziert. Durch Festlegen der Optimize-Eigenschaft auf True im Zip-Feld wird ADO zum Erstellen eines Indexes autorisiert, der die Leistung der Find-Methode verbessert.
'BeginOptimizeVB
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.
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