Create a Database

Each OLAP server contains an MDStores collection of databases, that is, objects of ClassType clsDatabase. A database in Decision Support Objects (DSO) contains dimensions and their subordinate levels, data sources, roles, and commands. Each database also contains an MDStores collection of cubes, that is, objects of ClassType clsCube.

In the following examples, you will add functionality to list databases associated with a server, as well as create and remove a database.

List Databases

Add a command button to the form named cmdListDatabases. Add the following code to the cmdListDatabases_Click() event:

Private Sub cmdListDatabases_Click()

    Dim dsoTempDB As DSO.MDStore

    Dim Counter As Integer

    

    'For each database on the server, print its name.

    For Counter = 1 To dsoServer.MDStores.Count

        Set dsoTempDB = dsoServer.MDStores(Counter)

        Debug.Print "Database: " & dsoTempDB.Name & _

        " / " & dsoTempDB.Description

    Next Counter

End Sub

Save your project and run the application. The Immediate window lists each database name and description. At this point, the only database in the system should be the FoodMart sample database.

Create a Database

To create an object of ClassType clsDatabase on your server, add a command button to the form named cmdAddDatabase. Add the following code to the cmdAddDatabase_Click() event:

Private Sub cmdAddDatabase_Click()

    On Error GoTo AddDatabase_Err

    'Declare and allocate local string variables

    'for use by the MDStore object.

    

    Dim strDBName As String

    Dim strDBDesc As String

    

    'Ask the user for a name for the database

    strDBName = InputBox("Enter a Unique DB Name", _

    "Adding New Database")

    

    'Is this new database name blank?

    If strDBName = "" Then

        MsgBox ("You must enter a database name")

        Exit Sub

    End If

    

    'Is there already a database by this name?

    If dsoServer.MDStores.Find(strDBName) Then

        MsgBox (strDBName & " already exists")

        Exit Sub

    End If

    

    'Add new database to server object collection

    'Using the AddNew method from MDStores.

    Set dsoDB = dsoServer.MDStores.AddNew(strDBName)

        

    'Ask the user for a description for the new database

    strDBDesc = InputBox("Enter a Description", _

    "Adding New Database")

    'Assign this description to the MDStore’s

    'Description property, and call the Update method.

    dsoDB.Description = strDBDesc

    dsoDB.Update

            

    'Inform the user that the database was added to the server.

    MsgBox (strDBName & " added to server " & dsoServer.Name)

    

    Exit Sub

AddDatabase_Err:

    Debug.Print "Error adding new Database"

    Debug.Print Err.Number, Err.Description, Err.Source

    Err.Clear

End Sub

Save your project and run the application. Click Add Database, and then enter a unique name in the text box. Use the List Database button created in the previous example to verify that the new database has been added.

Remove a Database

To remove a database, add a command button to the form named cmdRemoveDatabase. Add the following code to the cmdRemoveDatabase_Click() event:

Private Sub cmdRemoveDatabase_Click()

    Dim strDBName

    Dim bResult As Boolean

    

    'Do we have a database

    If dsoDB Is Nothing Then

        strDBName = InputBox("Database to remove.", _

        "Remove Database")

        If strDBName = "" Then

            MsgBox ("You must enter the name of a Database")

            Exit Sub

        End If

        

        'If the database can’t be found, inform the user.

        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

    

    'Ask users if they are sure they want to delete this DB

    bResult = MsgBox("Are you sure you want to remove " & dsoDB.Name, _

    vbYesNo, "Remove Database")

    

    If bResult = True Then

        strDBName = dsoDB.Name

        dsoServer.MDStores.Remove strDBName

        Set dsoDB = Nothing

        MsgBox (strDBName & "removed from server" & dsoServer.Name)

    Else

        MsgBox ("Canceled - Database not removed")

    End If

End Sub

Save your project and run the application. Try removing the database you created using the previous example.

Next

Add a Data Source

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