No. of Records in Multiple database

4 posts / 0 new
Last post
jyotikumar
Offline
Joined: 04/08/2022 - 02:37
No. of Records in Multiple database

Is there any way to crate a list of databases name and count the no. of records in each database of a folder?

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

Yes, you can use the ProjectManagement.Databases() to get a list of databases and then loop through each one and get the number of records.  What type of output are you looking for?

jyotikumar
Offline
Joined: 04/08/2022 - 02:37

I wanted list of names of all my files present in "Final Results" folder, and the no. of records in each files present in the folder.
 

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

Here is some code that will place the filenames and the filesizes into two arrays.  It uses the project folder for its base so if you only want items from a subfolder you will have to filter those out.


Option Explicit

Dim sFilenames() As String
Dim lFileSizes() As Long

Sub Main
	Dim i As Integer
	Dim msg As String
	Call getFilenames()
	Call getFilesizes()
	For i = 0 To UBound(sFilenames)
		msg = msg & sFilenames(i) & " (" & lFileSizes(i) & ")" &Chr(13)
	Next i
	MsgBox msg
End Sub

Function getFilesizes()
	Dim i As Integer
	Dim db As database
	
	ReDim lFileSizes(UBound(sFilenames))
	
	For i = 0 To UBound(sFilenames)
		Set db = client.opendatabase(sFilenames(i))
		lFileSizes(i) = db.count
		db.close
	Next i
	
	Set db = Nothing
End Function

Function getFilenames()
	Dim task As task
	Dim collection As collection
	Dim bFirstItem As Boolean
	Dim filename As String
	
	Set task = client.ProjectManagement
	Set collection = task.Databases
	
	bFirstItem = True
	
	For Each filename In collection
		If bFirstItem Then
			bFirstItem = False
			ReDim sFilenames(0)
			sFilenames(0) = filename
		Else
			ReDim preserve sFilenames(UBound(sFilenames) + 1)
			sFilenames(UBound(sFilenames)) = filename
		End If
	Next
	
	Set collection = Nothing
	Set task = Nothing
End Function