clsDataSource

Note

  This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

An object of ClassType clsDataSource specifies an external database that will be used as a source of data for an object of ClassType clsDatabase, clsCube, or clsPartition. The object of ClassType clsDataSource providescollections, methods, and properties though its own internal interface.

Remarks

Connections to data sources are initiated when Decision Support Objects (DSO) requires access to data or property information in the source database. Data sources are only connected to when needed or when explicitly requested by the program. Executing the IsConnected method of an object of ClassType clsDataSource causes the Analysis server to attempt to connect to the specified data source.

An object of ClassType clsDatabase may contain multiple objects of ClassType clsDataSource in its DataSources collection. Objects of ClassType clsCube and clsPartition can only contain a single object of ClassType clsDataSource in their respective DataSources collection. An aggregation object (ClassType clsAggregation) does not implement the DataSources collection of the MDStore interface.

Examples

A. Creating a New Database

The following example demonstrates how to connect to the Analysis server and create a new database, attach a data source, and add a shared dimension and level. It uses the sample FoodMart 2000 database. After building and running the example code, you should be able to view the new database using Analysis Manager.

Option Explicit
Public dsoServer As DSO.Server
Const strConnect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=FoodMart 2000;Connect Timeout=15"

'Note: Add command control to form to enable
'      the cmdCreateDatabase_Click method

Private Sub cmdCreateDatabase_Click()
    On Error GoTo CreateDatabase_Err
    
    Dim dsoDB As DSO.MDStore
    Dim dsoDS As DSO.Datasource
    
    'Create database and add connection string
    Set dsoDB = dsoServer.MDStores.AddNew("MyDatabase")
    Set dsoDS = dsoDB.Datasources.AddNew("NewSales")
    dsoDS.ConnectionString = strConnect
    dsoDS.Update
    
    'Create dimension and set data source
    Dim dsoDim As DSO.Dimension
    Set dsoDim = dsoDB.Dimensions.AddNew("Products")
    Set dsoDim.Datasource = dsoDS
    dsoDim.FromClause = "product"
    dsoDim.JoinClause = ""
    
    'Add levels
    Dim dsoLev As DSO.Level
    Set dsoLev = dsoDim.Levels.AddNew("Product Id")
    'Point to table and column
    dsoLev.MemberKeyColumn = """product_class"".""product_family"""
   dsoLev.ColumnSize = 4            'Width of column in bytes
    dsoLev.ColumnType = adInteger   'ADODB Data Type
    
    dsoDim.Update
    
    Debug.Print "<<success>>"
    
    Exit Sub

CreateDatabase_Err:
    Debug.Print "Error creating new database"
    Debug.Print Err.Description
    Err.Clear
End Sub

Private Sub Form_Load()
    On Error GoTo FormLoad_Err
    
    'Connect to the Analysis server
    Set dsoServer = New DSO.Server
    'MyServer is the name of the Analysis server 
    dsoServer.Connect ("MyServer")
    Debug.Print ("Connected")
    Exit Sub
    
FormLoad_Err:
    Debug.Print ("Error connecting to server")
    Debug.Print Err.Description
    Err.Clear
End Sub

B. Connecting to Data Source Providers

Connection string examples are also provided for the following data source providers:

Microsoft® OLE DB Provider for Jet 3.51 OLE DB:

  ConnectionString="Provider=Microsoft.Jet.OLEDB.3.51;" & _
  "Persist Security Info=False;" & _
  "Data Source=C:\Program Files\" & _
  "Microsoft Analysis Services\Samples\FoodMart 2000.mdb" 

Microsoft OLE DB Provider for Jet 4.0:

  ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Persist Security Info=False;" & _
  "Data Source=C:\Program Files\" & _
  "Microsoft Analysis Services\Samples\FoodMart 2000.mdb;" & _
  "JET OLEDB:SFP=True;"

Microsoft OLE DB Provider for ODBC (Microsoft Access):

  ConnectionString="Provider=MSDASQL.1;" & _
  "Persist Security Info=False;" & _
  "Data Source=FoodMart 2000;" & _
  "Connect Timeout=15"

Microsoft SQL Server™:

  ConnectionString = "Provider=SQLOLEDB.1;" & _
  "Persist Security Info=False;" & _
  "User ID=sa;" & _
  "Initial Catalog=FoodMart 2000;" & _
  "Data Source={SQL Server};" & _
  "Connect Timeout=15" 

See Also

Reference

clsDatabase
clsCube
clsPartition
Collections, clsDataSource
Methods, clsDataSource
Properties, clsDataSource

Help and Information

Getting SQL Server 2005 Assistance