Working with Data Sources
Collections of data sources (that is, MDStore objects of ClassType clsDataSource) are contained in MDStore objects of ClassType clsDatabase, clsCube, and clsPartition. Each object's data source specifies an external database that will be used as the source of data.
A database can contain multiple data sources in its DataSources collection. However, each cube and partition contains only a single data source.
The two examples in this topic demonstrate how to list and add a data source to the database's DataSources collection.
List Data Sources
The easiest way to list data sources is to iterate through the DataSources collection of an MDStore database object, as shown in the following code example which lists the Name and ConnectionString properties of each data source for every database on a given Analysis server.
Example
The following code example loops through the DataSources collection of each database on the local Analysis server, printing some of the basic properties for each data source in the Immediate window:
Private Sub ListDataSources()
Dim dsoServer As New dso.Server
Dim dsoDB As dso.MDStore
Dim dsoDS As dso.DataSource
' Create a connection to the Analysis server.
dsoServer.Connect "LocalHost"
' Step through the databases in the
' MDStores collection of the server.
For Each dsoDB In dsoServer.MDStores
' Print the name & description of the database.
Debug.Print "DATABASE: " & dsoDB.Name & " - " & _
dsoDB.Description
' Determine whether the database has data sources.
If dsoDB.DataSources.Count = 0 Then
Debug.Print " Data source: None"
Else
' Iterate through and print the data source
' information.
For Each dsoDS In dsoDB.DataSources
Debug.Print " Data source: " & dsoDS.Name
Debug.Print " Connect String: " & dsoDS.ConnectionString
Next
End If
Next
End Sub
Add a Data Source
The process to add a new data source is similar to the process for adding a new database. The AddNew method of the DataSources collection for a given database creates a new data source for the database.
Example
The following code example adds a data source named FoodMart to the TestDB database object on the local Analysis server:
Private Sub AddDataSource()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim strDBName As String
Dim strDSName As String
Dim strDSConnect As String
' Initialize variables for the database name,
' data source name, and the ConnectionString property
' for the data source.
strDBName = "TestDB"
strDSName = "FoodMart"
strDSConnect = "Provider=MSDASQL.1;User ID=sa;" & _
"Data Source=FoodMart;Connect Timeout=15"
' Create a connection to the Analysis server.
dsoServer.Connect "LocalHost"
' Locate the database first.
If dsoServer.MDStores.Find(strDBName) Then
Set dsoDB = dsoServer.MDStores(strDBName)
' Check to see whether the data source already exists.
If dsoDB.DataSources.Find(strDSName) Then
MsgBox "Data source " & strDSName & _
" already exists for database " & strDBName
Else
' Create a new data source.
Set dsoDS = dsoDB.DataSources.AddNew(strDSName)
' Add the ConnectionString properties
dsoDS.ConnectionString = strDSConnect
' Update the data source.
dsoDS.Update
' Inform the user
MsgBox "Data source " & strDSName & _
" has been added to database " & strDBName
End If
Else
MsgBox strDBName & " is missing."
End If
End Sub