| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The ADODB Connection, Command, Recordset, and Field objects, and the Parameters collection each include a Properties collection. Additionally, the ADOX Table, Column, and Index objects also have Properties collections. Each object or collection's Properties collection exposes both standard ADO properties and properties that are specific to the provider. The ADO Properties collections provide an extensibility mechanism that allows an OLE DB provider to expose additional properties that are not defined by ADO itself. The properties in an ADO Properties collection are often referred to as dynamic properties because they can change depending on the state or functionality of the object they are associated with.
Some provider-specific properties are used to set or read properties that may be required to work with the particular data source exposed by the provider. For example, to access a secured Access database, you may be required to specify the workgroup information file that contains the security accounts used for that database. Because ADO has no built-in property for this purpose, when you open a Connection object by using the Microsoft Jet 4.0 OLE DB Provider, the Properties collection exposes a Jet OLE DB:System Database property that you can use to specify the workgroup information file. You can also use some properties in an ADO object's Properties collection to determine whether the current object supports an ADO feature (for example, to determine whether a Recordset object supports transactions or updating).
You access properties in the Properties collection as you do most VBA collections: by using the Item method to reference the property by its ordinal position in the collection,
Connection.Properties.Item(0)
or by specifying its name:
Connection.Properties.Item("Jet OLE DB:System Database") = _
"\\MyComputer\MyShare\MySystem.mdw"
Because the Item method is the default method of an ADO collection, you can omit it as shown in the following functionally equivalent code fragments:
Connection.Properties(0)
Connection.Properties("Jet OLE DB:System Database") = _
"\\MyComputer\MyShare\MySystem.mdw"
Further, the accessor property to the Properties collection itself is the default property for the Connection, Command, and Recordset objects, so you can omit it as well:
Connection(0)
Connection("Jet OLE DB:System Database") = _
"\\MyComputer\MyShare\MySystem.mdw"
However, for clarity's sake, the sample code in this chapter includes the name of the Properties collection to make it obvious when a provider-specific property is being set or read.
You can examine the entire contents of a Properties collection by looping through it with a For Each…Next loop. The following sample procedure shows how to print the contents of the Properties collection for the Connection to the current database. This code sample uses the Connection property of the CurrentProject object to return a Connection object for the current database, which will work only when this code is running within an Access database. For more information about the Connection property of the CurrentProject object, see "Connecting to the Current Access Database by Using ADO" later in this chapter.
Sub PrintConnectionProperties()
Dim cnnDb As ADODB.Connection
Dim prpProp As ADODB.Property
' Use connection to the current database.
Set cnnDB = CurrentProject.Connection
For Each prpProp in cnnDB.Properties
Debug.Print prpProp.Name & "=" & prpProp.Value
Next
Set cnnDB = Nothing
End Sub
The PrintConnectionProperties procedure can be found in the PrintProperties module of the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.
The values of the properties in an ADO Properties collection are dynamic — that is, they will be different before and after the parent object is opened, and they depend on how the object is opened or initialized. For example, the values of properties in a Recordset object's Properties collection differ depending on the arguments passed to its Open method.
In most cases, properties in an ADO Properties collection become read-only after the parent object is opened. For this reason, if you need to set a provider-specific property, typically your code should set the property's value before it opens the parent object. For example, when it is running against the Microsoft Jet provider, the Properties collection of the Connection object provides a Jet OLEDB:System database property that can be used to specify the workgroup information file (system database), which defines the user and group accounts to use when you are working with an Access database that has been secured with user-level security. This property can only be set before you use the Open method to open the Connection object. After a Connection object is open, the Jet OLEDB:System database property becomes read-only. All properties in an ADO Properties collection can be referenced by name, as shown in the following code fragment, which sets the Jet OLEDB:System database property in a Connection object before it opens a database.
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
' Set path to workgroup information file.
.Properties("Jet OLE DB:System Database") = _
"\\MyComputer\MyShare\MySystem.mdw"
.Open "\\MyComputer\MyShare\MyDatabase.mdb"
End With
For more information about properties specific to the Microsoft Jet 4.0 OLE DB Provider, see ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.