Display Current DB File Size on a form

[deafmetal1]

I wanted to display the current Database file size on my Switchboard. I couldn't figure out how to get the code to work using a direct link to the open database (CurrentProject), so I added a field to an admin table that contains the db's filename. This same table includes the db version, among other things I retrieve to update labels on multiple forms.

So on to the code. This assumes you have a table with the filename in it, and have a label on your form that matches the name in the code.

The code goes into the Form_Open Sub.

Private Sub Form_Open(Cancel As Integer)

'Display database file size
       
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblAdmin")
    
    Dim strFileLocation As String
    Dim strFileSize As String
'Retrieve the filename stored in the tblAdmin table and utilize the Database's current path
    strFileLocation = CurrentProject.Path & "\" & rs.Fields(1) & ".mdb"
'Format the number of bytes into Mb
    strFileSize = FormatNumber(FileLen(strFileLocation) / 1000000, 2)
    strFileSize = "DB Size: " & strFileSize & " Mb"
'Display the result into the form label
    Forms("Switchboard").lblFileSize.Visible = True
    Forms("Switchboard").lblFileSize.Caption = strFileSize
    Forms("Switchboard").Repaint
    
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set db = Nothing
       
End Sub

You can obviously substitute any form and table you want. Also of note, the particular field of the first record to grab the filename is indicated in rs.Fields(1). In that case, it is pulling from the 2nd column of the first row (column/field numbering starts at 0 and increases left to right). If you're looking to dig deeper in a table for the filename, you'll need to use Index and Seek (F1).

Hope that helps someone, and if you know an easier way to directly point to the currentproject, please let me know. I kept getting runtime errors.

You can also use the example above for retrieving info to populate labels throughout your forms/project. I use it to display the current db version/build number and date.

Cheers

 

[wazz]

FileLen (CurrentDb.Name)
may do the trick (file size in bytes).