Removing databases in collection of databases

9 posts / 0 new
Last post
Pawelski_33
Offline
Joined: 06/05/2018 - 12:16
Removing databases in collection of databases

 
Hi There,
As a result of my script a number of databases are created (they are using the initial database as source of information which i call MasterDb). I want to put a code that will remove all of those small databases and only keep my initial MasterDb opened.So my question is if there is essentially a collection of databases I can loop through - my code idea is something like this.
Dim DatabaseObject as Database
For Each DatabaseObject In Databases
If DatabaseObject <> MasterDb Then
DatabaseObject.Close
End If 
Next 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Pawelski_33,

I have never tried it but I don't think you can have a collection of databases in IDEAScript, what you need instead is a collection (or array) of IDEA filenames and then you can delete the files you don't need.  Here is some example code for you: 


Dim dbFilename() As String 'holds the IDEA filenames

Sub Main
	'populate the array with IDEA filenamse, item 0 is the Master DB
	ReDim dbFilename(3)
	dbFilename(0) = "MasterDB.IMD"
	dbFilename(1) = "IDEADB1.IMD"
	dbFilename(2) = "IDEADB2.IMD"
	dbFilename(3) = "IDEADB3.IMD"
	
	For Each dbFile In dbFilename
		If dbFile <> dbFilename(0) Then
			Client.CloseDatabase dbFile 'the file must be closed before deleting
			Client.DeleteDatabase dbFile 'delete the file
		End If
	Next
End Sub

Pawelski_33
Offline
Joined: 06/05/2018 - 12:16

Hi Brian,
Thank you for your code - it is good idea to put the database name into array of names however i wanted to avoid it as i have 14 function to run within my code (with each function producing between 1-6 databases) which mean there will be quite a lot of code defining the names of the databases within the array for each function.
I was hoping that there is a collection of objects (database objects) in IDEA similar to collections in VBA i.e. 
Dim ws as Worksheet
For each ws in Worksheets 
... code to follow
Next ws
Anyway to resolve my problem i have used the following:
- I put the master database name in a variable MasterDbName at the begining of each function and then remove any database that's name is not MasterDbName at the end of the function - it seems to be working ok.
 
                Dim MasterDbName As String
                Do Until Client.CurrentDatabase.Name = MasterDbName
                            Client.CurrentDatabase.Close
                Loop

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Pawelski_33,

When you say remove do you mean close or delete the file?  I was assuming delete but it I think I might have misunderstood and you meant close.  You can always do a Client.CloseAll and then open the database that you want open.

Brian

Pawelski_33
Offline
Joined: 06/05/2018 - 12:16

Thanks Brian,
Apologies for not being more precise - I meant closing the database , in another words deleting from being available to view in the main IDEA window (under Home tab).Sorry for confusion.
Yes, using Client.CloseAll should work however as i am addng some fields to the main database in the functions in early stages of my programme it would be a bit counterproductive.

osaajah
Offline
Joined: 05/25/2018 - 02:33

Hi Pawelski_33,
I think the script will look like this:
'---------------------------
Option Explicit

Sub Main
Dim dbColl As Variant
Dim dbCurrent As String
Set dbColl = Client.ProjectManagement.Databases
For Each dbCurrent In dbColl
If dbCurrent <> "MasterDb.IMD" Then
Client.CloseDatabase dbCurrent
Client.DeleteDatabase dbCurrent
End If
Next
End Sub
'---------------------------

Firdaus Sentosa

Pawelski_33
Offline
Joined: 06/05/2018 - 12:16

Thank you very much osaajah. 
This is almost exactly what i was looking for ( the only issue is that my MasterDb is closed/delected as well).
I need to mention that I declare my MasterDb database earlier on in the code with the following line:
Set MasterDb = Client.CurrentDatabase (which is the first database opened)
However your code snippett is a great push into the right direction - i should make it work.
Thank you again.

osaajah
Offline
Joined: 05/25/2018 - 02:33

 Hi Pawelski_33,
 May be this modified script will help to have your solution.
I think IDEAScript unable to check whether two objects are same not.
So I use database name to check whether two object (= database) are same or not.
'--------------------------------------------
Option Explicit
Sub Main
Dim dbColl As Variant
Dim dbMaster As Database
Dim dbMasterName As String, dbCheckName As String
Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set dbMaster = Client.CurrentDatabase
    'extract file name from full path
    dbMasterName = fso.GetFileName(dbMaster.Name)
    Set dbColl = Client.ProjectManagement.Databases
    For Each dbCheckName In dbColl    
        If dbCheckName = dbMasterName Then
            Client.CloseDatabase dbMaster
            Client.DeleteDatabase dbMaster
        End If
    Next
    Set fso = Nothing
    Set dbMaster = Nothing
End Sub
'--------------------------------------------
 Firdaus Sentosa

Pawelski_33
Offline
Joined: 06/05/2018 - 12:16

Thanks ossajah, you hit the nail on the head.
I use Client.CurrentDatabase.Name earlier in my script to get the actual db name and then use it within the loop section to compare with the name of the MasterDb but it is essentially the same approach as you presented above.
Thanks again for your help.