Optimize, exemple de propriété (VB)
Cet exemple illustre la propriété dynamique Optimize de l’objet Field. Le champ zip de la table Authors de la base de données Pubs n’est pas indexé. La définition de la propriété Optimize sur True dans le champ zip autorise ADO à créer un index qui améliore les performances de la méthode Find.
'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