May 2022 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Last week, we explored the ShowWindow and PostMessage Windows API functions. Using the built-in .hWnd property of Forms and Reports, we were able to change their window states (Normal, Minimized, Maximized, Hidden, or Closed) without relying on the FindWindow API to fetch their window handles.

This time, we will look at another simple but very useful Windows API function: GetDiskFreeSpace.

This function allows us to check the free space available on any logical drive in the system. Its declaration in VBA is as follows:

#If VBA7 Then

Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" _

Alias "GetDiskFreeSpaceA" ( _

ByVal lpRootPathName As String, _

lpSectorsPerCluster As Long, _

lpBytesPerSector As Long, _

lpNumberOfFreeClusters As Long, _

lpTotalNumberOfClusters As Long) As Long

#Else

Declare Function GetDiskFreeSpace Lib "kernel32" _

Alias "GetDiskFreeSpaceA" ( _

ByVal lpRootPathName As String, _

lpSectorsPerCluster As Long, _

lpBytesPerSector As Long, _

lpNumberOfFreeClusters As Long, _

lpTotalNumberOfClusters As Long) As Long

#End If

GetDiskFreeSpace API Parameters


Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" _
    Alias "GetDiskFreeSpaceA" ( _
    ByVal lpRootPathName As String, _
    lpSectorsPerCluster As Long, _
    lpBytesPerSector As Long, _
    lpNumberOfFreeClusters As Long, _
    lpTotalNumberOfClusters As Long) As Long

1. lpRootPathName (String)

This is the drive name you want to check.
It must be a string ending with a backslash.
Example:
- "C:\" → checks the C drive
- "D:\" → checks the D drive
If you pass vbNullString, it will use the current drive.

2. lpSectorsPerCluster (Long – Output)

The function fills this variable with the number of sectors per cluster on the drive.
A cluster is the smallest unit of disk storage allocation.

Example:
If lpSectorsPerCluster = 8 It means each cluster contains 8 sectors.

3. lpBytesPerSector (Long – Output)

This returns the number of bytes in each sector.
Typically, most drives use 512 bytes per sector, but newer drives may use 4096 bytes per sector.

4. lpNumberOfFreeClusters (Long – Output)

Returns the number of free clusters available on the drive.
This tells you how many “allocation units” are currently unused.

5. lpTotalNumberOfClusters (Long – Output)

Returns the total number of clusters on the drive (both free and used).

6. Return Value (Long)

If the function succeeds, it returns nonzero (1).
If it fails, it returns 0, and you can call Err.LastDllError to get more details.

How to Calculate Free Space

The free space (in bytes) can be calculated as:


Free Space = SectorsPerCluster × BytesPerSector × NumberOfFreeClusters

Similarly, total disk size can be calculated as:


Total Space = SectorsPerCluster × BytesPerSector × TotalNumberOfClusters

