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.
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.
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.
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.