Using the Connection String
The primary way to interact with PivotTable® Service is to connect to a data source using a Connection object or the ActiveConnection property of a Catalog object. Parameters for this connection can be set using a connection string. For example, the properties in the connection string of a Connection object determine whether a connection connects to an Analysis server, creates a new cube, or connects to an existing local cube file. For more information about these data source properties, see Connection String Properties.
After a connection to an OLE DB for OLAP provider or a local cube has been established, queries can be issued against the data source and the results displayed. Information about the schema (that is, structure) of the data source can also be retrieved. For more information about data sources in OLE DB, see the OLE DB documentation.
The ADO Connection Object
The Open method of the Connection object provides for the inclusion of connection parameters in its ConnectionString property. A semicolon delineates each parameter. When this method is executed, a connection to the data source defined in the connection string is created.
The syntax of the open method is:
connection.Open ConnectionString, UserID, Password, OpenOptions
Connecting to Analysis Services
To connect to Microsoft® SQL Server™ 2000 Analysis Services, the Datasource property must be set to the name or IP address of the Analysis server to which you want to connect. The Provider property must also be set to "MSOLAP". Optionally, the Initial Catalog property may be set to specify a connection to a specific database on the Analysis server.
Connecting to a Local Cube
Connecting to a local cube is identical to connecting to an Analysis server with one exception: The Datasource property is set to the file location for the local cube instead of being set to the name of an Analysis server.
Setting the Connect Timeout Property
In OLE DB, the connection property that defines when a connection times out is DBPROP_INIT_TIMEOUT. In the connection string, this property is referred to as Connect Timeout. If a connection to a data source cannot be established in the number of seconds specified by this property, an error occurs.
The following example connects to an Analysis server on the local computer and sets the connect timeout property to 5 seconds:
Dim MyCon as ADODB.Connection
Set MyCon = new ADODB.Connection
MyCon.Open("provider=msolap; Datasource=LocalHost; Initial Catalog=FoodMart 2000; Connect Timeout=5")