Collections of data sources, that is, objects of ClassType clsDatasource, are contained in 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. Each cube and partition, however, contains only a single data source.
The two examples in this section demonstrate how to list and add a data source to the database’s DataSources collection.
Add a command button to the form called cmdListDatasources. Add the following code to the cmdListDatasources_Click() event:
Private Sub cmdListDatasources_Click()
Dim dsoTempDB As DSO.MDStore
Dim dsoDS As DSO.DataSource
Dim DBCounter As Integer
Dim DSCounter As Integer
'Step through the databases in the server's MDStores collection
For DBCounter = 1 To dsoServer.MDStores.Count
Set dsoTempDB = dsoServer.MDStores(DBCounter)
'Step through the data sources in the database's DataSources
'collection
Debug.Print "DATABASE: " & dsoTempDB.Name & " / " & _
dsoTempDB.Description
If dsoTempDB.DataSources.Count = 0 Then
Debug.Print " Datasource: None"
Else
For DSCounter = 1 To dsoTempDB.DataSources.Count
Set dsoDS = dsoTempDB.DataSources(DSCounter)
Debug.Print " Datasource: " & dsoDS.Name
Debug.Print " Connect String: " & dsoDS.ConnectionString
Next DSCounter
End If
Next DBCounter
End Sub
Save your project and run the application. At this point, you should see None listed for the datasource for any new database you add to the system.
Add a command button to the form named cmdAddDatasource. Add the following code to the cmdAddDatasource_Click() event:
Private Sub cmdAddDatasource_Click()
On Error GoTo AddDatasource_Err
Dim dsoDS As DSO.DataSource
Dim strDBName As String
Dim bResult As Boolean
Dim strConnect As String
Const strProvider = "Provider=MSDASQL.1;"
Const strSecurity = "Persist Security Info=False;"
Const strDataSrc = "Data Source=FoodMart;"
Const strTimeOut = "Connect Timeout=15"
Const strDSName = "FoodMart"
'Do we have a database?
If dsoDB Is Nothing Then
strDBName = InputBox("Database to add new DataSource to.", _
"Add Datasource")
If strDBName = "" Then
MsgBox ("You must enter the name of a Database")
Exit Sub
End If
'Does this database exist?
If Not dsoServer.MDStores.Find(strDBName) Then
MsgBox (strDBName & " database not found on this server")
Exit Sub
Else
Set dsoDB = dsoServer.MDStores(strDBName)
End If
End If
'Does database already have an attached datasource?
If dsoDB.DataSources.Count >= 1 Then
strDBName = dsoDB.Name
MsgBox (strDBName & " database already has a datasource " & strDSName & " attached")
Exit Sub
End If
'Ask the user to confirm adding the Datasource
bResult = MsgBox("Add FoodMart Datasource", vbOKCancel, _
"Add Datasource")
If bResult = True Then
Set dsoDS = dsoDB.DataSources.AddNew(strDSName)
'Build the connection string
strConnect = strProvider & strSecurity & strDataSrc
strConnect = strConnect & strDataSrc & strTimeOut
'Set datasource properties
dsoDS.Name = strDSName
dsoDS.ConnectionString = strConnect
dsoDS.Update
MsgBox ("Datasource added to " & dsoDB.Name)
Else
MsgBox ("Canceled - no Datasource added")
End If
Exit Sub
AddDatasource_Err:
Debug.Print "Error attaching new datasource"
Debug.Print Err.Number, Err.Description, Err.Source
Err.Clear
End Sub
Save your project and run the application. Try adding a datasource. Verify that the datasource has been successfully added by listing datasources.
Note Since we are using the FoodMart sample database for these exercises, the data source name and connection string are hard coded. To add a different data source you will need to provide the correct data source name and connection string information.