GetDiskFreeSpace` API usage into a reusable VBA function.

We’ll wrap it into a function `GetDriveSpace()` that you can call with any drive letter, and it will return **total space and free space in GB**


Step 1 – API Declaration
Keep this in a standard module (global):
#If VBA7 Then
    Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" _
        Alias "GetDiskFreeSpaceA" ( _
        ByVal lpRootPathName As String, _
        lpSectorsPerCluster As Long, _
        lpBytesPerSector As Long, _
        lpNumberOfFreeClusters As Long, _
        lpTotalNumberOfClusters As Long) As Long
#Else
    Declare Function GetDiskFreeSpace Lib "kernel32" _
        Alias "GetDiskFreeSpaceA" ( _
        ByVal lpRootPathName As String, _
        lpSectorsPerCluster As Long, _
        lpBytesPerSector As Long, _
        lpNumberOfFreeClusters As Long, _
        lpTotalNumberOfClusters As Long) As Long
#End If
Step 2 – Reusable Function
We’ll create a function that takes a drive letter (like "C:\" or "D:\") and returns total space and free space.

The DiskFreeSpace Wrapper Function.

We have created a wrapper function DiskFreeSpace to call the Windows API GetDiskFreeSpace Function from within, to retrieve and calculate the free space on the disk. The disk or computer memory capacity-related quantitative terms that we normally use are Gigabytes, Megabytes, or Kilobytes for communicating. So, we need to convert the cluster values into these measurements.

Create a new Standard Module in your Database.
Copy and paste the above Windows API Code into the global declaration area of the Module.
The DiskFreeSpace Function Code.

Next, copy and paste the following User function code below the Windows API code in the same Module:

Private SectorPerCluster As Long
Private BytesPerSector As Long
Private FreeClusters As Long
Private TotalClusters As Long

Private gbf As Double
Private mbf As Double
Private kbf As Double

Private tb As Double
Private gb As Double
Private mb As Double
Private kb As Double
Private fmt As String
Private msg As String, msg2 As String

Public Function DiskFreeSpace(ByVal strPath As String) As String
Dim Rtn As Long
Dim ClusterBytes As Double

On Error GoTo DiskFreeSpace_Err

Rtn = GetDiskFreeSpace(strPath, SectorPerCluster, BytesPerSector, FreeClusters, TotalClusters)

fmt = "#,##0"
msg = ""
msg2 = ""

If Rtn Then

'Bytes in a Cluster = Sectors * BytesPerSector
    ClusterBytes = SectorPerCluster * BytesPerSector 'Bytes per cluster
    
'msg2 = "        Disk Drive: " & UCase(strPath) & vbCrLf & _
        "Sector Per Cluster: " & SectorPerCluster & vbCrLf & _
        "  Bytes Per Sector: " & BytesPerSector & vbCrLf & _
        "     Free Clusters: " & FreeClusters & vbCrLf & _
        "    Total Clusters: " & TotalClusters
        
'Debug.Print msg2

    gbf = ClusterBytes / (1024# ^ 3) 'GB Factor per Cluster
    mbf = ClusterBytes / (1024# ^ 2) 'MB Factor     "
    kbf = ClusterBytes / (1024#)     'KB Factor     "

'free Space
    tb = Int(TotalClusters * gbf) ' Total Space in Gigabytes
    gb = Int(FreeClusters * gbf)  ' Free Space  in     "
    mb = Int(FreeClusters * mbf)  '       "     in Megabytes
    kb = Int(FreeClusters * kbf)  '       "     in Kilobytes

msg = "        Disk Drive:  " & UCase(strPath) & vbCrLf & _
       "Total Space ( GB ):  " & Format(tb, fmt) & vbCrLf & _
       " Free Space ( GB ):  " & Format(gb, fmt) & vbCrLf & _
       " Free Space ( MB ):  " & Format(mb, fmt) & vbCrLf & _
       " Free Space ( KB ):  " & Format(kb, fmt)
Else
    MsgBox "Disk Drive PathName: " & UCase(strPath) & vbCrLf & _
    "NOT FOUND!", vbOKOnly + vbCritical, "DiskFreeSpace()"
    DiskFreeSpace = ""
    Exit Function
End If

DiskFreeSpace = msg

DiskFreeSpace_Exit:
Exit Function

DiskFreeSpace_Err:
MsgBox Err & " : " & Err.Description, , "DiskFreeSpace()"
DiskFreeSpace = ""
Resume DiskFreeSpace_Exit
End Function

Our new function needs only one parameter, the disk's Root Pathname. If you look at the Windows API GetDiskFreeSpace Parameter declarations, the first Parameter is declared with ByVal qualification; other parameters are not qualified as such because they are declared as ByRef parameters by default.

The first parameter can be passed directly, like "C:\" or a variable initialized with the disk Root Pathname. Other parameter variables are declared as Long Integer Types in the Global declaration area. These variable References are passed to the Windows API, and the retrieved information is saved directly into those Variables.

All Variables except Rtn and ClusterBytes I have declared at the global declaration area so that our own Function DiskFreeSpace Code looks better and less crowded.

The user-defined function DiskFreeSpace needs only one parameter: the Root Pathname of the disk, like "C:\". The GetDiskFreeSpace Windows API is called with all five parameters from our function DiskFreeSpace().

The Disk Space Value Conversion Calculations.

If the API call was successful, then the variable Rtn will have the Value 1; otherwise, 0.

So testing the variable Rtn is necessary to proceed with converting the disk information into Gigabytes, Megabytes, or Kilobytes.

When the API is run successfully, the returned values are in Bytes per Sector, Sectors per Cluster, Disk Free Space in Clusters, and the Disk's total capacity in Clusters.

The disk capacity is logically grouped into Sectors of 512 Bytes (characters) and a group of 8 Sectors or more known as a Cluster. This is the amount of data the computer can read/write in one attempt. This may change depending on the type of disk drives, like Hard Disk, SSD, Zip-Drive, or MicroSD Drive, and their formatting type: NTFS, FAT32, etc.

The Bytes per Sector found 512 on most of the disk types, but the Sectors per cluster may change, like 8, 16, or 32. You may test this function on your own machine with different Disk Types to find out.

We are familiar with the terms like Gigabytes (GB), Megabytes (MB), or Kilobytes (KB), the terms which we normally use to communicate the disk's capacity. So we will convert the Clusters into bytes and then convert them into GB, MB, or KB.

[Total Bytes per Cluster] = [Bytes Per Sector] * [Sectors Per Cluster] = 512 * 8 = 4096 bytes.

GBF = [Total Bytes per Cluster] / (1024#^3): Gigabytes Factor.

MBF = [Total Bytes per Cluster] / (1024#^2): Megabytes Factor.

KBF = [Total Bytes per Cluster] / (1024#): KiloBytes Factor.

With these values, we can easily convert the Free Cluster Values into any of the above three Values, like:

GB = [Free Space Clusters] * GBF will give the Disk Free Space value in Gigabytes.

Our Function DiskFreeSpace() returns a String Value containing information formatted in such a way that the returned value can be displayed in a MsgBox, printed in the Debug Window, or displayed in a Label Control on a Form that is wide enough to display 5 lines of text.