May 2022 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Last week we learned the usage of ShowWindow and PostMessage Windows APIs. We could easily change the window state of Forms and Reports using their built-in windows-handle the hwnd Property values. We have not used the FindWindow API to retrieve the Form's windows identification number.

Here, we will learn another simple and useful Windows API Function, the GetDiskFreeSpace Function. The Windows API declaration is as follows:

Private 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

The GetDiskFreeSpace API needs five parameters:

The first parameter is the RootPathName of the Disk Drive like C:\ or C:.
The next four Parameter Variables must be declared as Long Integer Type and pass their references as parameters while calling the GetDiskFreeSpace API.
If the API call was successful and was able to retrieve the disk information then:
The second parameter Variable lpSectorPerCluster will contain the count of Sectors in a single Cluster on the Disk.
The disk capacity is measured in bytes, the number of bytes in a Sector, the number of Sectors in a Cluster, free space, and total space on the disk in Clusters.
The GetDiskFreeSpace API returns free space on the disk in Clusters only.
The lpBytesPerSector variable will have the quantity of data saved in a Disk-Sector, normally about 512 Bytes.
The 4th parameter lpNumberOfFreeClusters will have the free space left on the disk, returned in Clusters.
The last parameter lpTotalNumberOfClusters has the total capacity (the partition capacity) information in Clusters.

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 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 at 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 Debug Window, or displayed in a Label Control on Form that is wide enough to display 5 lines of text.