Add a Data Source

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.

List Data Sources

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 Datasource

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.


Next

Add Dimensions and Levels

(c) 1988-1998 Microsoft Corporation. All Rights Reserved.